Excel Query with Case ...else end; and Date conversion.

KARPURSHRIKANT

I m trying to retrive data in excel with the following query thru NODBC, Please advice how can i use Case ..else... Case or If then else in the query, also convert date in the format of (mm-yyyy)


SELECT
"Item Ledger Entry".InvtPostGrp,
"Item Ledger Entry".PostingDate,
"Item Ledger Entry"."Customer Name",
"Item Ledger Entry"."Source No_" as CustomerNo,
"Item Ledger Entry"."Guage","Item Ledger Entry"."Item Name",
"Item Ledger Entry".Weight as KG_Pc,
"Item Ledger Entry".PCs_Per_Bundles as Pcs_Bundle,
"Item Ledger Entry"."Sales Amount (Actual)" as Amount,
-"Item Ledger Entry"."Invoiced Quantity" as QTY_PCS,
case "Item Ledger Entry".PCs_Per_Bundles
of 0 :
0 ;
else -"Item Ledger Entry"."Invoiced Quantity"/"Item Ledger Entry".PCs_Per_Bundles
end; as Total_Bundles,
-"Item Ledger Entry"."Invoiced Quantity"*"Item Ledger Entry".Weight/1000 as Tonage

FROM "Item Ledger Entry" "Item Ledger Entry"
where
("Item Ledger Entry"."Posting Date" Between ? And ?) AND
"Item Ledger Entry"."Global Dimension 1 Code"=? and
("Item Ledger Entry"."Entry Type"='Sale') AND
(("Item Ledger Entry"."No_ Series" Like '%INV%') OR
("Item Ledger Entry"."No_ Series"='POSTEDRCPT'))


in the above query i m trying to compute the Total Bundles and the divisior cannot be '0'

P.S The query is executed sucessfully without the case statement........

Also Please advice where can i get a list of valid functions and statements relating to above excersie...

Thanks

Shrikant K.

ara3n

you can use the following trick in sql

ISNULL(ROUND("Item Ledger Entry"."Invoiced Quantity"/NULLIF("Item Ledger Entry".PCs_Per_Bundles ,0),2),0)