Produce a fixed-width flat format output file from a SQL query

Question

I need help producing fixed width file where every row is the same length. I know I have to cast each field as a fixed length character field. The size of the field should be the maximum size of the field in the source table.However, just could not produce the file using below query -

    Select cast(x.account_id as char(10)) ||
           cast(birth_month as char(2)) ||
           cast(birth_year as char(4))|| 
           TO_CHAR(LastVisittDate,'yyyymmdd') ||'    '||
           max(case when email_Rank = 1 then cast(email_address as char(100)) else null end) ||chr(13) as ConstitRow

 from INTERS XR
               inner join INTERS_REL RX on XR.account_id = RX.account_id and RX.sts <> 'D'

       where 
               RX.account_id in (deleted - long list of account IDs)

Answer 1

You are casting your column values to char(n), which will pad shorter strings and numbers (implicitly converted to strings) to n chars, and truncate longer values. (This is better than using varchar2(n), which would error with longer numbers and wouldn't make any difference to shorter strings).

You will have a problem with nulls though, as cast(null as char(n)) - or anything else - is still null, rather than n spaces as you might expect. That may be a problem for any of your columns, but particularly for your case expressions.

If any columns can be null you can use nvl or coalesce to treat them as a single space instead, and the cast will then pad those too:

cast(coalesce(First_name, ' ') as char(20))

Rather than casting, you could also use rpad():

rpad(coalesce(First_name, ' '), 20, ' ')

For the case expressions you can make the else clause evaluate to a single space instead of null, but you also need to apply the cast to the overall case expression, not have it within one when branch; so instead of this:

max(case when email_Rank = 1 then cast(email_address as char(100)) else null end)

you would do:

cast(max(case when email_Rank = 1 then email_address else ' ' end) as char(100))

or if you prefer:

cast(coalesce(max(case when email_Rank = 1 then email_address end), ' ') as char(100))

---

Your client may have been right-padding the overall string to the same length anyway (SQL*Plus will do that if you have set trimout off, or if spooling set trimspool off; which might be what BobC was referring to), but that doesn't really help if what you're really trying to create is fixed length fields, which cumulatively would give you a fixed length record as well - and if you didn't have fixed length fields it would impossible to interpret the data anyway.

Answer 2

I had the similar problem before. There's a package named as_xlsx by Anton Scheffer, and it addressed my problem. Check out question about this: Create an Excel File (.xlsx) using PL/SQL. Hope this helps you out.