Posted: Tue Jul 27, 2010 11:09 am Post subject: Convert all columns in SELECT * to CHAR
In a DB2 query, I need to convert columns of decimal data type to character so the DB2 data type will match the receiving COBOL field.
An example would be:
SELECT CHAR(COL_NAME_DECIMAL)
INTO :CHAR-DEFINED-FIELD
FROM TABLE_NAME
Where I run into a problem is that I'd like to use "SELECT *" rather than specify column names. I'm trying to place the rows of data into a generic COBOL table made up of character fields. Most of the data on the DB2 table is defined as CHAR, but there is a DECIMAL value here and there. Everything works fine until I run into the decimal data. In that case, I receive SQLCODE -303, Error: A VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER BECAUSE THE DATA TYPES ARE NOT COMPARABLE .
Using "SELECT *" rather than specifying all the fields will allow me to reuse the same generic code for multiple DB2 tables. It will also allow me to avoid making changes in the future due to any modifications in the table structure (column name change, deleted/added column, etc).
Any ideas on how to place this CHAR/DECIMAL data into character defined fields using SELECT *?
Joined: 26 Nov 2002 Posts: 12368 Topics: 75 Location: San Jose
Posted: Tue Jul 27, 2010 12:36 pm Post subject:
DiscoStuLikesDiscoMusic,
Here is an untested SQL which will generate a SQL select statement checking the property of the column from sysibm.syscolumns table.
Code:
SELECT A.COL_NAME
FROM (SELECT SUBSTR(CHAR(CASE
WHEN COLNO = 1 AND
COLTYPE NOT IN ('FLOAT'
,'SMALLINT'
,'INTEGER'
,'DECIMAL')
THEN CHAR('SELECT ') || CHAR(NAME,30)
WHEN COLNO > 1 AND
COLTYPE NOT IN ('FLOAT'
,'SMALLINT'
,'INTEGER'
,'DECIMAL')
THEN CHAR(' ,') || CHAR(NAME,30)
WHEN COLNO = 1 AND
COLTYPE IN ('FLOAT'
,'SMALLINT'
,'INTEGER'
,'DECIMAL')
THEN CHAR('SELECT CHAR(') ||
SUBSTR(NAME,1,LENGTH(RTRIM(NAME))) ||
CHAR(')')
WHEN COLNO > 1 AND
COLTYPE IN ('FLOAT'
,'SMALLINT'
,'INTEGER'
,'DECIMAL')
THEN CHAR(' ,CHAR(') ||
SUBSTR(NAME,1,LENGTH(RTRIM(NAME))) ||
CHAR(')')
END),1,80) AS COL_NAME
,COLNO AS COL_NO
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'Your table name'
UNION
SELECT B.COL_NAME
,B.COL_NO
FROM (SELECT CHAR(' FROM TABLE_NAME') AS COL_NAME
,MAX(COLNO) + 1 AS COL_NO
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'your table name') B) A
ORDER BY A.COL_NO
;
Last edited by kolusu on Wed Jul 28, 2010 7:12 pm; edited 1 time in total
Joined: 12 Jun 2007 Posts: 64 Topics: 17 Location: Hyderabad
Posted: Tue Sep 21, 2010 2:15 pm Post subject:
Hi All,
I understood the first part of the query, which checks whether a column is of the float, decimal, smallint or integer and then converts into a char type.
Here, I have a question:
1. why a separate condition was given for '=1' and '>1', we can go ahead with '>=1'.
2. And what is the importance of 2nd query i.e. the one mentioned after UNION.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
