How to extract data from a list property

Say I have a property in  a persistent class that stores list of colours and I would like to query that field and return  a list and be able to loop that list to get individual colours how will l go about achieving this I have tried something like this but its not working as expected

 

 

 &sql(SELECT colour INTO :colourList FROM favouritecolours)
 While (SQLCODE = 0) 
{
 for i=1:1:$LENGTH($P(colourList,","))
 {
 set fvalue=$P(colourList,",",i) 
write "the first"_fvalue,i, 
} 
}

 

 

the query returns colours but its just a string no delimiter eg;

green red blue

 

I have tried with

&sql(SELECT LIST(colour) INTO :colourList FROM favouritecolours)  

but still same results . What I would like to achieve is to get maybe  colours in string like eg;

green,red,blue

 

Answers

Sorry you didn't  publish the definition of  color  .

so assuming it is a $LB( ) structure   $LISTTOSTRING(color,',') may cover your needs.  It's an SQLFUNCTION too!

just LIST(color)   is wrong as it produces a comma-separated list of MULTIPLE records.