Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    20

    Unanswered: Subselect - Result in one Row, comma separated

    Hello,

    Please be advised that I have a complex SQL Select Statement that joins various tables. Within this statement I want to do the following (not sure if this is possible):

    As 3rd field in the select part of that complex SQL statement, I want to enter a subselect that runs over a table called "car_availablility" which has the following layout:

    Table car_availablility layout
    Car -------- Owner -------------Currently_available
    BMW -------- Dirk -------------- YES
    VW --------- Dirk -------------- YES
    Skoda ------ Dirk -------------- YES
    Ford ------- Dirk -------------- NO
    Seat ------- Marco ------------- YES
    VW --------- Marco ------------- NO
    Opel ------- Marco ------------- YES

    the subselect looks as follows (with a link to the a.Owner field from the main complex select statement)
    (select Car from car_availablility where a.Owner = car_availablility.Owner and Currently_available = 'YES') as Car_Overview


    The issue is now the following:
    Instead of having the result from the subselect shown on multiple lines, I want to list the values behind each other comma separated within the same field.

    Example:
    Instead of having the cars for Dirk listed as this:
    Car_Overview
    BMW
    VW
    Skoda

    I want to have them listed as
    Car_Overview
    BMW, VW, Skoda


    Is this possible just within a select statement (I cannot use stored procedures etc as my user just has select privileges)

    Systeminfo:
    Database server = DB2/NT64 9.5.8


    Thanks a lot in advance!
    Regards,

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    You can use xmlagg to group the rows into one string.
    the examle of xmlagg usage can be easily found by google searching.
    How do I concatenate strings from a column into a single row in DB2 - DB2 Database

  3. #3
    Join Date
    Sep 2012
    Posts
    20
    Hello,

    I tried it with the link you suggested, so with the following piece of SQL:

    SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
    car))),'<A>',''),'</A>',' ')
    FROM car_availablility

    But its not working correctly. If I just execute:

    SELECT xmlagg(xmlelement(Name "a", car))
    FROM car_availablility

    I get one xml element that lists the cars as follows:
    <a> BMW </a><a>VW </a>.........

    but as soon as I include the xml2clob function (As far as I understand this function is there to transform an xml element to a real string) in the query, so like this

    SELECT xml2clob(xmlagg(xmlelement(Name "a", car)))
    FROM car_availablility

    I get the following message:
    SQL0423N Locator variable "1" does not currently represent any value SQLSTATE-0F001

    Does anyone know how to solve this issue or has a different idea for my issue to concatenate column values to a single string?

    Thanks in advance!

  4. #4
    Join Date
    Sep 2012
    Posts
    20
    Tried now with XMLSERIALIZE instead of xml2clob and this works, at least with varchar like below

    SELECT XMLSERIALIZE(CONTENT xmlagg(xmlelement(Name a, car)) AS VARCHAR(32599))
    FROM car_availablility

    But as soon as I try to use Clob instead of varchar so like this

    SELECT XMLSERIALIZE(CONTENT xmlagg(xmlelement(Name a, car)) AS CLOB) FROM car_availablility

    I get exactly the same error message as mentioned in my previous post. Can somebody please explain me this?

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
    car))),'<A>',''),'</A>',' ')
    FROM car_availablility

    But its not working correctly
    which errors did you get?

    Does this work:
    Code:
    SELECT replace(replace(xmlagg(xmlelement(NAME a,car)),'<A>',''),'</A>',' ')
    FROM car_availablility

  6. #6
    Join Date
    Sep 2012
    Posts
    20
    Quote:
    SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
    car))),'<A>',''),'</A>',' ')
    FROM car_availablility

    But its not working correctly

    which errors did you get?
    As said I get the following one:
    SQL0423N Locator variable "1" does not currently represent any value SQLSTATE-0F001


    When I try to run your code the error message I am getting is the following one:
    IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named "REPLACE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

Posting Permissions

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