--Howdy:
--I figured out what was happening with the data
--I'm pulling from DB2. There are some funky characters
--in the table that I need to get rid of. Thinks like back slashes
--and ' ^ ' and like.
--While I've heard that it is perfectly legal to have these
--characters in the table, that does not make it
--data that I can use.
--So, I need to readdress this question: Instead of
--returning a null or space, is there a way to search
--and remove data fields that have characters like
-- " \ " in them?
--Thanks!
-X
-----Original Message-----
Sent: Friday, June 06, 2003 11:12 AM
To: [EMAIL PROTECTED]
Subject: sql to return space or null field
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
