|
|
Posted by MC on 01/20/07 12:45
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" <rshah44984@gmail.com> wrote in message
news:1169274382.788904.13060@v45g2000cwv.googlegroups.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
>
Navigation:
[Reply to this message]
|