0

I want to generate a fixed width file format.

I am trying to cast brith_day, birth_month and birth_year with fixed length but if any of these fields are null should remain the space using coalesce function.

The second part of the query (LastGiftDate and LastContactDate) somehow does not get the fixed width output. I want to use cast(coalesce...

select        cast(birth_day , ' ') as char(2))|| 
              cast(birth_month, ' ') as char(2))||
              cast(birth_year, ' ') as char(4))|| 
              cast(coalesce(gender, ' ') as char(2))|| 
              to_Char(LastGiftDate,'yyyymmdd')||
              to_Char(LastContactDate,'yyyymmdd')||

from table_t1

Data Type

BIRTH_YEAR NUMBER (4)
BIRTH_MONTH NUMBER (2)
BIRTH_DAY NUMBER (2)
LASTGIFTDATE DATE
LASTCONTACTDATE DATE

Community
  • 1
  • 1
Ameer B.
  • 115
  • 1
  • 3
  • 12
  • So, the base table is `table_t1` and it has columns `birth_day`, `birth_month`, `birth`year`, `gender`, `LastGiftDate`, and `LastContactDate`? What is the data type of each column? (Please run `describe table_t1` and **don't guess**!) And at the end, you want those dates in `yyyymmdd` format, concatenated, with no space or comma or anything else between them? It would help if you would edit your post and would show what an output string would look like. –  Feb 23 '17 at 21:18
  • it should be `cast(birth_day as char(2))` or `rpad(cast(birth_day as varchar), 2, ' '). Also: why aren't you storing the birthdate as a `date`? –  Feb 23 '17 at 21:23
  • Thank you all - I used cast(birth_day as char(2)) but some fields do have null values. to_Char(LastGiftDate,'yyyymmdd') and to_Char(LastContactDate,'yyyymmdd') work fine but the output looks weird – Ameer B. Feb 23 '17 at 21:50
  • I showed how to use cast and coalesce together [for your last question](http://stackoverflow.com/a/42356141/266304). Not sure why you still think this is any different? – Alex Poole Feb 23 '17 at 23:08

1 Answers1

1
SELECT COALESCE( CAST( birth_day   AS CHAR(2) ), '  ' ) || 
       COALESCE( CAST( birth_month AS CHAR(2) ), '  ' ) ||
       COALESCE( CAST( birth_year  AS CHAR(4) ), '    ' ) || 
       COALESCE( CAST( gender      AS CHAR(2) ), '  ' ) || 
       COALESCE( TO_CHAR( LastGiftDate,    'yyyymmdd' ), '        ' ) ||
       ' ' ||
       COALESCE( TO_CHAR( LastContactDate, 'yyyymmdd' ), '        ' )
FROM   table_t1;
MT0
  • 143,790
  • 11
  • 59
  • 117