Add Where Clause to SQL "In" function

Permanently deleted user

August 22, 2017 14:47

I build a where clause from a list of stations and add the commas and brackets. For example:

 

('Stn1','Stn2','Stn')

 

This is then assigned to a parameter that is being fed into a sql statement. For example:

 

Select * from Stations where location_code in @Stations

 

But I get an error "Incorrect syntax near @Stations

 

But if I use the where clause in SQL manager with the same query, it works fine.

 

Any thoughts?

 

Walter

0

• 

  Permanently deleted user

  August 23, 2017 07:25

  Hi Walter,

   

  Which underlining DB are you using? SQL server, Oracle, ….

   

  Is the error above notified by the workflow (GE) or the DB?

   

  Can you please provide the activities that build the string and sql query activity?

   

  Thank you

  0
• 

  Permanently deleted user

  August 23, 2017 11:44

  Morning Francesca,

   

  I'm using SQL server as the engine. The query in question does not yield any results, but it should.

   

  If you're attempting to pass in a string where clause for the sql script my paramter is:

   

  @Location "In" "String whereclause(the variable name)

   

  And then when you pass in the parameter... here's my code

   

  "and station in @Location" ......which should translate to " and station in ('Stn1, Stn2, Stn3')"

   

  This does not work and does not produce any records. But when I declare/set this value in SQL Manager, it works.

   

  Walter

  0
• 

  Permanently deleted user

  August 23, 2017 12:32

  We use same notation in a few workflows …but with Oracle.

   

  I would try:

  • Log the string before the sqlQuery activity to be sure that is really built as above and there are not syntax problems
  • Use string generated in the log (as per above) to test query in SQL Manager (and potentially running it from simulator
  • Ensure that in sqlQuery activity the parameter name is identical in the parameters section and command section (paying particular attention to lower and upper case)
  • Try to replace @ with : in  command section (to make it look like and station in :Location )(This might be a Oracle thing but it works for us)

  Let us know if you find the solution ...

  0
• 

  Permanently deleted user

  August 23, 2017 13:07

  Francesca,

   

  Appreciate your input. Tried your ideas. The log string works in SQL. 

   

  I've changed my design and am now dumping that string value into a #tempTable

   

  and then joining on that table. Just working out the kinks right now.

   

  Walter

  0
• 

  Permanently deleted user

  August 23, 2017 13:16

  It’s a pity that you had to change the logic for a simple scenario like this :-(

  0
• 

  Permanently deleted user

  August 23, 2017 13:22

  I think the workflow is trying to check formatting of the SQL statement before running it and when it checks Select * from Stations where location_code in @Stations it doesn't like that there aren't parenthesis after 'in'.  

   

  I think if you were to change your @Stations variable to be 'stn1','stn2','stn3' and wrote your query as Select * from Stations where location_code in (@Stations) it should work.  Or at least pass the query on without the syntax error

  0
• 

  Permanently deleted user

  August 23, 2017 13:28

  Marshall,

   

  Correct. The incoming whereclause is as such : ('Stn1,Stn2,Stn3')

   

  I will remove the brackets and test our your idea. 

   

  Thanks for your input Marshall

   

  Walter

  0
• 

  Permanently deleted user

  September 20, 2019 22:44

  Did you ever figure this out? I have the exact same problem.  It works in management studio, it works if I hard code it in the command text, but it hates using the @xxx.

   

   

  Thanks

  0

Please sign in to leave a comment.