Using SQL to create a flat file and Sequences

[ssmith001]

Here's my question: I need to create a flat file that looks like this. The first line ('H' record) in this example is a header record, the second ('P' record) is the detail record, followed then by another header, detail, etc.

H6567 DOM HOST 80485 80433 NN2PPDN0N
P6567 DOM000000000000000000075000000 EA 1WSA
H6568 DOM HOST 80400 80468 NN2PPDN0N
P6568 DOM000000000000000000083000000 EA 1WSA


The header's "6567" is derived using an Oracle sequence's NEXTVAL. The detail record's "6567" is derived from the CURRVAL.

I have two queries, one that creates the header, the other the detail. I tried to use a UNION to put them together but I get an error saying I can't do this with a sequence. Does anyone know how I can do this using SQL?

Thanks in Advance,

Steve

[slimdave]

Can you post the sql that you generate the data with?

[ssmith001]

For the header I use:

select 'H' -- RECORD_TYPE ('H' = header record)
||'A' -- TRANS_CODE
||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.NEXTVAL,'000009')) -- ID
||' DOM'
||' HOST' -- OTYPE
||' '||source -- SOURCE_ID
||' '||dest -- DEST_ID
||' $0.00USD' -- REVENUE and REVENUE_CURRENCY
||to_char(schedshipdate,'YYYYMMDDHHMM') -- EARLY_DEL
||to_char(schedarrivdate,'YYYYMMDDHHMM') -- LATE_DEL
||to_char(availtoshipdate,'YYYYMMDDHHMM') -- EARLY_AVAIL
||to_char(schedshipdate,'YYYYMMDDHHMM') -- LATE_AVAIL
||' ' -- GROUP_NAME
||'N' -- INBOUND
||'N' -- NOT_ROUTE_FLAG
||'2' -- CONS_FLAG
||'PPD' -- FREIGHT_TERMS
||'N' -- NOT_UPLOAD_FLAG
||'0' -- EXTERNAL_STAT
||'N' -- SHIP_MODE
from stsc.recship

And for the Detail I use:

select 'P' -- RECORD_TYPE ('P' = commodity record)
||' ' -- BLANK
||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.CURRVAL,'000009')) -- ID
||' DOM'
||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.vol),6,0) ELSE '000000' END -- CUBE
||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.wgt),9,0) ELSE '000000000' END -- WEIGHT
||lpad(a.qty,6,0) -- PIECES
||CASE WHEN b.UnitsPerPallet != 0 THEN lpad(round(a.qty/b.unitsperpallet),6,0) ELSE '000000' END -- PALLET
||CASE WHEN b.UOM != ' ' THEN ' '||b.UOM ELSE ' ' END -- PACKAGE_TYPE_ID
||' 1' -- LINE_NUMBER
||rpad(a.item,35) -- ITEM_ID
from stsc.recship a
, stsc.item b
where a.item = b.item

[ssmith001]

If anyone out there could take a look at this and let me know if there is a way to do this, I'd appreciate your help.

Thanks

[jmodic]

You don't to use two queries for that, you don't need tu use UNION to combine those two queries - you can do it in a one single select statement statement and use CHR(10) (or whatever the proper ascii sequence for new line is for your OS) to skip to a new line inside one record.

Try it like this:

select 'H' -- RECORD_TYPE ('H' = header record)
||'A' -- TRANS_CODE
||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.NEXTVAL,'000009')) -- ID
||' DOM'
||' HOST' -- OTYPE
||' '||a.source -- SOURCE_ID
||' '||a.dest -- DEST_ID
||' $0.00USD' -- REVENUE and REVENUE_CURRENCY
||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- EARLY_DEL
||to_char(a.schedarrivdate,'YYYYMMDDHHMM') -- LATE_DEL
||to_char(a.availtoshipdate,'YYYYMMDDHHMM') -- EARLY_AVAIL
||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- LATE_AVAIL
||' ' -- GROUP_NAME
||'N' -- INBOUND
||'N' -- NOT_ROUTE_FLAG
||'2' -- CONS_FLAG
||'PPD' -- FREIGHT_TERMS
||'N' -- NOT_UPLOAD_FLAG
||'0' -- EXTERNAL_STAT
||'N' -- SHIP_MODE
|| CHR(10) -- new line !!!
||'P' -- RECORD_TYPE ('P' = commodity record)
||' ' -- BLANK
||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.CURRVAL,'000009')) -- ID
||' DOM'
||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.vol),6,0) ELSE '000000' END -- CUBE
||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.wgt),9,0) ELSE '000000000' END -- WEIGHT
||lpad(a.qty,6,0) -- PIECES
||CASE WHEN b.UnitsPerPallet != 0 THEN lpad(round(a.qty/b.unitsperpallet),6,0) ELSE '000000' END -- PALLET
||CASE WHEN b.UOM != ' ' THEN ' '||b.UOM ELSE ' ' END -- PACKAGE_TYPE_ID
||' 1' -- LINE_NUMBER
||rpad(a.item,35) -- ITEM_ID
from stsc.recship a, stsc.item b
where a.item = b.item;

[ssmith001]

How does one find out what the ASCII code is for the CR? This database is running on AIX.

[jmodic]

AFAIK, the newline characters per OS are:

1. MS DOS/Widows: ASCII(10) + ASCII(13) - but obviously it works also with ASCII(10) alone
2. Mac: ASCII(13)
3. Unix (any flawor, including AIX): ASCII(10)

[ssmith001]

I tried both CHR(10) and CHR(13) and both resulted in a bunch of 'H' records with no 'P' records.

[jmodic]

But did you actualy spool the output to the OS file and open it with the text editor? Or did you simply look at the result set with some GUI tool that uses some sort of "grid" to display the result? In that case those tools usually display the whole record in a single line, marking CHR(10) with some "special" output character and continue the record on the same line.

Could you run the following in your SQL*Plus and check the result?

set heading off feedback off
spool test.txt
select 'line1' || CHR(10) || 'line2' from dual;
spool off

How many lines are there in your test.txt?

[ssmith001]

Interesting... there are 2 lines in the output file. I then spooled my query out to a file with no luck. I've attached the resulting file.

Could it be because the first part of the query that produces the 'H'eader record does not join to the ITEM table like the second part that produces the 'P' record does?

Thanks for all your help!

[ssmith001]

This just isn't working. I'm not getting any 'P' records. Help!!!

[ssmith001]

Does the CHR(10) only work when both sides of the query pull from the same table?

[jmodic]

Oh my god! I can't belive it - it's been almost a week since you've been given the CHR(10) hint and you are still struggling with it.

Can't you see that CHR(10) in the query is nothing but an ordinar constant, just like every other constant? So why would it behave differently if "both sides of the query pull from the same table" or not (whatever that actually means...)?

CHR(10) in this case is a constant is a constant is a constant. CHR(10) is a simple literal is a simple literal is a simple literal. Just like 'A' or '1' or 'z' or whatever single character literal string you choose. Nothing special about that, it has no special meaning to the query (it has special meaning to the operating system when interpreting the results returning from a query, though: on most systems OS will interpret it like "at this point we must break this line and skip to another one").

here is a simple example that returns all the employee's names and the department in which each of them work, for each employee printing his name and department into two sepparate lines:

select e.ename || chr(10) || d.dname
from scott.emp e, scott.dept d
where e.deptno=e.deptno;

I hope you can start your further exploration starting from this simple example.

[ssmith001]

I appreciate your comments and help. The only reason I'm pursuing this any further is because you had initially stated that I did not need to do a union, but using the solution you proposed, in this case, is not working.