Results 1 to 2 of 2
Dec 18, 2009, 07:20 #1
- Join Date
- Nov 2004
- 1 Post(s)
- 0 Thread(s)
Problem with READ INTO OUTFILE and NULL values
I'm producing a file every couple of minutes that needs to be processed by a very old (1984) SunOS machine. The file has to be presented in a flat format where individual fields need to be padded out to a particular length. I've had a couple of problems with this which I've solved, but the big one at the moment is values that are NULL, I'm getting the whole field reduced to \N instead of being padded out.
Another problem is that when I force in a NULL value I'm getting the result padded out further than I should:
SET @sql_text = CONCAT ( "SELECT CAST(LPAD(4, 2, '0') AS UNSIGNED) , CAST(LPAD(12345, 6, '0') AS UNSIGNED) , RPAD('W1',3,' ') , RPAD(NULL, 3, ' ') , CAST(RPAD(1, 1, '1') AS UNSIGNED) into outfile 'TEST-" , DATE_FORMAT( NOW(), '%Y%m%d%H%i%s') , ".txt' FIELDS TERMINATED BY '' LINES TERMINATED BY '\n';" ); PREPARE s1 FROM @sql_text; EXECUTE s1; DROP PREPARE s1;
4 12345 W1 1 121234561231231
Can anyone help? Do I need to put an IF/ELSE statement on every line that could be a NULL value in the SELECT part of the query?
By the way, the reason that a lot of those values are CAST is because I kept getting hex values returned that I thought were causing other problems so I was just being thorough.
MySQL v5.1.30 by the way