I am unloading few columns from my DB2 table, and running through SYNCSORT.
Each column is '|' delimited. The second column, shows following definition in DB2 table - Integer NULLIF (196) = '?'. Whats the best way to convert the column to ZD format, and replace all null fields with spaces. I tried searching the forum with vain, and going through manual. If someone could point me exactly how can I do this would appreciate it.
In DB2 statement, this will be equivalent of CHAR(COALESCE(<fieldname>,' ')) function.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
There is no "null character". If a column can be NULL, there will be a separate indicator for each row to tell you whether the value is NULL or not, and you use that. If you have not used that explicitly or implicitly in the preparation of your data, then you need to do that.
There is no "null character". If a column can be NULL, there will be a separate indicator for each row to tell you whether the value is NULL or not, and you use that. If you have not used that explicitly or implicitly in the preparation of your data, then you need to do that.
You are right. I havent been able to keep my head straight, and not sure what was i thinking when i wrote the original query..
Say:
EMP_IDNUM SMALLINT - position 1-3
EMP_NUM INTEGER (IF NULL POS(4) = '?') [ records with no emp_num suggests "contractor"/"consultant"] position 4-7
EMP_NAME VARCHAR position 8-50
EMP_AMOUNT DECIMAL(11,2) position 50 onwards
Varchar normally has first 2 bytes of the particular field to indicate the type of data. Ifnull for an integer would have last byte turned on for null character.
If you can let me know, if it would be INREC and PARSE that could achieve this, it would be helpful.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Gernerally, if the data is variably-located, you would use PARSE. If in fixed locations, of fixed size, just define the fields (start-positio,lenght - without data-type in a BUILD/OVERLAY/PUSH (generally) and with data-type for an IFTHEN.