Skip to Content
0
Dec 05, 2022 at 09:46 AM

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

97 Views

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;

image.png

Line 1 is duplicated.

Is this a join issue in the SQl?

Attachments

image.png (15.2 kB)