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?
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;
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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)
Last edited by jmodic; 10-29-2003 at 05:05 PM.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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?
Code:
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?
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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?
Reply With Quote
Bookmarks