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

select * from x where xid in ( 1,2,3 )

My problem is, that 1,2,3 is a value stored in one row of another table

select xid from y where yid = 1 => '1,2,3'

So how to combine this in SQL? My naive approach

select * from x where xid in ( select xid from y where yid=1 )

results in: convert 1,2,3 to numeric not possible.

asked 04 Oct '10, 10:05

Martin's gravatar image

Martin
8.6k118151237
accept rate: 14%


If you are on V10+, you can use sa_split_list() system procedure to break comma-separated list into individual items, like this:

select * from x where xid in ( select row_value from sa_split_list (select xid from y where yid=1 ), ',')

permanent link

answered 04 Oct '10, 10:27

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

thanks, I have looked for a normal function but didn't remebered the SQL Anywhere specific ones.

(04 Oct '10, 11:54) Martin
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

(04 Oct '10, 11:56) Martin

@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), ','))...

(05 Oct '10, 07:42) Volker Barth
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.

(06 Oct '10, 00:17) Mark Culp

@Mark: Thanks for correcting me - should have tested before posting:)

(06 Oct '10, 07:35) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 04 Oct '10, 10:05

question was seen: 728 times

last updated: 04 Oct '10, 10:27