Using SQL to create a flat file and Sequences
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Using SQL to create a flat file and Sequences

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    Using SQL to create a flat file and Sequences

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you post the sql that you generate the data with?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2003
    Posts
    100
    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

  4. #4
    Join Date
    Aug 2003
    Posts
    100
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Aug 2003
    Posts
    100
    How does one find out what the ASCII code is for the CR? This database is running on AIX.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)
    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?

  8. #8
    Join Date
    Aug 2003
    Posts
    100
    I tried both CHR(10) and CHR(13) and both resulted in a bunch of 'H' records with no 'P' records.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  10. #10
    Join Date
    Aug 2003
    Posts
    100
    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!
    Last edited by ssmith001; 11-03-2003 at 12:08 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width