Is it possible to insert the row(s) returned by my subquery into a temp table as one appended field? So in this temp table I'd have one row, with one field of all the appended values. Then my other select would simply grab that row from the temp table?
I forgot to mention that the example select I provided is in a stored proc.
I see what you are trying to achieve here, you want the subquery to return a set of comma delimited values as a string. In this case if you create a function which returns a concatenated set of values you can add this function directly into your original query.
Your function will include a cursor to scan through all the values and concatenate them to a string and return that string.
Yeah, it is two different programming issues: one to return a single row; a completely separate issue to construct a single concatenated string from more-than-one row. Figure out and resolve each issue separately, and bring them together at the end.
The problem is not "Sybase complaining ..."; it is you not understanding what you want; what you are attempting in SQL. You have an equal sign following MRNum; that means equality; but you have a subquery which returns more-than-one MRNum; that cannot be resolved, it is an error. You could either change the subquery to return one value; or you could change the "=" to "IN" which allows multiple values; which will overcome the error.
Getting what you want is a different issue, you need to understand (a) the tables as they are (b) what SQL to use and (c) how to concatenate more-than-one value from several rows into one value.
Tempdb is a slow and laborious method, quite unnecessary here; a Function is much easier and faster; you do need a cursor (I prefer a simple non-cursor while loop because it is less overhead than a cursor and avoids cursor-class locks).
First get select MRNum from ExtPat where PID = 4545 to load into a single concatenated column, instead of returning multiple values for each row. There are several methods, post what you have tried, then we can respond. Think about the max width (what do you do for more than 3 MRNum values/rows, when the specified new concatenated col width is exceeded).
Last edited by Derek Asirvadem; 09-18-09 at 02:17.
There is a list aggregate function which might help you. It doesn't appear to be available on all versions of Sybase but it might be available to you. I believe the following code does what you ask but I still think you're going about things the wrong way - ie the two tables don't appear to be related (no where clause) so why not have two selects :
list( ep.MRNum ) as MRNum,
FROM Pat p,
where ep.PID = 4545
group by p.name, p.birthDt, p.admitDtm
PS1 - if there are too many MRNum then you'll have an issue with the size of the string etc so better to pull as rows.
PS2 - nice picture Rudy !
Sybase ASE does not have a "list()" function, ASA does. Sybase ASE/ASE/ASIQ are different products (not versions, each has their own versions). AFAIK (and FatBob can confirm), most posters here are Sybase ASE.
Last edited by Derek Asirvadem; 09-20-09 at 07:03.