Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Unanswered: Format SQL Output

    I want to write a sql to select rows from a table. I would like the output to have each column value separated by a comma in each row.

    Iam trying the following sql and it does not seem to work. What am i doing wrong

    db2 -x "select TABSCHEMA||','||card from syscat.tables"

    I keep getting the follwoing error
    SQL0440N No authorized routine named "||" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    Thx ahead

  2. #2
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Format SQL in UDB

    I want to write a sql to select rows from a table. I would like the output to have each column value separated by a comma in each row.

    Iam trying the following sql and it does not seem to work. What am i doing wrong

    db2 -x "select TABSCHEMA||','||card from syscat.tables"

    I keep getting the follwoing error
    SQL0440N No authorized routine named "||" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    Thx ahead

  3. #3
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by koganti
    I want to write a sql to select rows from a table. I would like the output to have each column value separated by a comma in each row.

    Iam trying the following sql and it does not seem to work. What am i doing wrong

    db2 -x "select TABSCHEMA||','||card from syscat.tables"

    I keep getting the follwoing error
    SQL0440N No authorized routine named "||" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    Thx ahead

    to use '||' all the argument should be string. what's the datatype of column 'card'?
    you can try this TABSCHEMA||','||varchar(card)

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DB2, you say? I don't know anything about it, but - is there the double pipe operator for concatenation in DB2? It exists in Oracle (that's for sure), but in DB2?

    How about using CONCAT instead? Something like
    Code:
    SELECT tabschema CONCAT ',' CONCAT card
    FROM syscat.tables

  5. #5
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Following solved the problem
    db2 -x "select tabschema||','||varchar(char(card)) from syscat.tables"

    When you concatenate two fields then they should have same data type. If they are different data type then convert them to same data type.

    Thx
    Koganti

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Ah, lovely!

    But, do you really need VARCHAR(CHAR(card)) ? Once again - I don't know DB2 SQL, but - wouldn't VARCHAR(card) or even CHAR(card) be enough?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    koganti, please don't double-post

    i've merged your two threads, and you can see how much confusion you caused
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can also use the cast function:

    db2 -x "select tabschema||','|| cast(card as char(10)) from syscat.tables"

    CHAR and VARCHAR can be concatenated.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    || is the standard SQL concatenation operator. CONCAT can also be used as synonym.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    For Varchar function in UDB you cannot pass numeric. You can only pass char or date.

    Following worked
    db2 -x "select tabschema||','|| cast(card as char(10)) from syscat.tables"

    Thanks guys. You guys are awesome

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would do this:
    Code:
    LTRIM(CHAR(number))
    (or RTRIM - I can't remember which one is the right one)

    This will get rid of leading spaces.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    LTRIM - left trim (leading spaces)
    RTRIM -right trim (spaces at the end of string)

    In v9 there is also a TRIM function that trims left and right spaces.
    Hope this helps,
    Grofaty

Posting Permissions

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