I'm having data in below format in input file (Max length 100)
1. ID - 9 Length Fixed START POSITION 1
2. First Name - 15 Max Variable Length START POSITION 11
3. Last Name - 22 Max Variable Length (This can have space between text) START POSITION 28
4. In Date - 10 Length Fixed START POSITION 55
5. Num1 - 9 Digits Fixed START POSITION 65
6. Num2 - 11 Fixed (May not be available in all cases) START POSITION 76
7. Out Date - 10 Length Fixed START POSITION 89
Sample:
111111111 FIRSTNAME LAST NAME 09/20/2011 123456789 11133344455 09/21/2011
I'm trying to build an output file (max length 80) using above input file.
INSERT INTO AAA.TABLENAME VALUES ('111111111','FIRSTNAME',
'LAST NAME','09/20/2011','123456789','11133344455','09/21/2011');
INSERT INTO AAA.TABLENAME VALUES ('111111111','FIRSTNAME',
'LAST NAME','09/20/2011','123456789','','09/21/2011');
The second sample output is when Num2 is not available
In the above output file i need to take care that no trailing spaces are getting inserted into the table AAA.TABLENAME.
Would appreciate if anyone can help me out of this !
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
sarbamrit,
I think you might have over-complicated it for yourself, or for us. You fields all have fixed start positions. You have two fields which could contain trailing blanks (and I suppose, potentially, could be entirely blank) and one which is a fixed length of 11, but which may be blank.
For those three fields, all trailing blanks are to be removed, even if the output for that field is only then represented by ''.
So, a simple "remove trailing blanks" question, plus putting in the quotes.
What is not so simple, or perhaps so simply dealt with, is the fact that your input requires splitting, which is only referred to in your title.
You have:
Code:
INSERT INTO AAA.TABLENAME VALUES ('111111111','FIRSTNAME',
'LAST NAME','09/20/2011','123456789','11133344455','09/21/2011');
To avoid having to think about how to calculate for the split always working, and to generalise the solution somewhat, would this be acceptable output for you?
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
we are building an INSERT statement and not a load file.
unfortunately, in db2 '' and ' ' are considered spaces when dealing with CHAR columns.
so, any CHAR columns with 0-length would have to have NULL instead of ''. (or ,,)
but, if the column is NOT NULL, then '' for spaces must be used.
unless of course the column attributes are NOT NULL WITH DEFAULT,
in which case you could use ,,
numeric columns CAN NOT have quotes around the values,
and again the rules for NOT NULL and NOT NULL WITH DEFAULT,
need to be followed.
if any column is NOT NULL but without a WITH DEFAULT,
then you need to supply some value.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You know what you are doing next with all this stuff? It has been suggested to me that you'd be much better using a load utility rather than inserts. What have you got to counter that argument?
