Skip to Content
0

variable in formatted search

Jun 30, 2017 at 09:52 AM

52

avatar image

i have the following query that runs well in SQL but returns not results in formatted search.

why ?

Declare @NameList NVarChar(MAX) = ''

select @NameList =

case when @NameList = ''

then coalesce(concat( T3.U_ProductName ,'-',T3.U_Value),'')

else @NameList + coalesce(concat(' ;',T3.U_ProductName,'-',T3.U_Value),'')

end

from ordr T0

inner join rdr1 T1

on T0.DocEntry = T1.DocEntry

inner join [dbo].[@COR_PRODUCTP] T2

on T1.U_ProductPro = T2.DocEntry

inner join [dbo].[@COR_PRODUCTv] T3

on t2.DocEntry = T3.DocEntry

where T0.DocNum = 1000001

--$[$8.0.0]

and t1.U_ProductPro=1

--$[$38.U_ProductPro.0]

print @NameList ;

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

DIEGO LOTHER Jun 30, 2017 at 05:17 PM
0

Hi Dov,

At the end your query you need use SELECT not PRINT command.

I don't have your user tables, then I changed your query to test on my SAP and worked fine.

DECLARE @NameList NVARCHAR(MAX) = ''
SELECT 
	@NameList = CASE WHEN @NameList = '' THEN 
						COALESCE(CONCAT(T1.ItemCode ,'-',T1.Dscription),'')
					 ELSE 
						@NameList + COALESCE(CONCAT(' ;',T1.ItemCode ,'-',T1.Dscription),'')
					 END
FROM 
	ORDR T0
	INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
	--INNER JOIN [dbo].[@COR_PRODUCTP] T2 ON T1.U_ProductPro = T2.DocEntry
	--INNER JOIN [dbo].[@COR_PRODUCTv] T3 ON t2.DocEntry = T3.DocEntry
WHERE 
	T0.DocNum = $[$8.0.NUMBER]
	AND T1.ItemCode = $[$38.1.0]

SELECT @NameList

And remember, to run this query ok, your order must be saved.

Hope it helps.

Kind Regards,

Diego Lother

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Jun 30, 2017 at 01:21 PM
0

Hi,

Formatted searches use the B1 client's built in SQL parser, to allow for the B1 specific formatted search variables like $[$38.U_ProductPro.0]. This parser is otherwise limited, and does not directly understand the more complicated syntax that you might use in MS SQL Management Studio. Also I don't think you can use print for the return value of the formatted search. I am pretty sure it needs to be a normal query result.

So you will need to build your query completely differently.

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded