Formatted search for Delivery Order

[shikin]

I have this one sql query that i want to use it in Delivery Order item details column. Here is my code:

if (SELECT 1 FROM ORDR T0 <br>INNER JOIN RDR1 T1 ON T0.DocEntry=T0.DocEntry<br>WHERE isnull( T0.[U_Unit] ,0)>0 and  T1.[ItemCode] =$[dln1.itemcode]) =1<br>SELECT <br>replace(<br>replace(<br>replace(<br>CAST(T1.[Text] AS NVARCHAR(2000))+CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10)+'Total : <'+<br>cast(cast(round((T0.[U_unit]*$[dln1.quantity]) ,0) as decimal(12,0)) as nvarchar) +'> UNITS X <'+<br>cast(cast(round(($[dln1.quantity]) ,0) as decimal(12,0)) as nvarchar)+'> SETS'<br>,'<1>' ,concat('< ',round(cast(replace($[$38.11.1],',','') as decimal(12,0)),0),' >'))<br>,'<2>',concat('< ',cast(cast(round((2*cast($[dln1.quantity] as decimal(12,0))) ,0) as decimal(12,0)) as nvarchar) ,' >'))<br>,'<3>',concat('< ',cast(cast(round((3*cast($[dln1.quantity] as decimal(12,0))) ,0) as decimal(12,0)) as nvarchar) ,' >'))<br>FROM ORDR T0 <br>inner join RDR1 t1 on t0.docentry=t1.docentry<br>WHERE T1.[ItemCode]=$[dln1.itemcode]<br>else<br>select $[dln1.text]

but when i try it, from SO copy to DO, this error appears:

Can someone help me?

[clas_hortien]

The problem is the selection in the IF branch:

IF (SELECT 1 FROM
    ORDR T0 
    INNER JOIN RDR1 T1 ON T0.DocEntry=T0.DocEntry 
WHERE
    isnull( T0.[U_Unit],0)>0 
    AND T1.[ItemCode] =$[dln1.itemcode]) =1 

It returns more than one row and that can't be used in an IF clause.

You can rewrite this as

IF EXISTS (SELECT 1 FROM
	ORDR T0 
	INNER JOIN RDR1 T1 ON T0.DocEntry=T0.DocEntry 
WHERE
	isnull( T0.[U_Unit],0)>0 
	AND	T1.[ItemCode] =$[dln1.itemcode]) 

EXISTS returns only one value (true or false) when the subselect returns rows.

Regards

Clas

[shikin]

hi clas.hortien , thanks so much for your reply. I followed your edited query but somehow it returns another error:

can you please advice? Thanks so much.

[clas_hortien]

Hi,

this is hard to tell where the error comes from, most probably from an implicit conversion of some column to the data type INT. This part

"T0.[U_unit]*$[dln1.quantity])"

can be a candidate. Try to cast both of the columns to decimal before you multiply them.

Regards

Clas

[Andre_S]

Your where conditions are not specific enough. At the moment they are returning all order lines with this specific item (subquery returned more than 1 value), which can be many. You need to rather work with BaseLine and BaseEntry to get the link to your SalesOrder.