Use variable in SQL IN query

Help Activities
, ,
1

i want to pass variable in_varStr(‘1’,‘788’,…),
tried many ways but its throwing error

“Select Distinct
wp.WorkOrderNumber PreReqWorkOrderNumber
, wp.ParentWorkOrderNumber
, w.CommodityType
From dbo.Workorder w, dbo.WorkOrderPrerequisite wp,ServiceRequestWorkType_SYS SRWT
Where 1 = 1
And w.WorkOrderNumber = wp.ParentWorkOrderNumber
And wp.WorkOrderNumber In (in_varStr)”

Forum Engagement Daily Reports
2

@Shilpa_Mohanty

open advanced editor instead of edit query…and use as below

"Select Distinct
wp.WorkOrderNumber PreReqWorkOrderNumber
, wp.ParentWorkOrderNumber
, w.CommodityType
From dbo.Workorder w, dbo.WorkOrderPrerequisite wp,ServiceRequestWorkType_SYS SRWT
Where 1 = 1
And w.WorkOrderNumber = wp.ParentWorkOrderNumber
And wp.WorkOrderNumber In (" + in_varStr + ")"

cheers

3

Hi @Shilpa_Mohanty

Use below syntax

“Select Distinct
wp.WorkOrderNumber PreReqWorkOrderNumber
, wp.ParentWorkOrderNumber
, w.CommodityType
From dbo.Workorder w, dbo.WorkOrderPrerequisite wp,ServiceRequestWorkType_SYS SRWT
Where 1 = 1
And w.WorkOrderNumber = wp.ParentWorkOrderNumber
And wp.WorkOrderNumber In (“+in_varStr+”)”

you can use like this it will work

4

image
image1588×475 46.1 KB

5

image
image1588×475 46.1 KB

error

6

@Shilpa_Mohanty

  1. Did you open advanced editor only?
  2. try to remove new lines…and have all the query in single line that should sort the error

"Select Distinct wp.WorkOrderNumber PreReqWorkOrderNumber, wp.ParentWorkOrderNumber, w.CommodityType From dbo.Workorder w, dbo.WorkOrderPrerequisite wp,ServiceRequestWorkType_SYS SRWT Where 1 = 1 And w.WorkOrderNumber = wp.ParentWorkOrderNumber And wp.WorkOrderNumber In (" + in_varStr + ")"

cheers

7

try to replace double quotes in the place of double quotes from your keyboard.

if still facing issue
use _ at the the end of each line
or
store in one variable using asssign activity and use that variable

8

this helped
was struggling from yesterday
thankyou so much

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.