| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-17-07, 12:14
|
|
Registered User
|
|
Join Date: Jul 2002
Location: ATL
Posts: 170
|
|
|
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
|
|
07-17-07, 12:27
|
|
Registered User
|
|
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
|
|
07-17-07, 12:30
|
|
Registered User
|
|
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)
|
|
07-17-07, 12:31
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
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
|
|
07-17-07, 12:58
|
|
Registered User
|
|
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
|
|
07-17-07, 13:37
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
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?
|
|
07-17-07, 14:02
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
koganti, please don't double-post
i've merged your two threads, and you can see how much confusion you caused 
|
|
07-17-07, 21:28
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
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
|
|
07-18-07, 06:20
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
|| is the standard SQL concatenation operator. CONCAT can also be used as synonym.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
07-18-07, 16:43
|
|
Registered User
|
|
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 
|
|
07-20-07, 16:47
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
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
|
|
07-23-07, 01:56
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Format SQL Output
