Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Posts
    13

    Unanswered: Need help with subquery

    Hi everyone!

    I have a query that will always return only one row. But I'd like for one of it's fields to be populated by a subquery that will possibly return more than one row.

    Here is a heavily stripped down version of it...

    Code:
    SELECT
    	name,
    	birthDt,
    	MRNum = (select MRNum from ExtPat where PID = 4545),
    	admitDtm,
    FROM
    	Pat
    Sybase is complaining that my subquery is returning multiple rows. How can I return multiple rows from a subquery and append all those rows into one field.

    For example, let's say that my subquery returns three rows, I'd like the above SQL statement to return something like this...

    Code:
    name    birthDt         MRNum                     admitDtm
    John    1980/10/02      32456, 34535, 65457       2008/04/04
    Is something like this possible?

    Thanks!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What you are looking to do here is not possible. You will either need to break down the query into two:

    Code:
    SELECT
    	name,
    	birthDt,
    	admitDtm
    FROM
    	Pat
    AND

    Code:
    select MRNum from ExtPat where PID = 4545
    OR have the following SQL

    Code:
    SELECT
    	name,
    	birthDt,
    	admitDtm,
    	MRNum
    FROM
    	Pat,
    	select MRNum from ExtPat where PID = 4545

  3. #3
    Join Date
    Sep 2008
    Posts
    13
    Thanks for the reply.

    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.

    Thanks!

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Sep 2008
    Posts
    13
    Do you have an example of how I can do this?

  6. #6
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    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. Reason: Clarity
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Fatbob73
    Is something like this possible?
    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 :
    Code:
    SELECT p.name,
           p.birthDt,
           list( ep.MRNum ) as MRNum,
           p.admitDtm
    FROM   Pat p,
           ExtPat ep 
    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 !

  8. #8
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    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.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •