CODEWITHSUNDEEP
oracle-sqldeveloper
user19874068
user19874068

Reputation: 11

How to repeat some data points in query results?

I am trying to get the max date by account from 3 different tables and view those dates side by side. I created a separate query for each table, merged the results with UNION ALL, and then wrapped all that in a PIVOT.

The first 2 sections in the link/pic below show what I have been able to accomplish and the 3rd section is what I would like to do.

Query results by step

How can I get the results from 2 of the tables to repeat? Is that possible?

--define var_ent_type = 'ACOM' 
--define var_ent_id = '52766'
--define var_dict_id = 113

SELECT
*
FROM 
(
SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'PERF_SUMMARY' as "TableName",
    PS.DICTIONARY_ID,
    to_char(MAX(PS.END_EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN PERFORMDBO.PERF_SUMMARY PS ON (PS.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND PS.DICTIONARY_ID >= 100
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'PERF_SUMMARY',
    PS.DICTIONARY_ID

union all 

SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(H.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN HOLDINGDBO.POSITION H ON (H.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION',
    1

union all 

SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(C.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN CASHDBO.CASH_ACTIVITY C ON (C.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY',
    1

--ORDER BY
--    2,3, 4 
) 

PIVOT

(
MAX("MaxDate")
FOR "TableName"
IN ('CASH_ACTIVITY', 'PERF_SUMMARY','POSITION')
)

Upvotes: 1

Views: 58

Answers (1)

Shankar S
Shankar S

Reputation: 91

Everything is possible. You only need a window function to make the value repeat across rows w/o data.

--Assuming current query is QC
With QC as (
...
)
select code, account, grouping, 
       --cash,
       first_value(cash) over (partition by code, account order by grouping asc rows unbounded preceding) as cash_repeat,
       perf,
       --pos,
       first_value(pos) over (partition by code, account order by grouping asc rows unbounded preceding)  as pos_repeat
from QC
;

See first_value() help here: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/FIRST_VALUE.html#GUID-D454EC3F-370C-4C64-9B11-33FCB10D95EC

Upvotes: 0

Related Questions