-1

I have a query of Postgresql-14 as below, the record was found if I run it on DBeaver 23.1.4.

I use:

  • Dapper 2.0.143
  • Npgsql 7.0.4
  • Npgsql.EntityFrameworkCore.PostgreSQL 7.0.4

Additional info:

process_datetime and start_datetime are of datatype timestamptz.

What I don't understand is why it works properly on DBeaver 23.1.4?

I copy the query into a string variable named "sql", but I did not find the record if I run it on my Net Core 6 (C#) code, what I missed?

select 
    t.organizations_pid, 
    coalesce(sum(t.sum_usage),0) * 1.05 totalUsage,   
    coalesce(count(t.iccid), 0) numSIMs 
from 
    (select 
         s.organizations_pid, c2.pid cdrs_pid, c2.cdr_id,  
         c2.iccid, c2.network, c1.sum_usage, 
         c2.start_datetime, c2.status 
     from
         (select 
              s.organizations_pid, c1.iccid, 
              sum(c1.usage) sum_usage, max(c1.process_datetime) max_process_datetime 
          from 
              cdrs c1 
          inner join 
              sims s on c1.iccid = s.iccid 
          where 
              s.organizations_pid = 29 
              and to_char(c1.start_datetime,'YYYY-MM-DD') >= '2023-08-01' 
              and to_char(c1.start_datetime,'YYYY-MM-DD') <= '2023-08-07' 
              and c1.is_beginbal = false 
          group by 
              s.organizations_pid, c1.iccid) c1 
     inner join 
         cdrs c2 on c2.process_datetime = c1.max_process_datetime 
                 and c2.iccid = c1.iccid 
     inner join 
         sims s on c2.iccid = s.iccid 
                and s.last_cdr_id = c2.cdr_id
     inner join 
         organizations o on s.organizations_pid = o.pid 
     where 
         s.organizations_pid = 29 
         and s.attributes->'subscription'->> 'subscriptionStatus' = 'ACTIVE') t 
group by 
    t.organizations_pid

My C# code is below:

using (var dr = conn.ExecuteReader(sql, new { }))
{
   if (dr.Read())
   {
     double totalUsage = dr.GetDouble(dr.GetOrdinal("totalUsage"));
   }
}

I also have tried below, but result returns null.

var result = conn.Query(sql, new { }).SingleOrDefault();
Donald
  • 551
  • 2
  • 6
  • 22
  • Not working - what does it mean exactly? Do you get an exception? – Ulugbek Umirov Aug 07 '23 at 14:20
  • it did not find the record – Donald Aug 07 '23 at 14:20
  • 2
    `and to_char(c1.start_datetime,'YYYY-MM-DD') >= '2023-08-01'` is guaranteed to cause problems. If `start_datetime` is a date-typed column, convert the parameter to a date, not the field. Better yet, use date-typed parameters instead of strings. Converting the *field* prevents the database from using any indexes – Panagiotis Kanavos Aug 07 '23 at 14:24
  • If `start_datetime` isn't a date field, `to_char` has no effect and returns whatever text is stored in the column. It would be no different than `and start_datetime> '2023-08-01`. That query will only work if the stored text and filter patterns match. That's a table design bug - dates should be stored in date-typed columns. – Panagiotis Kanavos Aug 07 '23 at 14:27
  • If it works in DBeaver, it should work here as well. Why are you using DataReader? Use Dapper Query. e.g., var result = connection.Query(sql).SingleOrDefault(); – CSharp Aug 07 '23 at 14:29
  • I have tried: var xxx = conn.QueryFirstOrDefault(sql, new {} ); but it returns null – Donald Aug 07 '23 at 14:38
  • I also have tried: I have tried: var result = conn.Query(sql, new { }).SingleOrDefault(); but it returns null – Donald Aug 07 '23 at 14:43
  • Side note: you can massively improve this query by using window functions https://dbfiddle.uk/fVEgGHtH – Charlieface Aug 07 '23 at 15:30

1 Answers1

0

I figured out this by changing the

to_char(c1.start_datetime,'YYYY-MM-DD') >=

to

DATE_TRUNC('day',c.start_datetime at TIME zone 'UTC') >=
Donald
  • 551
  • 2
  • 6
  • 22