If Then Else SAP B1 Query

[former_member606861]

Hello,

I have this query I already use daily:

SELECT T2.[WhsName], T0.[DocDate], T0.[DocEntry] AS 'Order Number', T0.[LineNum], T0.[ItemCode] AS 'Item Code', T0.[Dscription] AS 'Product Name', T0.[Quantity], T0.[LineTotal] AS 'Net Sale'

FROM INV1 T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OWHS T2 ON T0.[WhsCode] = T2.[WhsCode] INNER JOIN OINV T3 ON T0.[DocEntry] = T3.[DocEntry]

Trying to create another Column that would categorize document into either Delivery or Store Order. The Document should be considered a Delivery if the ItemCode 'Delivery' appears in the document lines. Is this possible to accomplish through an If Else statement in SAP B1? So far, I hvae tried Case Option, but have not had much luck either.

Also tried below, but did not seem to work either.

IF T0.[ItemCode] = 'Delivery', THEN T3.[DocEntry] = 'Delivery' ELSE 'Store'.

[KonradZaleski]

CASE WHEN T0.[ItemCode] = 'Delivery' THEN 'Delivery' ELSE 'Store' END

[former_member606861]

Thanks, but I am trying to go a bit farther. Basically, if the item code "Delivery" is in the lines, I would need the whole order to be designated as "Delivery." Not sure if this would be more complex and not fall under If Then Statements. Thanks for assistance!

[Johan_H]

Hi Ismael,

How about this:

CASE
 WHEN ISNULL((select distinct 'true' 
              from INV1 r 
              where r.DocEntry = T0.DocEntry 
                and r.ItemCode = 'Delivery'), 'false') = 'true' THEN 'Yes'
 ELSE 'No'
END AS [Is Delivery]

Regards,

Johan

[former_member358098]

Isn't this overcomplicated?

Why did wrapping in true/false appeared?

Couldn't we just ?

CASE
WHEN (select distinct 1 
		from INV1 r 
              	where r.DocEntry = T0.DocEntry 
                and r.ItemCode ='Delivery') = 1 THEN 'Yes' 
ELSE 'No' 
END AS [Is Delivery]

[Johan_H]

artemt, same difference indeed. My version is easier to read / understand, your version is cleaner / more efficient.

However, your version may throw an error if the subquery returns an empty result set. So if we make one more small adjustment, Ismael may want to use your version:

CASE
 WHEN ISNULL((select distinct 1
              from INV1 r 
              where r.DocEntry = T0.DocEntry 
                and r.ItemCode ='Delivery'), 0) = 1 THEN 'Yes'
 ELSE 'No'
END AS [Is Delivery]