Formated Search - Values from 2 tables

[former_member611080]

Hi to all,

I`m new to SAP (working with SAP and SQL for about 2-3 months) and I was wondering if you could help me, or someone can give some pointers.

I need to implement a formatted search on the Sales Order that looks for a value in 2 tables(QUT1 and OITM). First it should look into QUT1 and if the value is null or doesn`t fit the conditions, it should take it from OITM. I`ve come up with 2 different queries:

SELECT (COALESCE(CAST(CASE
WHEN
T1."U_ItemDesc_2" IS NULL

THEN
T0."FrgnName"

ELSE
T1."U_ItemDesc_2"

END AS varchar), '')) AS "Item Descr 2 Joined" FROM OITM T0 LEFT OUTER JOIN QUT1 T1 ON T0."ItemCode" = T1."ItemCode" WHERE T0."ItemCode" = $[$38.1.0] AND T1."ItemCode"=$[$38.1.0]

This query shows only what is in QUT1, and if the value is null or doesn`t meet the conditions, it does not show the value from OITM.

Second query:

SELECT COALESCE(NULLIF(T0."U_ItemDesc_2",'') , T1."FrgnName") FROM "SBO_JUMEL"."QUT1" T0 RIGHT JOIN "SBO_JUMEL"."OITM" T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OQUT T2 ON T0."DocEntry" = T2."DocEntry" WHERE T2."DocStatus"='O' AND T0."ItemCode" = $[$38.1.0] AND T1."ItemCode"=$[$38.1.0] ORDER BY T2."DocTime" ASC

This query is the same, still does not show values from OITM field if there is QUT1 value is null.

Has anybody used thistype of formated search and can give some pointers? Any help would be much appreciated

[former_member611080 · Accepted solution]

Solution in case anyone else needs this 🙂

SELECT CASE WHEN $[POR1.ItemCode] <> '' THEN CASE WHEN $[POR1.BaseEntry] <> '' AND $[POR1.BaseLine] <> '' THEN CASE WHEN (SELECT T2."U_ItemDesc_2" FROM PQT1 T2 WHERE CAST(T2."DocEntry" AS VARCHAR) = $[POR1.BaseEntry] AND CAST(T2."LineNum" AS VARCHAR) = $[POR1.LineNum]) <> '' THEN (SELECT T2."U_ItemDesc_2" FROM PQT1 T2 WHERE CAST(T2."DocEntry" AS VARCHAR) = $[POR1.BaseEntry] AND CAST(T2."LineNum" AS VARCHAR) = $[POR1.LineNum]) ELSE T0."U_Description2" END ELSE T0."U_Description2" END END FROM OITM T0 LEFT OUTER JOIN PQT1 T1 ON T0."ItemCode" = T1."ItemCode" WHERE T0."ItemCode" = CAST($[POR1.ItemCode] AS VARCHAR)

[former_member611080]

Can anybody help me out on this one?

SELECT

CASE

WHEN ISNULL(T1."U_ItemDesc_2",'') = '' THEN T0."FrgnName"

ELSE

( SELECT CAST( T1."U_ItemDesc_2" FROM QUT1 T1 WHERE T1."ItemCode" = $[RDR1.ItemCode] AND T1."DocEntry" = $[RDR1.BaseEntry] AND T1."LineNum" = $[RDR1.LineNum] )) END

FROM OITM T0 LEFT OUTER JOIN QUT1 T1 ON T0."ItemCode" = T1."ItemCode"

I get "Incorrect syntax near FROM" error. The query should look in QUT1 for values based on the ELSE condition, and if none, pull value from OITM.

[kothandaraman_nagarajan]

Hi,

Try this query, it should work for you,

SELECT

Case when ISNULL(T1.[U_ItemDesc_2],'') = '' then T0.[FrgnName] else T1.[U_ItemDesc_2] end

FROM

OITM T0 INNER JOIN QUT1 T1 ON T0.[ItemCode] = T1.[ItemCode]

WHERE T0.[ItemCode] = $[$38.1.0] and T1.[DocEntry] = $[$38.45.0] and T1.[LineNum] = $[$38.46.0]

Regards,

Nagarajan

[former_member611080]

Hi,

Thanks for taking the time to help me out. I`ve implemented you solution, but I`m getting "General Error; Inconsistent Datatype; NVARCHAR or VARCHAR are invalid function argument types: Line 3 col 11". I guess the error is because when using CASE in SQL , an integer is expected to return? How can I avoid this error?

Thank you very much.

[former_member611080]

I`ve modified it a little bit so it does not throw the error:

SELECT

CASE

WHEN T1."U_ItemDesc_2" IS NULL OR T1."U_ItemDesc_2" = '' THEN T0."FrgnName" ELSE T1."U_ItemDesc_2" END

FROM OITM T0 INNER JOIN QUT1 T1 ON T0."ItemCode" = T1."ItemCode"

WHERE T0."ItemCode" = $[RDR1.ItemCode] and T1."DocEntry" = $[RDR1.BaseEntry] and T1."LineNum" = $[RDR1.LineNum]

But this does not allow values to be pulled from OITM if there are no values in QUT1 based on the where condition

[former_member611080]

Hi Johan,

Thank you very much for taking the time to look into my problem. Much appreciated. I`ve implemented your solution and I must say, it is a lot better than what I`ve came up with:). The query solves half of my problem. Now I gotta find a way for it to look into OITM and pull values from there if there is no match in QUT1. I would imagine that building a query with CASE would be the way to do this?

Again, than you very very much for your help

[Johan_H]

Hi Paul,

There is quite a lot wrong with your queries, so where to begin? First let me say, that I applaud you for making a very good effort.

The quick answer, that will not give you a good solution: switch the FROM clause around, so that you start off with the "fall back" table. Then using LEFT OUTER joins, you will get a value or NULL from the QUT1 table, which will make the COALESCE function work properly.

FROM OITM T1
     LEFT OUTER JOIN QUT1 T0 ON T1.ItemCode = T0.ItemCode
     LEFT OUTER JOIN OQUT T2 ON T0.DocEntry = T2.DocEntry

However, because an item can (technically) be entered multiple times in one Quotation, and multiple Quotations can be open at the same time, the query as is, becomes rather volatile. It will only work, if you have only one Quotation with the given item open at that moment.

Assuming that you draw your sales orders from quotations (you use the copy to button on the Quotation form or the copy from button on the Order form), you can tie the order to the its specific quotation:

SELECT COALESCE(NULLIF(NULLIF(T1.[U_ItemDesc_2], ''), ' '), T0.[FrgnName],'Aaargh')
FROM OITM T0
          LEFT OUTER JOIN QUT1 T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.ItemCode = $[RDR1.ItemCode]
     AND T1.DocEntry = $[RDR1.BaseEntry]
     AND T1.LineNum = $[RDR1.LineNum]

Regards,

Johan

P.S. Thanks for teaching me about the NULLIF function. I didn't know that one yet. Very useful.