I have a query while loading data into Target table.The requirement is to laod a flat file to teradata database.below are the details:
Source file data:
I would load the data as-is and then process it within database.
E.g. load as a single VarChar and extract the columns using STRTOK:
STRTOK(x, ',', n)
n will be hard-coded (one SELECT per name/value pair plus UNION ALL) or from a CROSS JOIN to a number table.
Additionally you need to find the DAY/MONTH columns, to add a WHERE n < dmPos:
CHAR_LENGTH(RTRIM(x, ', ')) - CHAR_LENGTH(OTRANSLATE(RTRIM(x, ' ,'), ',', '')) AS dmPos
-- column number of the DAY/MONTH columns
thanks for your rsponse.
BUT HERE THE PROBLEM IS THE NUMBER OF VALUES ALONG A ROW IS NOT FIXED.IN SOME ROWS IT IS 28,20,4 ETC.AND ALSO THE POSITION OF DATA AND DAY IS NOT CONSISTENT.PLEASE Elaborate more on this.i'm unable to understand.Please respond!!!!!
Create a table with all the odd number between 1 and the maximum number of columns, e.g. 27 and then cross join:
CREATE TABLE odd_numbers(n int);
INSERT INTO odd_numbers VALUES (1);
INSERT INTO odd_numbers VALUES (3);
INSERT INTO odd_numbers VALUES (27);
CHAR_LENGTH(RTRIM(x, ', ')) - CHAR_LENGTH(OTRANSLATE(RTRIM(x, ' ,'), ',', '')) AS endPos
,STRTOK(x, ',', n)
,STRTOK(x, ',', n+1)
,STRTOK(x, ',', endPos)
,STRTOK(x, ',', endPos+1)
FROM odd_numbers CROSS JOIN vt
WHERE n < endPos