Fixed length Flat file 1

[pnad]

I need to output the results of a query to a fixed length flat file. My query is :

spool test.txt
select rpad(b.col1, 3)||
rpad(a.col2,5)||
rpad(substr(to_char(b.effective_date,'yyyy/mm/dd'),1,4),4)||
rpad(substr(to_char(b.effective_date,'yyyy/mm/dd'),6,2),2)||
rpad(substr(to_char(b.effective_date,'yyyy/mm/dd'),9,2),2)||
from table1 a, table2 b;
spool off

But if any of the columns is empty or null, the formatting goes awry. Is there any way, I can format the query to output to a fixed length flat file.

Thanks.

 

[SantaMufasa]

PNad,

First, you have a syntax error in your code: your final concatenation operator ("||") has no expression to concatenate. Next, since you are joining two tables, you need one or more WHERE clauses to avoid a Cartesian Product as a result.

Also, you can accomplish your date extraction with one TO_CHAR expression and you can force a NULL to expand to the full, fixed-length width with my code adjustments, below:

spool test.txt
select rpad(b.col1||' ', 3)||
rpad(a.col2||' ',5)||
rpad(to_char(b.effective_date,'yyyymmdd')||' ',8)
from table1 a, table2 b
where <join condition>;
spool off

Let us know if this meets with your satisfaction.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pnad]

Hi Mufasa,

Thanks for your quick response.

I chnaged the query as per your suggestions and the entire query is below:

spool test.txt
select /*+ index(a BSTRN_DETAIL_KEY03) */ rpad(b.source_code||' ', 3)||
rpad(a.company_code||' ',5)||
rpad(to_char(b.effective_date,'yyyymmdd')||' ',8)||
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='POLCY')||' ',10)||
rpad(a.converted_amount||' ',12)||
rpad(a.debit_credit_ind||' ',1)||
rpad(a.account_number||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='LOB')||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='PROD')||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='TAXST')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='OPC')||' ',3)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='SAD')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='REICH')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='REICO')||' ',3)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='BCTR')||' ',6)||
rpad( a.tran_dtl_desc||' ',8)||
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='TRX')||' ',4)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='STATE')||' ',2)||
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='MEMO')||' ',6)
from bstrn_detail a, bstrn_header b
where a.cal_acctg_period = '2006-07-31'
and a.tran_id=b.tran_id
and b.source_code = 'ALS'
and a.company_code='INTGA'
order by b.effective_date;
spool off

And I have pasted the sample output here:

ALSINTGA20060720210002875233.15 D21400000SURPLUS 20446NJ_21000WD NJPWDE
ALSINTGA2006072021000272652906.74 D21400000SURPLUS 20445FL_21000WD FLFREE
ALSINTGA20060720210001414193.78 D51460000FPVA PINN NQUALIND20407NONRENONCT_21000XF CT
Is there anyway to prepad the converted amount field with 0000's. And as you would have noticed, it still doesnt format it in the fixed length format.

 

[SantaMufasa]

Yes, you can do an "lpad" function (just as you used the "rpad" function):

select lpad(1.23,8,0) from dual;

LPAD(1.2
--------
00001.23

If you need additional guidance, let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pnad]

The o/p looks good to me - thank you very much for all your help. I have sent the sample file to the users so am waiting for their response. fingers crossed.

 

[pnad]

select /*+ index(a BSTRN_DETAIL_KEY03) */ rpad(b.source_code||' ', 3)||
rpad(a.company_code||' ',5)||
rpad(to_char(b.effective_date,'yyyymmdd')||' ',8)||
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='POLCY')||' ',10) "polcy"||
rpad(a.converted_amount||' ',12)||
rpad(a.debit_credit_ind||' ',1)||
rpad(a.account_number||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='LOB')||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='PROD')||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='TAXST')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='OPC')||' ',3)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='SAD')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='REICH')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='REICO')||' ',3)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='BCTR')||' ',6)||
rpad( a.tran_dtl_desc||' ',8)||
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='TRX')||' ',4)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='STATE')||' ',2)||
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='MEMO')||' ',6)
from bstrn_detail a, bstrn_header b
where a.cal_acctg_period = '2006-07-31'
and a.tran_id=b.tran_id
and b.source_code = 'ALS'
and a.company_code='INTGA'
order by "polcy", a.account_number;

