Need a second set of eyes to check my sql query

HCL Domino Domino Forum
1

G’day, I’m trying to do a query in lotusscript using odbc and I’m trying to sum in the query at the same time but it doesn’t seem to do it. Does anyone have an example that I could see to compare?

Here is my existing one:

{SELECT RECEIPTS.WHSE, RECEIPTS.CODE, RECEIPTS.REMAINING_QTY FROM RECEIPTS WHERE WHSE = ‘} & Trim(sw$) & {’ AND CODE = ‘} & Trim(onesku) & {’ AND SUM(REMAINING_QTY) > ‘} & whatweneed% & {’}

Thank you

KB

2

Subject: Need a second set of eyes to check my sql query

My understand is that most DBMS do not allow aggregate functions (SUM, COUNT, etc.) in the WHERE clause.

http://sqlzoo.net/howto/source/z.dir/err934/sqlserver

3

Subject: RE: Need a second set of eyes to check my sql query

Yep that’s right. Thanks for that. I took out the WHERE and did it this way and it works:

{SELECT RECEIPTS.WHSE, RECEIPTS.CODE, SUM(RECEIPTS.REMAINING_QTY) AS SumQty FROM RECEIPTS GROUP BY RECEIPTS.WHSE, RECEIPTS.CODE HAVING RECEIPTS.WHSE = ‘} & Trim(sw$) & {’ AND RECEIPTS.CODE = ‘} & Trim(onesku) & {’ AND SUM(RECEIPTS.REMAINING_QTY) > 0}

Thanks man,

KB