UDF to be used instead of ITM Description IF it exists in SQL statement

[neilos]

Hi Community,

I have a UDF which is used as an alternative item description for specific scenarios.
Our customers need to see their descriptions for some of our products '[U_BPDesc]'.
On a sales order, there may be two items, where one has an alternative description while the other doesn't.

The query I have is

SELECT
T0.[DocNum], 
T0.[DocDate], 
T0.[CardCode], 
T0.[U_Site],
T1.[LineNum], 
T1.[ItemCode], 
T1.[Dscription], 
T1.[Quantity], 
T1.[SubCatNum],
T2.[PrjName],
T3.[U_BPDesc]

FROM
ORDR T0
INNER JOIN RDR1 T1 on T1.DocEntry=T0.DocEntry
full outer JOIN OPRJ T2 ON T0.[Project]=T2.[PrjCode]
INNER JOIN OSCN T3 ON T1.[SubCatNum] = T3.[ItemCode]

WHERE
T0.DocNum = {?DocKey@}

What is happening, if an item has a U_BPDesc, then it is being displayed twice. One time with a blank desc field and again with the field containing a value.

Outputs as below;

Line 1 is duplicated.

Is this a join issue in the SQl?

[LoHa]

Hi Neil,

you could use a case/when like

,CASE WHEN ISNULL(T3.[U_BPDesc],'') <> '' THEN T3.[U_BPDesc] ELSE T1.[Dscription] END AS [Needed Dscr]

regards Lothar

[neilos]

Hi Lothar,

It kind of works. As in it adds the description where it needs to be instead of an empty field.
The line is still duplicated, but I can deal with this using DISTINCT, as long as I include the Line Number in the query incase I have the same item loaded multiple times on different lines.

Thanks.

[LoHa]

Hi Neil,

if it works for you please mark as solved.

Your join to OSCN, could be a little bit dangerous. A SubCatNum could exist for different BP and in newer versions it is possible that a BP could have multiple SubCatNums for one ItemCode but one is set to default (OSCN.IsDefault).

regards Lothar