0

There is postgres sql query, shown below:

Select agencyId, SUM(open),
   from agencies 
   where agency_id= 100 and agency_platform = any(@platformIds) 
   GROUP BY agencyId;

If platformIds list is empty then this query doesn't return any data, and on the other hand below query data with no issues:

Select agencyId, SUM(open), 
    from agencies 
    where agency_id= 100 
    GROUP BY agencyId;

any thoughts?

stuartd
  • 70,509
  • 14
  • 132
  • 163
Mayank Gupta
  • 45
  • 7
  • 1
    It’s behaving as designed - the `any` check returns false. Perhaps add a condition to only check the `platformIds` if there *are* any? – stuartd Jan 27 '23 at 18:29
  • @stuartd, I have tried this Select agencyId, SUM(open) from agencies where agency_id= 100 and case when array_length(@platformIds, 1) > 0 then agency_platform = any(@platformIds) else true end GROUP BY agencyId; but it says ERROR: syntax error at or near "array_length". Any clue? – Mayank Gupta Jan 27 '23 at 18:33
  • https://stackoverflow.com/questions/11188756/how-to-find-the-size-of-an-array-in-postgresql – stuartd Jan 27 '23 at 18:35

0 Answers0