1 reply

Issue with SUM in sql template

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

This query works fine if the rows are available an produce the correct result.
But for conditions that the rows are 0,

boPending

boPending

and

notLocked

notLocked

result in

null

null

I tried

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

but the result is same.

Is there any issue related to this that I can follow?
Are there any alteranitives using query builer instead of sql template?

Drizzle Team•3y ago•

1 reply

Dush

Issue with SUM in sql template

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

This query works fine if the rows are available an produce the correct result.
But for conditions that the rows are 0,

boPending

boPending

and

notLocked

notLocked

result in

null

null

I tried

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

but the result is same.

Is there any issue related to this that I can follow?
Are there any alteranitives using query builer instead of sql template?

Issue with SUM in sql template

Similar Threads

Issue with SUM in sql template

Similar Threads

Similar Threads

Similar Threads