Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 11, 2017 - 3:05 pm UTC

Last updated: July 12, 2017 - 10:27 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Team,

Could you please have a look at below scenario:
I have 3 tables:
select * from tab_login_details;
select * from tab_request;
select * from tab_access;

Basically i need output as below:
FK_TB_LOGIN_MASTER FK_TB_COMPANY_DETAILS FLAG
1022 1001 Y
1022 1002 N
1022 1003 Y

Below is the logic i need to build.
In table : tab_login_details, there can be duplicates for the combination of FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS.
Query table tab_login_details using FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS,
if any of the retrieved row is having FK_TB_CCRS_PAT_REQUEST IS NOT NULL
THEN query table tab_request using tab_request.PK_TB_CCRS_PAT_REQUEST = tab_login_details.FK_TB_CCRS_PAT_REQUEST ANS STATUS = 'AC' -- if any row match this condition then 'Y',ELSE 'N'

if ALL retrieved rows having FK_TB_CCRS_PAT_REQUEST IS NULL
THEN query table tab_access using tab_access.FK_TB_CCRS_LOGIN_DETAILS = tab_login_details.PK_TB_CCRS_LOGIN_DETAILS ANS STATUS = 'AC' -- if any row match this condition then 'Y',ELSE 'N'

Could you please help to build this query or suggest something which I can try.

--------------------------------------------------
Hi Chris,
Apology for insufficient information provided.
Please find below details:

-------------------------------------------

FK_TB_COMPANY_DETAILS = 1001 is Y for the reasons :
In tab_login_details for FK_TB_COMPANY_DETAILS = 1001 FK_TB_CCRS_PAT_REQUEST is NULL hence I need to find the entry in TAB_ACCESS for tab_login_details.FK_TB_CCRS_LOGIN_DETAILS in (33717) and rec_status = 'AC', if count > 0 it will be marked to "Y".

FK_TB_COMPANY_DETAILS = 1002 is N for the reasons :
In tab_login_details for FK_TB_COMPANY_DETAILS = 1002 FK_TB_CCRS_PAT_REQUEST is NULL also corresponding entry in TAB_ACCESS i.e. tab_login_details.FK_TB_CCRS_LOGIN_DETAILS in (33717), status is not 'AC' hence it will be marked to "N".

FK_TB_COMPANY_DETAILS = 1003 is Y for the reasons :
In tab_login_details for FK_TB_COMPANY_DETAILS = 1003 FK_TB_CCRS_PAT_REQUEST is not NULL (any of the row NOT NULL), then find entry in TAB_REQUEST, like tab_request.PK_TB_CCRS_PAT_REQUEST in (1111,2222,3333) and rec_status = 'AC', if count is > 0 then it will be mark as "Y"



with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/e9ek3s7f7tydpj5kvrgyn2mnx

and Chris said...

I'm still not sure I fully understand what you're trying to do. But the solution is similar to that for your other question.

First you need to outer join all the tables together. Then you can find whether all the FK_TB_CCRS_PAT_REQUEST columns are null for a given company and master with analytics. For example, min partitioned by these columns:

min(t.FK_TB_CCRS_PAT_REQUEST) over (
  partition by t.fk_tb_login_master, t.fk_tb_company_details
)


You can do similar with count to check the number of rec_statuses = 'AC'. But here you want to map the other statuses to null in the count. 

count(case when a.rec_status = 'AC' then 1 end) over (
  partition by t.fk_tb_login_master, t.fk_tb_company_details
)


Bung these in case expressions as appropriate and you get:

select t.fk_tb_login_master, t.fk_tb_company_details,
       min(t.FK_TB_CCRS_PAT_REQUEST) over (
         partition by t.fk_tb_login_master, t.fk_tb_company_details
       ) mn,
       count(case when a.rec_status = 'AC' then 1 end) over (
         partition by t.fk_tb_login_master, t.fk_tb_company_details
       ) ct,
       case
         when min(t.FK_TB_CCRS_PAT_REQUEST) over (
                partition by t.fk_tb_login_master, t.fk_tb_company_details
              ) is null then 
           case
             when count(case when a.rec_status = 'AC' then 1 end) over (
                    partition by t.fk_tb_login_master, t.fk_tb_company_details
                  ) > 0 then 'Y'
             else 'N'
           end 
         else 
           case
             when count(case when r.rec_status = 'AC' then 1 end) over (
                    partition by t.fk_tb_login_master, t.fk_tb_company_details
                  ) > 0 then 'Y'
             else 'N'
           end 
       end yn
from   tab_login_details t
left   join tab_access a
on     t.PK_TB_CCRS_LOGIN_DETAILS = a.FK_TB_CCRS_LOGIN_DETAILS 
left   join tab_request r
on     r.PK_TB_CCRS_PAT_REQUEST  = t.FK_TB_CCRS_PAT_REQUEST 
order  by 1 , 2;

