I have to deal with an older database. In this database no relation was used, instead the references have been stored as a comma separated list :-( Now I need to execute a statement like this
My problem is, that 1,2,3 is a value stored in one row of another table
So how to combine this in SQL? My naive approach
results in: convert 1,2,3 to numeric not possible. asked 04 Oct '10, 10:05 Martin |
If you are on V10+, you can use sa_split_list() system procedure to break comma-separated list into individual items, like this:
answered 04 Oct '10, 10:27 Dmitri thanks, I have looked for a normal function but didn't remebered the SQL Anywhere specific ones. 1
@Dmitri: the idea is ok, anyway sa_split_list is not accepting the select, but it works if I use a temporary variable instead @Martin: You will have to put the select in its own pair of brackets, as its used as a subquery expression, methinks, i.e. ...sa_split_list ((select xid from y where yid=1), ','))... 1
@Volker: You can't use a query as a parameter to a function/procedure call so Martin's solution of using a temp variable is the way to resolve the issue. @Mark: Thanks for correcting me - should have tested before posting:) |