CODEWITHSUNDEEP
oracle-database
smned
smned

Reputation: 683

Oracle for every result inside case query

I have a query which aggregates multiple tables. Table1, Table2, Table3 and Table4. The query runs as 

SELECT TABLE1.COLUMN1, 
   RESULT.COLUMN1,
   RESULT.COLUMN2, 
   RESULT.COLUMN3,
   RESULT.SOMEAGGCOLUMN, 
   FROM (
            SELECT DISTINCT COLUMN1,COLUMN2,COLUMN3,
                CASE WHEN(SELECT COLUMN1 FROM TABLE2 
                            WHERE TABLE2.ID = TABLE3.TABLE2ID 
                            AND TABLE2.COLUMN2  = 'CRITERIA')
                        THEN (SELECT COLUMN2 FROM TABLE2)
                     ELSE 'DEFAULT VALUE'
                END AS SOMEAGGCOLUMN
            FROM TABLE2, TABLE3
             WHERE TABLE2.ID = TABLE3.TABLE2ID
             GROUPBY
             COLUMN1,
             COLUMN2,
             COLUMN3,
             SOMEAGGCOLUMN
        ) RESULT, TABLE1 WHERE

    RESULT.COLUMN1='CRITERIA'
    AND RESULT.COLUMN2 = 'CRITERIA'
    AND TABLE1.COLUMN1 = 'CRITERIA'
    GROUP BY
    TABLE1.COLUMN1, 
    RESULT.COLUMN1,
    RESULT.COLUMN2, 
    RESULT.COLUMN3,
    RESULT.SOMEAGGCOLUMN

I wanted to aggregate and resturn data in combined form. adding AND rownum =1 inside the case select statements returns correct values, but i want to return distinct multiple values to be returned as multiple rows to be combined with the outer values. eg. if the inside query returns 2 TABLE2.COLUMN1 VALUES i want to have records stating.

       TABLE1.COLUMN1, 
       RESULT.COLUMN1,
       RESULT.COLUMN2, 
       RESULT.COLUMN3,
       RESULT.SOMEAGGCOLUMN = value one 

       and 

       TABLE1.COLUMN1, 
       RESULT.COLUMN1,
       RESULT.COLUMN2, 
       RESULT.COLUMN3,
       RESULT.SOMEAGGCOLUMN = value two.

Can i achieve that using the query?

  TABLE1
_____________________________
  ID COLUMN1 COLUMN2 COLUMN3
-----------------------------
  1   ABC     T6AD    OTHERM
  2   CDE     T7AD    ANOTHER
----------------------------

  TABLE2 
______________________________
  ID  COLUMN1 COLUMN2  COLUMN3
------------------------------
  1    ASA     T6AD    OTHERM
  2    AFS     T6AD    OTHERM
  3    AED     T7AD    ANOTHER
------------------------------

  TABLE3 
 ________________________________
  ID  TABLE2ID  COLUMN1 COLUMN2
 --------------------------------
  1      1       DETAIL  DETAIL2
  2      2       DETAIL3 DETAIL4
 -------------------------------

What i am trying to achieve is get table one results with the condition then fetch records from table 2 and three using non key columns of table 1. Sample result.

For each T6AD FROM TABLE1 i will get

 ABC T6AD OTHERM ASA DETAIL  DETAIL2
 ABC T6AD OTHERM AFS DETAIL3 DETAIL4

I know it's a bit confusing, but imagine the three tables have values linking them together each being a subset further detailing structured data storing additional details. I wanted to traverse to the last table to retrieve details for one record in the Major table. And single key across is not available, rather traversing down.

Upvotes: 0

Views: 37

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You appear to just want:

select t1.column1, t1.column2, t1.column3, t2.column1, t3.column1, t3.column2
from table1 t1
left join table2 t2 on t2.column2 = t1.column2 and t2.column3 = t1.column3
left join table3 t3 on t3.table2id = t2.id
where t1.column2 = 'T6AD';

With your sample data as CTEs:

with table1(id, column1, column2, column3) as (
  select 1, 'ABC', 'T6AD', 'OTHERM' from dual
  union all select 2, 'CDE', 'T7AD', 'ANOTHER' from dual
),
table2 (id, column1, column2, column3) as (
  select 1, 'ASA', 'T6AD', 'OTHERM' from dual
  union all select 2, 'AFS', 'T6AD', 'OTHERM' from dual
  union all select 3, 'AED', 'T7AD', 'ANOTHER' from dual
),
table3 (id, table2id, column1, column2) as (
  select 1, 1, 'DETAIL', 'DETAIL2' from dual
  union all select 2, 2, 'DETAIL3', 'DETAIL4' from dual
)
select t1.column1, t1.column2, t1.column3, t2.column1, t3.column1, t3.column2
from table1 t1
left join table2 t2 on t2.column2 = t1.column2 and t2.column3 = t1.column3
left join table3 t3 on t3.table2id = t2.id
where t1.column2 = 'T6AD';

COL COLU COLUMN3 COL COLUMN1 COLUMN2
--- ---- ------- --- ------- -------
ABC T6AD OTHERM  ASA DETAIL  DETAIL2
ABC T6AD OTHERM  AFS DETAIL3 DETAIL4

If the criteria was T7AD instead you'd get:

COL COLU COLUMN3 COL COLUMN1 COLUMN2
--- ---- ------- --- ------- -------
CDE T7AD ANOTHER AED

Still not sure what aggregation you're referring to though...

Upvotes: 2

Related Questions