#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0

    Query output help


    Hi there,

    I'm having some trouble with a postgreSQL statement.

    I'm trying to get multiple database cells from a table using this query:

    Code:
    SELECT data FROM data_table WHERE (name='w' OR name='x' OR name='y' OR name='z')
    When I get output, I have specified a separator for output, so between each result is the separator |

    I have two questions about receiving output.

    1. Is there a way to 'sort' the output according to the order it is in the statement? (W will be first in the output, X will be second etc)

    2. In some instances there will be no data returned for a name. Is it possible, in the event that nothing is returned, to have a default value returned instead of just nothing?

    If I have 0 data on 1 name for instance it will return as
    "Wdata|Xdata|Ydata" (if z data is 0)

    Is there a way to instead have:
    "Wdata|Xdata|Ydata|nodata" where "nodata" is returned when there is no data received for that query.

    Thanks very much to anybody that can help me.

    Rob
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    When I get output, I have specified a separator for output, so between each result is the separator |
    Not with the statement you have show us.

    I'm trying to get multiple database cells from a table using this query:
    The correct term is column, not cell. And they are not an attribute of the database but of the table. So it should be "multiple colums from a table". But your statement only returns a single column (named data). It might however return multiple rows. So what exactly do you want to have?

    1. Is there a way to 'sort' the output according to the order it is in the statement? (W will be first in the output, X will be second etc)

    2. In some instances there will be no data returned for a name. Is it possible, in the event that nothing is returned, to have a default value returned instead of just nothing?
    Assuming with "nothing" you actually mean NULL values, then something like this:
    Code:
    with some_values (name, srt_order) as (
       values ('w',1), ('x',2) , ('y',3), ('z',4)
    )
    select v.name, coalesce(d.data, 'nodata)
    from some_values v
      left join data_table d on d.name = v.name
    order by v.srt_order
    The statement you showed us, will not produce the output you have shown us. If you want to have all rows in a single row, you need to use an aggregate function, e.g:
    Code:
    with some_values (name, srt_order) as (
       values ('w',1), ('x',2) , ('y',3), ('z',4)
    )
    select string_agg(v.name||coalesce(d.data, 'nodata), '|' order by v.srt_order)
    from some_values v
      left join data_table d on d.name = v.name
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0
    Thanks very much for your help, through your examples I was able to achieve this. Sorry for my noob terminology though.

IMN logo majestic logo threadwatch logo seochat tools logo