SQL query Syntax

[Rshah]

sql query syntax?

i write this query but error get : column not found
how can i write ?
SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,
IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]
GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);

this database driver sage line 50 odbc

i write a simpale query of this database, successful run but when i
write in condition (IF) is not run error comes : column not found.

rashesh

[MC]

Perhaps I'm missing something here, but T-SQL doesnt support IIF function.
You should use CASE. Something like:

SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
CASE WHEN MID(a.type,2,1)="I" THEN SUM(a.quantity) ELSE 0 END AS iqty,
CASE WHEN MID(a.type,2,1)="O" THEN SUM(a.quantity) ELSE 0 END AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]
GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);
MC

"Rshah" <rs********@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegro ups.com...

sql query syntax?

i write this query but error get : column not found
how can i write ?
SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,
IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]
GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);

this database driver sage line 50 odbc

i write a simpale query of this database, successful run but when i
write in condition (IF) is not run error comes : column not found.

rashesh

[Ed Murphy]

MC wrote:

Perhaps I'm missing something here, but T-SQL doesnt support IIF function.
You should use CASE. Something like:

SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
CASE WHEN MID(a.type,2,1)="I" THEN SUM(a.quantity) ELSE 0 END AS iqty,
CASE WHEN MID(a.type,2,1)="O" THEN SUM(a.quantity) ELSE 0 END AS oqty
FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
WHERE a.date>=[from_date] And a.date<=[to_date]

You can write this as
WHERE a.date BETWEEN [from_date] AND [to_date]

GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
MID(a.type,2,1)
ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);

Did you mean to omit c.description from the ORDER BY?