FK_TB_LOGIN_MASTER FK_TB_COMPANY_DETAILS         MN         CT Y
------------------ --------------------- ---------- ---------- -
              1022                  1001                     1 Y
              1022                  1001                     1 Y
              1022                  1001                     1 Y
              1022                  1001                     1 Y
              1022                  1002                     0 N
              1022                  1002                     0 N
              1022                  1003       1111          0 Y
              1022                  1003       1111          0 Y
              1022                  1003       1111          0 Y
              1022                  1003       1111          0 Y
              1022                  1003       1111          0 Y


Which squashes down to:

select distinct t.fk_tb_login_master, t.fk_tb_company_details,
       case
         when min(t.FK_TB_CCRS_PAT_REQUEST) over (
                partition by t.fk_tb_login_master, t.fk_tb_company_details
              ) is null then 
           case
             when count(case when a.rec_status = 'AC' then 1 end) over (
                    partition by t.fk_tb_login_master, t.fk_tb_company_details
                  ) > 0 then 'Y'
             else 'N'
           end 
         else 
           case
             when count(case when r.rec_status = 'AC' then 1 end) over (
                    partition by t.fk_tb_login_master, t.fk_tb_company_details
                  ) > 0 then 'Y'
             else 'N'
           end 
       end yn
from   tab_login_details t
left   join tab_access a
on     t.PK_TB_CCRS_LOGIN_DETAILS = a.FK_TB_CCRS_LOGIN_DETAILS 
left   join tab_request r
on     r.PK_TB_CCRS_PAT_REQUEST  = t.FK_TB_CCRS_PAT_REQUEST 
order  by 1 , 2;

FK_TB_LOGIN_MASTER FK_TB_COMPANY_DETAILS Y
------------------ --------------------- -
              1022                  1001 Y
              1022                  1002 N
              1022                  1003 Y

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Other alternatives

Rajeshwaran, Jeyabal, July 12, 2017 - 2:38 pm UTC

Given this.
.....
FK_TB_COMPANY_DETAILS = 1001 is Y for the reasons :
In tab_login_details for FK_TB_COMPANY_DETAILS = 1001 FK_TB_CCRS_PAT_REQUEST is NULL
hence I need to find the entry in TAB_ACCESS for tab_login_details.FK_TB_CCRS_LOGIN_DETAILS in (33717)
and rec_status = 'AC', if count > 0 it will be marked to "Y".

FK_TB_COMPANY_DETAILS = 1002 is N for the reasons :
In tab_login_details for FK_TB_COMPANY_DETAILS = 1002 FK_TB_CCRS_PAT_REQUEST is NULL
also corresponding entry in TAB_ACCESS i.e. tab_login_details.FK_TB_CCRS_LOGIN_DETAILS in (33717),
status is not 'AC' hence it will be marked to "N".

FK_TB_COMPANY_DETAILS = 1003 is Y for the reasons :
In tab_login_details for FK_TB_COMPANY_DETAILS = 1003 FK_TB_CCRS_PAT_REQUEST is not NULL
(any of the row NOT NULL), then find entry in TAB_REQUEST, like tab_request.PK_TB_CCRS_PAT_REQUEST in (1111,2222,3333)
and rec_status = 'AC', if count is > 0 then it will be mark as "Y"
....

just did an outer join and Aggregation on top of it, got this.

demo@ORA12C> select t1.fk_tb_login_master, t1.fk_tb_company_details,
  2          case when count(t2.rowid) > 0 or
  3                    count(t3.rowid) > 0 then 'Y'
  4                    else 'N' end flag
  5  from tab_login_details t1 left outer join
  6        tab_access t2
  7  on ( t1.pk_tb_ccrs_login_details = t2.fk_tb_ccrs_login_details
  8        and t2.rec_status ='AC' )
  9      left outer join tab_request t3
 10  on ( t1.fk_tb_ccrs_pat_request = t3.pk_tb_ccrs_pat_request
 11      and t3.rec_status ='AC' )
 12  group by t1.fk_tb_login_master, t1.fk_tb_company_details
 13  order by t1.fk_tb_login_master, t1.fk_tb_company_details   ;

FK_TB_LOGIN_MASTER FK_TB_COMPANY_DETAILS F
------------------ --------------------- -
              1022                  1001 Y
              1022                  1002 N
              1022                  1003 Y
demo@ORA12C>

Thanks a lot Chris!!

A reader, July 14, 2017 - 6:07 am UTC


Thanks Rajeshwaran!!

A reader, July 14, 2017 - 6:08 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.