Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use QV table data set in SQL

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.

 

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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)'

)

View solution in original post

3 Replies
sunny_talwar

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

tamilarasu
Champion
Champion

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
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein