Question
· Jan 22, 2019

SQL select to a list

I need to select my result into a list and be able to loop through the list when query finished any help appreciated here is where I am

##sql(SELECT %ID INTO :IDArray() FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID = :Key AND EndDate IS NULL)
for I=1;1:$LISTLENGTH(IDArray)

{

w $Data(IDArray),i

 


}

Discussion (2)2
Log in or sign up to continue

If you're curious, this is why what you tried originally got you unexpected results.

You passed the embedded sql statement an array with a missing subscript,  :IDArray(), hoping it would put your first result in IDArray(1), your second in IDArray(2), etc.  The problem with that is that a single embedded sql statement like the one you used will only ever return one result row: the first row that matches the criteria.  So what you were likely seeing is your first valid ID stored in IDArray(1), and none of the other ones you wanted present.

So why can it accept an array as an output argument at all, then?  When embedded sql sees an array as an output argument, it gives that array a subscript for each of the columns in the result row.  So IDArray(1) wasn't the first ID, but rather the first column of the returned sql row.  For example, &sql(select id, name, age into :result() from Person) would return the id into result(1), name into result(2), and age into result(3).

Hope this helps.  Good luck!