Oracle SQL Header and detail

Question

I am trying to create a header and detail style output which can output a header line, then detail lines, then a header line, then detail lines etc.

It will use two tables:

HeaderTable:

MAIN_NO MAIN_DESCRIPTION MAIN_CUST MAIN_NAME
1       Apple            Student   John
2       Pear             Child     Peter
3       Orange           Alien     Matt

DetailTable:

MAIN_NO PERF EVENT INFO   PREV_VALUE NEW_VALUE REV STATE MODIFIED   NOTE
1       ABC  YES   RED    12         30        0   Con   01/02/2018 FALSE
1       DEF  YES   BLUE   18         34        0   Con   01/04/2018 FALSE
2       GHI  NO    BLUE   14         38        0   UUU   01/07/2018 TRUE
2       JKL  NO    RED    11         39        0   Con   01/03/2018 FALSE
3       MNO  NO    GREEN  19         32        0   UUU   01/01/2018 TRUE
3       PQR  YES   WHITE  10         33        0   UUU   01/08/2018 TRUE

Desired Output:

MAIN_NO MAIN_DESCRIPTION MAIN_CUST MAIN_NAME
1       Apple            Student   John
1       ABC  YES   RED    12         30        0   Con   01/02/2018 FALSE
1       DEF  YES   BLUE   18         34        0   Con   01/04/2018 FALSE
2       Pear             Child     Peter
2       GHI  NO    BLUE   14         38        0   UUU   01/07/2018 TRUE
2       JKL  NO    RED    11         39        0   Con   01/03/2018 FALSE
3       Orange           Alien     Matt
3       MNO  NO    GREEN  19         32        0   UUU   01/01/2018 TRUE
3       PQR  YES   WHITE  10         33        0   UUU   01/08/2018 TRUE

SQL Fiddle: http://sqlfiddle.com/#!4/bacd0c

Current Code:

SELECT MAIN_NO, MAIN_DESCRIPTION, MAIN_CUST, MAIN_NAME
FROM 
(
    SELECT MAIN_NO, MAIN_DESCRIPTION, MAIN_CUST, MAIN_NAME, null, null, null, null, null, MAIN_NO rnk 
    FROM HeaderTable

    UNION ALL

    SELECT MAIN_NO, PERF, EVENT, INFO, PREV_VALUE, NEW_VALUE, REV, STATE, MODIFIED,   NOTE, rnk 
    FROM 
    (
        SELECT hd.*, hd.MAIN_NO || '.' || ROW_NUMBER() over (PARTITION BY hd.MAIN_NO ORDER BY hd.MAIN_NO) rnk
        FROM IFSAPP.HeaderTable h 
        INNER JOIN DetailTable hd on h.MAIN_NO= hd.MAIN_NO
    ) K     
)p 
ORDER BY rnk

However I am getting the error:

ORA-00909: invalid number of arguments

Although I'm not even sure this will give me the output I want.

Alex Poole · Accepted Answer

The original ORA-00909 error is coming from concat(), which only takes two arguments; you have three:

concat(hd.MAIN_NO, '.', ROW_NUMBER() over (PARTITION BY hd.MAIN_NO ORDER BY hd.MAIN_NO))

You need nested calls:

concat(concat(hd.MAIN_NO, '.'), ROW_NUMBER() over (PARTITION BY hd.MAIN_NO ORDER BY hd.MAIN_NO)))

or use the || operator:

hd.MAIN_NO || '.' || ROW_NUMBER() over (PARTITION BY hd.MAIN_NO ORDER BY hd.MAIN_NO)

As noted in comments though, you have other issues; fixing the concat problem lets the "ORA-01789: query block has incorrect number of result columns" error come though instead. (As in your modified question.) You can fix that with nulls, also also suggested in comments; but your rnk columns are also different types - the first branch uses the number form the table, the second forms a string - which will get "ORA-01790: expression must have same datatype as corresponding expression".

I think you want to just treat the header as rank 0 within the main_no, which avoids the concatenation altogether:

SELECT MAIN_NO, MAIN_DESCRIPTION, MAIN_CUST, MAIN_NAME,
  PREV_VALUE, NEW_VALUE, REV, STATE, MODIFIED, NOTE
FROM (
    SELECT MAIN_NO, MAIN_DESCRIPTION, MAIN_CUST, MAIN_NAME,
        null as prev_value, null as new_value, null as rev, null as state,
        null as modified, null as note, 0 rnk
    FROM HeaderTable
    UNION ALL
    SELECT hd.MAIN_NO, hd.PERF, hd.EVENT, hd.INFO,
        hd.PREV_VALUE, hd.NEW_VALUE, hd.REV, hd.STATE, hd.MODIFIED, hd.NOTE,
        ROW_NUMBER() over (PARTITION BY hd.MAIN_NO ORDER BY hd.MAIN_NO) as rnk
    FROM HeaderTable h 
    INNER JOIN DetailTable hd on h.MAIN_NO= hd.MAIN_NO
)
ORDER BY main_no, rnk
/

   MAIN_NO MAIN_D MAIN_CU MAIN_ PREV_VALUE  NEW_VALUE        REV STA MODIFIED   NOTE
---------- ------ ------- ----- ---------- ---------- ---------- --- ---------- -----
         1 Apple  Student John
         1 ABC    YES     RED           12         30          0 Con 01/02/2018 FALSE
         1 DEF    YES     BLUE          18         34          0 Con 01/04/2018 FALSE
         2 Pear   Child   Peter
         2 GHI    NO      BLUE          14         38          0 UUU 01/07/2018 TRUE
         2 JKL    NO      RED           11         39          0 Con 01/03/2018 FALSE
         3 Orange Alien   Matt
         3 MNO    NO      GREEN         19         32          0 UUU 01/01/2018 TRUE
         3 PQR    YES     WHITE         10         33          0 UUU 01/08/2018 TRUE

I've removed some unnecessary subqueries too; you need one to stop rnk being included in the final result set.

Belatedly spotted you'd add a SQL Fiddle for the data; here's the result for that.

You can explicitly cast the nulls to the expected data type but it shouldn't necessary; just for fun I've done that in this db<>fiddle demo, with your sample data in a CTE.

Oracle SQL Header and detail

Answers (2)

Related Questions