Skip to Content
avatar image
Former Member

variable in formatted search

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 ;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 30, 2017 at 05:17 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 30, 2017 at 01:21 PM

    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

    Add comment
    10|10000 characters needed characters exceeded