Beas User Input Query

Respondida

Mark Alexander

• 18 de enero de 2018 14:51

We've just started using Beas and I'm formatting a few queries that combine SAP and Beas tables. I need to create a query that prompts the user for a value to search on. I can do this in SAP by using a parameter like [%0], but this does not work when I use it in a query with a beas table. The query I have is very simple:

SELECT t2.ItemCode, t2.SuppCatNum, t1.description, t1.menge_lager, t1.input_unit
FROM beas_stl t1 INNER JOIN OITM t2 ON t1.art1_id = t2.ItemCode
WHERE t1.ItemCode = '[%0]'

The [%0] doesn't work and I have to explicitly put in the Item Code value to get this to work

Vote

0

• Comentario oficial

  

  Daniel Gesk

  • 19 de enero de 2018 07:55

  Hello Mark Alexander,

  before the select-statement you have to declare a variable:

  /* Select FROM [dbo].[OITM] T1 */

  DECLARE @ItemCode NVARCHAR(20)

  /* WHERE */

  SET @ItemCode = /* T1.ItemCode */ '[%0]'

  Then you can use: ......where t1.ItemCode=@ItemCode

  Best regards, Daniel Gesk

   

   

   
• 

  Mark Alexander

  • 19 de enero de 2018 15:48

  Hi Daniel, thanks for the response.

  I did as you suggested but I still get an error. Here's my new SQL statement below:

  DECLARE @ItemCode NVARCHAR(100)
  SET @ItemCode = '[%0]'

  SELECT t1.ItemCode, t2.SuppCatNum, t1.description, t1.menge_lager, t1.input_unit
  FROM beas_stl t1 INNER JOIN OITM t2 ON t1.art1_id = t2.ItemCode
  WHERE t1.ItemCode = @ItemCode

  I get an error stating - Incorrect syntax near the keyword 'SET' (See image below)

  If I replace '[%0]', in the SET statement, with an actual value in the DB like '115626-01-rA', the query works with no error

  
• 

  Daniel Gesk

  • 22 de enero de 2018 22:36

  Hello Mark,

  you forgot some lines which I mentioned in my message. Please use the complete syntax I mentioned.

  (Lines before and after "Declare"; exact Syntax at SET @ItemCode - you can use copy and paste)

  Best regards, Daniel

   

   
• 

  Mark Alexander

  • 23 de enero de 2018 13:17

  Hi Daniel,

  Thanks for the response, I put the missing text into the query and it worked great. I didn't put them in originally as I thought they were comments (Similar to C++ code), and didn't realise they had significance to the statement. Thanks for all your help!

  /* Select FROM [dbo].[OITM] T1 */
  DECLARE @ItemCode NVARCHAR(100)
  /* WHERE */
  SET @ItemCode = /* T1.ItemCode */ '[%0]'

  SELECT t1.ItemCode, t2.SuppCatNum, t1.description, t1.menge_lager, t1.input_unit
  FROM beas_stl t1 INNER JOIN OITM t2 ON t1.art1_id = t2.ItemCode
  WHERE t1.ItemCode = @ItemCode
• 

  Martin Obojes

  • 14 de agosto de 2020 08:26

  Hello, this post is older, but I still have a question.
  In the example above, a SAP table is used for the variable. But I would need the same thing with the beas table ... so the window opens. I have rewritten this query here on the beas table, but it doesn't work.
  So in this case here it would be the beas_stl table. For me it's a different one, but it comes out to be the same.
  
  I would be very happy if someone could help me.
  
  Many thanks
  
  Martin O.

Iniciar sesión para dejar un comentario.