Can you use @Store in SQL report?

Ian Wheeler

• June 17, 2022 12:45

Hi I would like to use the @Store system in my SQL report with a SQL stored procedure

EXEC    [dbo].[testmultselect] @WO = N'@Store2',    @Qty = @Store1

Invalid query tree  [300-33]. The SQL error is 8180: SQL: EXEC    [dbo].[testmultselect] @WO = N'@Store2',    @Qty = @Store1

Vote

0

• 

  Dave Gutman

  • June 17, 2022 13:35
  • Edited

  Hi Ian,

  When using SQL Reports you can use the Dynamic Syntax elements.  For example: 
  SELECT "my field" FROM "RDR1" WHERE "RDR1"."ItemCode"='$[B1DASHBOARDVALUE.STRING]'  OR  "RDR1"."ItemCode" = $[$38.1.0]

  When using @STORExxx assignments put single quotes around the @STORExxx when using @STOREs in SQL functions on an assignment if the data is a string (the single quotes are passed directly to the generated SQL command, the @STORExxx is converted to its current value).  For example in HANA: 
  @STORE910 = SQL(SELECT "your field" FROM "your table" WHERE "your other field" = '@STORE3'); 

  When using EXECUTESQL() and the value is numeric then do not use either type of quote around the @STORExxx variables.  Likewise don't use the N prefix.  For example:
  EXECUTESQL(SELECT "your field" FROM "your table" WHERE T0."your other field" = @STORE3 );
  I've found it helps to see the actual query B1UP generates from your command, say in the debugger.  Copy that code into a query window and get it working there, then put the changes back into your B1UP code.

  Hope this helps.
• 

  Ian Wheeler

  • June 20, 2022 13:30

  Hi
  In the SQL Report function I am still not correct and get and error 

   

  select PD.ItemCode Component         , ('Store1'*avg(BaseQty)*COUNT(PD.ItemCode)) -isnull(CS.WhsStock,0)  [Missing Stock]         ,isnull(TF.WhsCode,'') [From]         ,isnull(TF.BinCode,'') [From BinCode]      from OWOR PH inner join WOR1 PD on PH.DocEntry=PD.DocEntry inner join dbo.OITM I On PD.ItemCode=I.ItemCode left outer join [BJL_StagingDataBase].[BJL_Production_Inventory].[BJL_ItemCodeByBuyerDetails] B on PD.ItemCode=B.ItemCode left outer join (SELECT [ItemCode]                    ,[WhsCode]                    ,sum ([BinQty]) WhsStock                    FROM [BJL_StagingDataBase].[BJL_Transfer_Inventory].[ProdStockByBin]                     group by [ProdItemCode]                    ,[ItemCode]                   ,[WhsCode]) CS on PD.ItemCode=CS.ItemCode and PD.wareHouse=CS.WhsCode LEFT outer join (SELECT [ItemCode]                    ,[WhsCode]                    ,BinCode                    ,BinAbsEntry                    ,[BinQty]                    ,SUM(BinQty) over (partition by ItemCode order by WhsCode,BinQty) CummStock                    FROM  [BJL_StagingDataBase].[BJL_Transfer_Inventory].[ProdStockByBin]                    where WhsCode in('RM_Leam','INSP','SC','INSP_Lea')                     and BinQty>00) TF on PD.ItemCode=TF.ItemCode and TF.[WhsCode]<>PD.wareHouse Where PH.DocNum='50119' and I.InvntItem='Y' --and isnull(TF.CummStock,0)>0 group by PH.DocEntry, PH.DocNum         ,PH.Status         ,PH.ItemCode          ,PH.PlannedQty-CmpltQty-RjctQty         ,PD.ItemCode         ,I.ItemName         ,I.PrcrmntMtd         ,IssuedQty         ,PD.wareHouse         ,isnull(CS.WhsStock,0)         ,isnull(TF.WhsCode,'')          ,isnull(TF.BinCode,'')          ,ISNULL(TF.BinAbsEntry,'')          ,ISNULL(TF.BinQty,0)         ,ISNULL(B.FullName,'')         ,isnull(TF.CummStock,0)         ,I.InvntItem having  CASE when ISNULL(TF.BinQty,0) > ('Store1'*avg(BaseQty)*COUNT(PD.ItemCode)) -isnull(CS.WhsStock,0)                 and isnull(TF.CummStock,0)-ISNULL(TF.BinQty,0) <= ('Store1'*avg(BaseQty)*COUNT(PD.ItemCode)) -isnull(CS.WhsStock,0)  Then 'Y'                 else 'N' end='Y' Order by isnull(TF.BinCode,'') DESC

Please sign in to leave a comment.