If I understand your question correctly, you can look up the CAST function for example:
select cast(tabname as char(12)) from syscat.tables
Or even SUBSTR
select substr(tabname,1,12) from syscat.table
To convert blanks to null, look up NULLIF or CASE. Here is an excerpt for the NULLIF function:
-NULLIF--(--expression--,--expression--)---------------------><
The NULLIF function returns a null value if the arguments are equal, otherwise it returns the value of the first argument
Good luck
From: "Johnson, Shaunn" <[EMAIL PROTECTED]> Reply-To: "Johnson, Shaunn" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [DB2EUG] sql to return space or null field Date: Fri, 6 Jun 2003 11:12:02 -0400
Howdy:
I'm working with DB2 OS/390 6.1.2.
I'm trying to write a query where return a few rows and append that data into a text file.
[snip some code] select c_auth_num, c_auth_reason, c_bill_prov, c_claim_num, c_claim_type, c_contract_num, c_denied_reason, c_diag_1 from hdxx0000.htxx0_clxxdemo where d_from_dt between '01/01/2001' and '06/30/2001' and c_stat_cd like 'Y%' and c_stat_reason not in('AV','VC','VS','Y15') and c_diag_1 not like 'INT%' and c_proc_cd not like 'INT%' and c_type_serv <> 'IN' [/snip some code]
This seems to work find ... sometimes. What I am looking at is that somehow, some where, when I want to manipulate the data, send it to another database or whatever, I get errors like these
[snip error] copy: line 1284849, value too long for type character(12) [/snip error]
And when I try to look at the text file and figure out what it's complaining
about (matching the field against what the table structure is)
and it *looks* like it could fit, but there could also be something
in the field that may / may not belong. It is my understanding that
DB2 accepts almost all ANSI characters so, even if it was a
^M or whatever, it would *still* fit in the table (as far as DB2
is concerned).
My question: Is there a way to explicitly say select column (set length to 12 characters) from table? OR, maybe better if there is a way to say:
if the field returns a blank, force it to be null ?
Thanks in advance!
-X
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
- ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
