I have tables set up like this:
INSERT INTO CATEGORY VALUES (1000, 'Type1', 'A', '01-Jan-21', 27.00);
INSERT INTO CATEGORY VALUES (1010, 'Type2', 'A', '01-Jan-21', 30.00);
INSERT INTO CATEGORY VALUES (1020, 'Type3', 'A', '07-Jan-21', 0.00);
INSERT INTO SUBCATEGORY VALUES (1010, 1010, 'ABC', '01-Jan-21', 2000.00);
INSERT INTO SUBCATEGORY VALUES (1010, 1020, 'ABC', '07-Jan-21', 0.00);
INSERT INTO SUBCATEGORY VALUES (1020, 1010, 'XYZ', '01-Jan-21', 1000.00);
And I want them to display like:
CATCODE NAME C CDATE DECIMAL
---------- ----- - --------- ----------
1000 Type1 A 01-JAN-21 2527
1010 Type2 A 01-JAN-21 3000
CATCODE SUBCODE NAME SDATE DECIMAL
---------- ---------- ---- --------- ----------
1010 1010 ABC 01-JAN-21 2000
1010 1020 ABC 07-JAN-21 0
CATCODE NAME C CDATE DECIMAL
---------- ----- - --------- ----------
1020 Type3 A 07-JAN-21 0
CATCODE SUBCODE NAME SDATE DECIMAL
---------- ---------- ---- --------- ----------
1020 1010 XYZ 01-JAN-21 1000
So basically it posts all the columns for the category table, and if that category has a subcategory (related by CATCODE), it will post the columns for the subcategory table underneath.
I've experimented around with different combinations of JOINs and UNION trying to get the result outlined above, but quite frankly I don't know that I'm approaching this correctly. UNION I don't think can't work because of the mismatched data types.
CodePudding user response：
This could be done using Oracle SQL MODEL clause.
With your sample data:
WITH
category AS
(
Select 1000 "CAT_CODE", 'Type1' "CAT_NAME", 'A' "C", To_Date('01-JAN-21', 'dd-MON-yy') "C_DATE", 27.00 "DECIM" From Dual Union All
Select 1010, 'Type2', 'A', To_Date('01-JAN-21', 'dd-MON-yy'), 30.00 From Dual Union All
Select 1020, 'Type3', 'A', To_Date('07-JAN-21', 'dd-MON-yy'), 0.00 From Dual
),
sub_category AS
(
Select 1010 "CAT_CODE", 1010 "SUB_CODE", 'ABC' "SUB_NAME", To_Date('01-JAN-21', 'dd-MON-yy') "S_DATE", 2000.00 "DECIM" From Dual Union All
Select 1010, 1020, 'ABC', To_Date('07-JAN-21', 'dd-MON-yy'), 0.00 From Dual Union All
Select 1020, 1010, 'XYZ', To_Date('01-JAN-21', 'dd-MON-yy'), 1000.00 From Dual
),
... create CTE (I named it out_model) using MODEL clause to define the rows of your report with all the values that we are going to use for main sql and final report ...
out_model AS
(
SELECT ROW_STR, SUB_ROW_STR, ID, CASE WHEN SUB_ROW_STR Is Null THEN IDX ELSE IDX END "IDX", COUNT_IDX,
CAT_CODE, CAT_NAME, C, C_DATE, C_DECIM, SUB_CODE, SUB_NAME, S_DATE, S_DECIM
FROM (
Select CAST(Null As VarChar2(255)) "ROW_STR", CAST('-' As VarChar2(255)) "SUB_ROW_STR", CAST(0 As Number(6)) "ID", CAST(0 As Number(6)) "S_ID",
ROW_NUMBER() OVER(PARTITION BY c.CAT_CODE ORDER BY c.CAT_CODE) "IDX", Count(s.CAT_CODE) OVER(PARTITION BY c.CAT_CODE) "COUNT_IDX",
To_Char(c.CAT_CODE) "CAT_CODE", c.CAT_NAME, c.C, To_Char(c.C_DATE, 'dd.mm.yyyy') "C_DATE", To_Char(c.DECIM, '999G990D00') "C_DECIM",
To_Char(s.SUB_CODE) "SUB_CODE", s.SUB_NAME, To_Char(s.S_DATE, 'dd.mm.yyyy') "S_DATE", To_Char(s.DECIM, '999G990D00') "S_DECIM"
From category c
Left Join sub_category s ON(s.CAT_CODE = c.CAT_CODE)
)
MODEL Partition By(CAT_CODE)
Dimension By(IDX, COUNT_IDX)
Measures (ID, S_ID, ROW_STR, SUB_ROW_STR, CAT_NAME, C, C_DATE, C_DECIM, SUB_CODE, SUB_NAME, S_DATE, S_DECIM)
RULES ( ROW_STR[1, ANY] = CASE WHEN ID[CV(), CV()] = 0 THEN LPAD(CV(CAT_CODE), 10, ' ') || ' ' || RPAD(CAT_NAME[CV(), CV()], 10, ' ') || ' ' || C[CV(), CV()] || ' ' || RPAD(C_DATE[CV(), CV()], 12, ' ') || ' ' || LPAD(C_DECIM[CV(), CV()], 12, ' ') END,
ROW_STR[1, -5] = CASE WHEN CV(IDX) = 1 THEN ' CAT_CODE' || ' ' || 'CAT_NAME ' || ' ' || 'C' || ' ' || 'CAT_DATE ' || ' ' || ' CAT_DECIM' END,
--
ROW_STR[1, -4] = CASE WHEN CV(IDX) = 1 THEN '----------' || ' ' || '----------' || ' ' || '-' || ' ' || '------------' || ' ' || '------------' END,
ROW_STR[500, 500] = CASE WHEN CV(IDX) = CV(COUNT_IDX) THEN ' ' || ' ' || 'SUB_CODE ' || ' ' || 'SUB_NAME ' || 'SUB_DATE ' || ' ' || ' SUB_DECIM' END,
ROW_STR[600, 600] = CASE WHEN CV(IDX) = CV(COUNT_IDX) THEN ' ' || ' ' || '----------' || ' ' || '------------' || ' ' || '------------' || ' ' || '------------' END,
SUB_ROW_STR[ANY, ANY] = CASE WHEN SUB_CODE[CV(), CV()] Is Not Null And ID[CV(), CV()] = 0 THEN ' ' || ' ' || LPAD(SUB_CODE[CV(), CV()], 10, ' ') || ' ' || RPAD(SUB_NAME[CV(), CV()], 10, ' ') || RPAD(S_DATE[CV(), CV()], 12, ' ') || ' ' || LPAD(S_DECIM[CV(), CV()], 12, ' ') END
)
ORDER BY CAT_CODE, COUNT_IDX, IDX
)
Main SQL with the resulting report...
SELECT CASE WHEN ROWNUM > 1 And SUBSTR(p.ROW_STR, 1, 10) = ' CAT_CODE' THEN Chr(10) || p.ROW_STR ELSE p.ROW_STR END "REPORT"
FROM ( SELECT ROW_STR "ROW_STR", CAT_CODE,
SUB_CODE, IDX "IDX", COUNT_IDX
FROM out_model
UNION ALL
SELECT SUB_ROW_STR, CAT_CODE, SUB_CODE,IDX 10000, COUNT_IDX
FROM out_model
) p
WHERE ROW_STR Is Not Null And (COUNT_IDX < 500 OR (COUNT_IDX >= 500 And Nvl((SELECT COUNT(*) FROM sub_category WHERE CAT_CODE = p.CAT_CODE), 0) > 0))
ORDER BY
CAT_CODE, IDX, COUNT_IDX
/* R e s u l t :
REPORT
--------------------------------------------------------------
CAT_CODE CAT_NAME C CAT_DATE CAT_DECIM
---------- ---------- - ------------ ------------
1000 Type1 A 01.01.2021 27.00
CAT_CODE CAT_NAME C CAT_DATE CAT_DECIM
---------- ---------- - ------------ ------------
1010 Type2 A 01.01.2021 30.00
SUB_CODE SUB_NAME SUB_DATE SUB_DECIM
---------- ------------ ------------ ------------
1010 ABC 01.01.2021 2,000.00
1020 ABC 07.01.2021 0.00
CAT_CODE CAT_NAME C CAT_DATE CAT_DECIM
---------- ---------- - ------------ ------------
1020 Type3 A 07.01.2021 0.00
SUB_CODE SUB_NAME SUB_DATE SUB_DECIM
---------- ------------ ------------ ------------
1010 XYZ 01.01.2021 1,000.00
*/