How to use QV table data set in SQL

[#1]

HI All,

I have one query from QV and I would like to put it into the sub query?

Here is the sample:

IDList:
Load Id Inline
[
Id
'500900000111mClAAI'
'500900000111mCgAAI'
]
;


SQL SELECT CaseId,
CreatedById,
CreatedDate,
Field,
Id,
IsDeleted,
NewValue,
OldValue
FROM CaseHistory
where CaseId in
(
The result from IDList
)

Can we do that?

Thanks.

[sunny_talwar]

I believe it is possible by saving your list into a variable and then calling that variable in your SQL query.

[tamilarasu · Accepted solution]

Hi Isaac,

I haven't tested but you can try,

IDList:
Load Id Inline
[
Id
500900000111mClAAI
500900000111mCgAAI
]; 

Temp:
LOAD Concat(Distinct Chr(39) & Id & Chr(39),',') as TempID Resident IDList;

Let vID = FieldValue('TempID',1);
Drop Table IDList, Temp;

SQL SELECT CaseId,
CreatedById,
CreatedDate,
Field,
Id,
IsDeleted,
NewValue,
OldValue
FROM CaseHistory
where CaseId in
(
'$(vID)'
)

[jonathandienst]

That should work, except for the extra quotes in the sql statement:

SQL SELECT CaseId, 

     CreatedById, 

     CreatedDate, 

     Field, 

     Id, 

     IsDeleted, 

     NewValue, 

     OldValue 

FROM CaseHistory 

where CaseId in 

( 

$(vID)

)