I changed the query to sort by polcy and account number but it gives me an ora-00904 error saying that FROM is not where it is expected. Please help.

 

[SantaMufasa]

PNAD,

Your output from your outer, main SELECT is a single column of concatenated output. Therefore, the only legal place for a column alias is just prior to your FROM (i.e., following the definition of your single column of output).

Instead, you have a column alias, "polcy", illegally appearing in your fourth expression of concatenation, thus cause Oracle to throw the ORA-00904 error saying that the "FROM" is not where Oracle hoped it would be.

The implication is, however, that you will need to re-word your ORDER BY expression, as well, since you will be removing the column alias "polcy".

Let us know of your outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pnad]

Thank you Mufasa for your quick response.

Since the output is a single column of concatenated output, how do I order by "location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='POLCY'" (this would have been aliased as "polcy" if we werent using the concatenation to produce a fixed length flat file) and then account number. I guess, thats what I was trying to do by giving an alias but that doesnt work.

Basically, I want to be able to order by a column that is retrieved using a subquery and as you explained that cannot be done using an alias. Is there any other way?

I was able to order by effective date, though.

 

[SantaMufasa]

PNAD,

Try this:

select /*+ index(a BSTRN_DETAIL_KEY03) */ rpad(b.source_code||' ', 3)||
rpad(a.company_code||' ',5)||
rpad(to_char(b.effective_date,'yyyymmdd')||' ',8)||[b]
c.polcy||[/b]
rpad(a.converted_amount||' ',12)||
rpad(a.debit_credit_ind||' ',1)|| 
rpad(a.account_number||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='LOB')||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='PROD')||' ',8)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='TAXST')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='OPC')||' ',3)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='SAD')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='REICH')||' ',5)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='REICO')||' ',3)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='BCTR')||' ',6)||
rpad( a.tran_dtl_desc||' ',8)||
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='TRX')||' ',4)||
rpad((select bu_code from Bu_Set_Details d where bu_Set_id=a.bu_set_id and d.bu_type='STATE')||' ',2)|| 
rpad((select location_code from BSTRN_DTL_LOC c where a.tran_detail_id=c.tran_detail_id and location_type_code='MEMO')||' ',6)
from bstrn_detail a
    ,bstrn_header b[b]
    ,(select tran_detail_id,rpad(location_code||' ',10)polcy
        from BSTRN_DTL_LOC
       where location_type_code='POLCY') c[/b]
where a.cal_acctg_period = '2006-07-31'
and a.tran_id=b.tran_id
and b.source_code = 'ALS'
and a.company_code='INTGA'[b]
and a.tran_detail_id=c.tran_detail_id[/b]
order by [b]c.polcy[/b], a.account_number;


 [santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via [b]www.dasages.com[/b]]

 

[LKBrwnDBA]

Instead of using all those RPAD() functions, just use the SQL*Plus 'COLUMN' options:

SET LIN 123 PAGES 0 COLSEP ''
COL source_code     FOR A3
COL company_code    FOR A5
COL polcy           FOR A10
--- Etc ...
select /*+ index(a BSTRN_DETAIL_KEY03) */ 
b.source_code,
a.company_code,
to_char(b.effective_date,'yyyymmdd') effective_date,
(select location_code from BSTRN_DTL_LOC c 
where a.tran_detail_id=c.tran_detail_id 
and location_type_code='POLCY') "polcy"
a.converted_amount,
---Etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[pnad]

Thank you