Redbrick List Forum

Re: Conversion of zero to Null in TMU load script?

Posted By:
Date:

Steve,

You're sunk trying to use NULLIF with a separated input file. Since the
NULLIF is position specific, the variable nature of a separated file would
make it's use suspect, so it gets errored out.

What you could do is use a psuedocolumnname for contractor id and then use
a REJECT clause for contractor_ids = 0. YOu could then load the reject
file (of contractor_ids) after awk(ing) or sed(ing) the input file for that
field and remove the 0 entry. Depending on the way the table was created,
it may default to a null so you could just ignore what is coming in from
the reject input file (all zeros) and the column will automatically be
defaulted to null.

Let me know if you have any questions ...

-- Bill

*********************************************************************
William S. Gates IBM
Consulting I/T Specialist 300 Nichols Avenue
Data Management Svcs. Wilmington, DE 19803

Business Intelligence
IBM Red Brick Data Warehouse

Cell: (302) 438-0779 www.ibm.com
Office: (302) 477-1805 gatesw@us.ibm.com
Fax: (302) 477-1806
eFax: (707) 516-7055

*********************************************************************




"Steve Krueger"
<skrueger@baldwinan To: redbrick-list@iiug.org
dlyons.com> cc:
Sent by: Subject: Conversion of zero to Null in TMU load script? [28]
forum.subscriber@ii
ug.org


03/11/2003 01:26 PM






Fellow Red Brick users,
I am looking for a way in my TMU load script to convert fields with a value
of zero to null in the table. I tried using the NULLIF option, but
apparently the NULLIF option cannot be used with a 'SEPARATED format' input
file (TMU fatal error).
Is there another option I can use with a separated format input file?
Below is a sample tmu script (Version 6.11).
Thanks for any ideas or suggestions.

LOAD DATA
INPUTFILE 'e:\rdb1\icprogram_db\inputs\internet_ic_settle.txt'
INSERT
FORMAT SEPARATED BY '|'
DISCARDFILE
'e:\rdb1\icprogram_db\discards\settlement_Úte%_%time%.discard'
ROWMESSAGES
'e:\rdb1\icprogram_db\discards\settlement_Úte%_%time%.messages'
INTO TABLE TEST (
settlementkey INTEGER EXTERNAL(9),
customerkey INTEGER EXTERNAL(9),
terminalkey INTEGER EXTERNAL(9),
contractorkey INTEGER EXTERNAL(9),
il_policykey INTEGER EXTERNAL(9),
name CHAR(30) RTRIM,
state CHAR(10),
monthly_amount DECIMAL EXTERNAL(10,2),
monthly_adjustment DECIMAL EXTERNAL(10,2),
expected_value DECIMAL EXTERNAL(10,2),
insured_value DECIMAL EXTERNAL(10,2),
adjusted_value DECIMAL EXTERNAL(10,2),
pdl_premium DECIMAL EXTERNAL(10,2),
ntl_premium DECIMAL EXTERNAL(10,2),
pd_premium DECIMAL EXTERNAL(10,2),
pd_premium_adjustment DECIMAL EXTERNAL(10,2),
work_accident_premium DECIMAL EXTERNAL(10,2),
work_accident_adjustment DECIMAL EXTERNAL(10,2),
received_date DATE 'YYYYMMDD',
entry_date DATE 'YYYYMMDD',
entry_time TIME 'HHIISSFF',
vehiclekey INTEGER EXTERNAL(9),
contractor_id INTEGER EXTERNAL(8) NULLIF (1:8) = '0',
account_number INTEGER EXTERNAL(4),
settlement_id INTEGER EXTERNAL(8),
adjustment_id INTEGER EXTERNAL(8),
reporting_date DATE 'YYYYMMDD',
ia_policykey INTEGER EXTERNAL(9),
dwh_update_date CURRENT_DATE
);

Messages In This Thread

• Conversion of zero to Null in TMU load script?
  Steve Krueger -- Tuesday, 11 March 2003, at 1:26 p.m.
  • Re: Conversion of zero to Null in TMU load script?
    William Gates -- Tuesday, 11 March 2003, at 2:10 p.m.
    • Re: Re: Conversion of zero to Null in TMU load script?
      QI JIN -- Tuesday, 11 March 2003, at 5:55 p.m.
    • Re: Conversion of zero to Null in TMU load script?
      John Kelleher -- Wednesday, 12 March 2003, at 7:49 p.m.

Redbrick List Forum is maintained by Administrator with WebBBS 5.12.