|
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|