Nested If loop on Query

[Former Member]

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

[Former Member · Accepted solution]

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

[former_member204969 · Accepted solution]

Try to structure your query in this way:

Select DISTINCT T0.DocNum . . .,
   Case When (T0.DocTime > '999' AND T1.U_Closing_Time2 > '999') Then ( . . .)
            When (. . .) then (. . .)
            When (. . .) then  (. . .)
            . . .
            When (. . .) then  (. . .)
   End 'headertext',
. . .

[Former Member · Accepted solution]

I think SAP already highlighted the syntax error for you. Try add underscore to "Day Difference" like "Day_Difference". I don't think SAP understands "Day Difference" like Microsoft Access

does.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi Gordon,

Thank you so much for the syntax, it works perfectly. Just realized that I don't have to include else when using a When nested loop.

Best Regards,

Alex

[Former Member]

Hi Alex,

it is possible but some case will not support IF loop on Query.

can you post your IF loop Query.

Regards,

Madhan.

[Former Member]

Hi!

You can use

If (Condition) BEGIN....END Else if (Condition) BEGIN.... END

[Former Member]

Hi Thanga,

I have tried it out, but it doesn't work. I think there are something wrong wtih the syntax but I cant find the error.

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", 
If (T0.DocTime > '999' AND T1.U_Closing_Time2 > '999') BEGIN (((LEFT ((T0.DocTime),2)*60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),2)*60) + (RIGHT ((T1.U_Closing_Time2),2)))) END ELSE 
If (T0.DocTime < '999' AND T1.U_Closing_Time2 > '999') BEGIN (((LEFT ((T0.DocTime),1)*60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),2)*60) + (RIGHT ((T1.U_Closing_Time2),2)))) END ELSE
If (T0.DocTime > '999' AND T1.U_Closing_Time2 < '999') BEGIN (((LEFT ((T0.DocTime),2)*60) + (RIGHT ((T0.DocTime),2))) - ((LEFT ((T1.U_Closing_Time2),1)*60) + (RIGHT ((T1.U_Closing_Time2),2)))) END ELSE
If (T0.DocTime < '999' AND T1.U_Closing_Time2 < '999') BEGIN (((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]