Sap Business One Query Parameter from more than 1 table

[hogianto]

i have call procedure from query generator that have parameter

/* SELECT FROM [dbo].[INV1] T0 */

DECLARE Discount1 DECIMAL(38,2);

DECLARE Discount2 DECIMAL(38,2);

DECLARE Discount3 DECIMAL(38,2);

Discount1 = /* T0."U_STEM_Disc1" */ '[%0]';

Discount2 = /* T0."U_STEM_Disc2" */ '[%1]';

Discount3 = /* T0."U_STEM_Disc3" */ '[%2]';

CALL CKM.CKM_COO_Report_Master_StockNMarginPHILIPS (Discount1 , Discount2 , Discount3 ,'Personal Care', 'Jakarta',output );

select * from :output ;

i want to change the parameter 'Personal Care', 'Jakarta' with input parameter
'Personal Care' came from OITM table field and 'Jakarta' came from OWHS table field

anyone can help or give suggestion?
thanks in advance
Regards,

Hogianto

[LoHa]

Hi,

try it like this

/* SELECT FROM [dbo].[INV1] T0 */
DECLARE @Discount1 AS DECIMAL(38,2)
/* WHERE */
SET @Discount1 = /* T0."U_STEM_Disc1" */ '[%0]'

/* SELECT FROM [dbo].[INV1] T1 */
DECLARE @Discount2 AS DECIMAL(38,2)
/* WHERE */
SET @Discount2 = /* T1."U_STEM_Disc2" */ '[%1]'

/* SELECT FROM [dbo].[INV1] T2 */
DECLARE @Discount3 AS DECIMAL(38,2)
/* WHERE */
SET @Discount3 = /* T2."U_STEM_Disc3" */ '[%2]'

/**SELECT FROM [OITM] T3 **/
DECLARE @Type NVARCHAR(MAX)
/* WHERE */
SET @Type = /* T3.["U_STEM_Level4Desc"] */ '[%3]'

/**SELECT FROM [OWHS] T4 **/
DECLARE @WH NVARCHAR(MAX);
/* WHERE */
SET @WH =/* T4.["County"] */ '[%4]'

SELECT @Discount1,@Discount2,@Discount3, @Type ,@WH

regards Lothar

[hogianto]

Hi,

have tried it and still error

[SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "@": line 3 col 3 (at pos 97)

i think it cannot contain @ also when i remove the @ it showed that something error around set syntax
remove the set know the parameter name is shown as error

any ideas?

[LoHa]

Hi Hogianto,

you can do it like that:

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [WTR1] T1 **/
DECLARE @ToWhs as nvarchar(max)
/* WHERE */
SET @ToWhs = /* T1.WhsCode */ '[%1]'

regards Lothar

[hogianto]

thanks loh that can pop up the parameter

but when i execute the query it says error

below is my code

/* SELECT FROM [dbo].[INV1] T0 */
DECLARE Discount1 DECIMAL(38,2);
DECLARE Discount2 DECIMAL(38,2);
DECLARE Discount3 DECIMAL(38,2);
Discount1 = /* T0."U_STEM_Disc1" */ '[%0]';
Discount2 = /* T0."U_STEM_Disc2" */ '[%1]';
Discount3 = /* T0."U_STEM_Disc3" */ '[%2]';
/**SELECT FROM [OITM] T1 **/
DECLARE @Type NVARCHAR(MAX)
/* WHERE */
SET @Type = /* T1.["U_STEM_Level4Desc"] */ '[%3]'

/**SELECT FROM [OWHS] T2 **/
DECLARE @WH NVARCHAR(MAX);
/* WHERE */
SET @WH =/* T2.["County"] */ '[%4]'
CALL CKM.CKM_COO_Report_Master_StockNMarginPHILIPS (Discount1 , Discount2  , Discount3 , @Type,@WH,output );
select * from :output ;

i also tried solution provide in https://answers.sap.com/questions/30424/sap-b1-query-generator-variables.html

but also same error

[SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "@": line 6 col 2 (at pos 175)

if i remove @

[SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "MAX": line 6 col 16 (at pos 189)

Below is my current

/* SELECT FROM [dbo].[INV1] T0 */
DECLARE Discount1 DECIMAL(38,2);
DECLARE Discount2 DECIMAL(38,2);
DECLARE Discount3 DECIMAL(38,2);
Discount1 = /* T0."U_STEM_Disc1" */ '[%0]';
Discount2 = /* T0."U_STEM_Disc2" */ '[%1]';
Discount3 = /* T0."U_STEM_Disc3" */ '[%2]';

DECLARE @Type NVARCHAR(MAX);
SET @Type = (SELECT max(T1."U_STEM_Level4Desc") FROM [OITM] T1 where T1.["U_STEM_Level4Desc"] = '[%3]');
DECLARE @WH NVARCHAR(MAX);
SET @WH = (SELECT max(T2."County") FROM [OWHS] T2 where T2.["County"] = '[%4]') ;
CALL CKM.CKM_COO_Report_Master_StockNMarginPHILIPS (Discount1 , Discount2  , Discount3 , @Type,@WH,output );
select * from :output ;