on 02-02-2010 7:11 AM
Hi All,
Need some help on one of the query I have. I have several conditions to be fullfilled and I need to use if-then-else syntax. However, izzit possible to if -then-else syntax on SAP B1 Query ? I have tried a few times and it returns me with various error. So far only when syntax works for me.
Thank You
Best regards,
Alex
I have one working syntax here, but it uses four different column on the query as I can't come out a nested one.
SELECT DISTINCT T0.DocNum as 'DO#' , T0.DocStatus as 'Status', (Datename (mm, T0.CreateDate)) + (Datename (yyyy, T0.CreateDate)) as '@MonthYear', T0.CreateDate, T0.DocTime,
T0.CardName, T1.ItemCode, round((T1.Quantity),2), T1.WhsCode, T1.U_Closing_Date, T1.U_Closing_Time2, DATEDIFF(dd, T0.[CreateDate], T1.U_Closing_Date) as "Day Difference",
(CASE WHEN T0.DocTime > '999' AND T1.U_Closing_Time2 > '999' THEN (((LEFT ((T0.DocTime),2)*60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),2)*60) + (RIGHT ((T1.U_Closing_Time2),2)))) END),
(CASE WHEN T0.DocTime < '999' AND T1.U_Closing_Time2 > '999' THEN (((LEFT ((T0.DocTime),1)*60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),2)*60) + (RIGHT ((T1.U_Closing_Time2),2)))) END),
(CASE WHEN T0.DocTime > '999' AND T1.U_Closing_Time2 < '999' THEN (((LEFT ((T0.DocTime),2)*60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),1)*60) + (RIGHT ((T1.U_Closing_Time2),2)))) END),
(CASE WHEN T0.DocTime < '999' AND T1.U_Closing_Time2 < '999' THEN (((LEFT ((T0.DocTime),1)*60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),1)*60) + (RIGHT ((T1.U_Closing_Time2),2)))) END)
FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE DATEDIFF(dd, T0.[CreateDate], T1.U_Closing_Date) < '0' AND T1.WhsCode = [%0]
Edited by: AlexAkane on Feb 2, 2010 10:15 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Alex,
Try this to replace your CASE:
CASE WHEN T0.DocTime > '999' AND T1.U_Closing_Time2 > '999' THEN (((LEFT ((T0.DocTime),2)60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),2)60) + (RIGHT ((T1.U_Closing_Time2),2))))
WHEN T0.DocTime < 999 AND T1.U_Closing_Time2 > '999' THEN (((LEFT ((T0.DocTime),1)60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),2)60) + (RIGHT ((T1.U_Closing_Time2),2))))
WHEN T0.DocTime > '999' AND T1.U_Closing_Time2 < 999 THEN (((LEFT ((T0.DocTime),2)60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),1)60) + (RIGHT ((T1.U_Closing_Time2),2))))
WHEN T0.DocTime < '999' AND T1.U_Closing_Time2 < 999 THEN (((LEFT ((T0.DocTime),1)60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),1)60) + (RIGHT ((T1.U_Closing_Time2),2))))
END AS 'Time to Close'
Thanks,
Gordon
User | Count |
---|---|
101 | |
14 | |
10 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.