Link to home
Start Free TrialLog in
Avatar of Rao_S
Rao_S

asked on

Can you please help me in writing a correct sub query?

i have 2 tables..

create table t1
(id  number,
 mdate  date,
 inst   varchar2(10),
 db     varchar2(10),
 day    varchar2(3),
 cnt    number,
 type   varchar2(3),
 en     varchar2(2)
 );
insert into t1 values (10,to_date('06/02/2013','mm/dd/yyyy'),'QB01','KLL','Sun',1,'Leg','P');
insert into t1 values (12,to_date('06/02/2013','mm/dd/yyyy'),'TD336','ASD','Sun',1,'SQL','Q');
insert into t1 values (14,to_date('06/02/2013','mm/dd/yyyy'),'TD300','TPL','Sun',5,'Log','P');
insert into t1 values (16,to_date('06/02/2013','mm/dd/yyyy'),'TD300','Master','Sun',1,'SQL','P');
insert into t1 values (20,to_date('06/03/2013','mm/dd/yyyy'),'QB01','KLL','Mon',1,'Leg','P');
insert into t1 values (30,to_date('06/04/2013','mm/dd/yyyy'),'QB01','KLL','Tue',1,'Leg','P');
insert into t1 values (33,to_date('06/07/2013','mm/dd/yyyy'),'TD300','Master','Tue',1,'SQL','P');
insert into t1 values (40,to_date('06/07/2013','mm/dd/yyyy'),'QB01','KLL','Fri',1,'Leg','P');
insert into t1 values (43,to_date('06/07/2013','mm/dd/yyyy'),'TD300','TPL','Fri',23,'Log','P');
insert into t1 values (46,to_date('06/07/2013','mm/dd/yyyy'),'TD500','BCR','Fri',1,'Leg','T');
insert into t1 values (47,to_date('06/07/2013','mm/dd/yyyy'),'TD300','Master','Fri',1,'SQL','P');
insert into t1 values (50,to_date('06/08/2013','mm/dd/yyyy'),'QB01','KLL','Sat',1,'Leg','P');
insert into t1 values (53,to_date('06/08/2013','mm/dd/yyyy'),'QB01','CTX08','Sat',1,'Leg','P');
insert into t1 values (56,to_date('06/08/2013','mm/dd/yyyy'),'QB01','UTIL','Sat',1,'Leg','P');


create table t2
(
 inst   varchar2(10),
 global varchar2(9),
 db     varchar2(10),
 first  varchar2(10),
 last   varchar2(10),
 old    varchar2(1),
 org   varchar2(10),
 en     varchar2(2)
 );
 
 
insert into t2 values ('QB01','N/A','KLL','Bill','Smith','N','ABC','P');
insert into t2 values ('QB01','N/A','KLL','Mike','Sy','Y','ABC','PR');
insert into t2 values ('QB01','N/A','KLL','Mike','Sy','Y','ABC','PF');
insert into t2 values ('QB01','N/A','KLL','Bill','Smith','N','ABC','PR');
insert into t2 values ('QB01','N/A','CTX08','John','Fed','N','ABC-XYZ','P');
insert into t2 values ('QB01','N/A','Model','Jo','Snyder','N','ABC-XYZ','P');
insert into t2 values ('QB01','N/A','KLL','Tom','Vador','N','ABC-XYZ','P');
insert into t2 values ('TD300','N/A','TPL','Ken','Herr','N','ABC','P');
insert into t2 values ('TD336','N/A','ASD','Luo','Drew','N','ABC-XYZ','P');
insert into t2 values ('TD500','N/A','BCR','Jack','Bean','N','ABC','P');

when i run this query, i get...
select  inst,
        db,
        type,
        primarydba,
        count(1) as times
from ( select * from ( select distinct b.inst, b.db, b.type,
                                (select max(d1.first || ', ' || d1.last)
                                 from   t2 d1
                                 where  d1.inst = b.inst
                                ) primarydba,
                                b.day
                        FROM    t1 b
                        where   trunc(b.mdate) > trunc(sysdate)-8
                        and     b.inst in (    select distinct a.inst
                                               from    t1 a,
                                                       t2 d2
                                               where   d2.inst = a.inst
                                               and     d2.en = 'P'
                                               and     d2.old = 'N'
                                               and     d2.org in ('ABC', 'ABC-XYZ')
                                            )
                                    group by b.inst, b.db, b.type, b.day
                          ) TBL)
GROUP BY inst, db, type, primarydba
ORDER BY times desc, inst, db, type;

INST      DB      TYPE      PRIMARYDBA      TIMESFAILED
QB01      KLL      Leg      Tom, Vador      5
TD300      Master      SQL      Ken, Herr                      3
TD300      TPL      Log      Ken, Herr                      2
QB01      CTX08      Leg      Tom, Vador      1
QB01      UTIL      Leg      Tom, Vador      1
TD336      ASD      SQL      Luo, Drew      1
TD500      BCR      Leg      Jack, Bean                      1

i need the result to look like this......

INST      DB      TYPE      PRIMARYDBA      TIMES
QB01      KLL      Leg      Bill, Smith      5
TD300      Master      SQL      Unknown                      3
TD300      TPL      Log      Ken, Herr                      2
QB01      CTX08      Leg      John, Fed                      1
QB01      UTIL      Leg      Unknown                      1
TD336      ASD      SQL      Luo, Drew      1
TD500      BCR      Leg      Jack, Bean                      1

Can you please help?
Avatar of Sean Stuber
Sean Stuber
I don't get your initial results.  I get 


QB01	KLL	Leg	Tom, Vador	4
TD300	Master	SQL	Ken, Herr	2
QB01	CTX08	Leg	Tom, Vador	1
QB01	UTIL	Leg	Tom, Vador	1
TD300	TPL	Log	Ken, Herr	1
TD500	BCR	Leg	Jack, Bean	1

Open in new window



Are your expected results based on your initial results?  If so, how do you get the counts you reported?
Avatar of Rao_S
Rao_S

ASKER

this is the data in table 1 that i have..
10      6/3/2013      QB01      KLL      Sun      1      Leg      P
12      6/3/2013      TD336      ASD      Sun      1      SQL      Q
14      6/3/2013      TD300      TPL      Sun      5      Log      P
16      6/3/2013      TD300      Master      Sun      1      SQL      P
20      6/4/2013      QB01      KLL      Mon      1      Leg      P
30      6/5/2013      QB01      KLL      Tue      1      Leg      P
33      6/8/2013      TD300      Master      Tue      1      SQL      P
40      6/8/2013      QB01      KLL      Fri      1      Leg      P
43      6/8/2013      TD300      TPL      Fri      23      Log      P
46      6/8/2013      TD500      BCR      Fri      1      Leg      T
47      6/8/2013      TD300      Master      Fri      1      SQL      P
50      6/9/2013      QB01      KLL      Sat      1      Leg      P
53      6/9/2013      QB01      CTX08      Sat      1      Leg      P
56      6/9/2013      QB01      UTIL      Sat      1      Leg      P

and this is the data in table 2 that i have..
QB01      N/A      CTX08      John      Fed      N      ABC-XYZ      P
QB01      N/A      KLL      Mike      Sy      Y      ABC      PR
QB01      N/A      KLL      Mike      Sy      Y      ABC      PF
QB01      N/A      KLL      Dick      Wyke      N      ABC      PR
QB01      N/A      KLL      Bill      Smith      N      ABC      P
QB01      N/A      Model      Jo      Snyder      N      ABC-XYZ      P
QB01      N/A      ZX20      Tom      Vador      N      ABC-XYZ      P
TD300      N/A      TPL      Ken      Herr      N      ABC      P
TD336      N/A      ASD      Luo      Drew      N      ABC-XYZ      P
TD500      N/A      BCR      Jack      Bean      N      ABC      P

is it possible that i might have changed my insert statement..?
Avatar of Rao_S
Rao_S

ASKER

i do get this result..
INST      DB      TYPE      PRIMARYDBA      TIMESFAILED
QB01      KLL      Leg      Tom, Vador      5
TD300      Master      SQL      Ken, Herr                      3
TD300      TPL      Log      Ken, Herr                      2
QB01      CTX08      Leg      Tom, Vador      1
QB01      UTIL      Leg      Tom, Vador      1
TD336      ASD      SQL      Luo, Drew      1
TD500      BCR      Leg      Jack, Bean                      1
Avatar of Sean Stuber
Sean Stuber
I figured it out

----   where   trunc(b.mdate) > trunc(sysdate)-8


If I use

  where   trunc(b.mdate) > trunc(sysdate)-9

it works

you must be a day a head of me
Avatar of Rao_S
Rao_S

ASKER

and this is the query i am running..
select  inst,
        db,
        type,
        primarydba,
        count(1) as timesfailed
from ( select * from ( select distinct b.inst, b.db, b.type,
                                (select max(d1.first || ', ' || d1.last)
                                 from   t2 d1
                                 where  d1.inst = b.inst
                                ) primarydba,
                                b.day
                        FROM    t1 b
                        where   trunc(b.mdate) > trunc(sysdate)-8
                        and     b.inst in (    select distinct a.inst
                                               from    t1 a,
                                                       t2 d2
                                               where   d2.inst = a.inst
                                               and     d2.en = 'P'
                                               and     d2.old = 'N'
                                               and     d2.org in ('ABC', 'ABC-XYZ')
                                            )
                                    group by b.inst, b.db, b.type, b.day
                          ) TBL)
GROUP BY inst, db, type, primarydba
ORDER BY timesfailed desc, inst, db, type;
Avatar of Sean Stuber
Sean Stuber
it doesn't help to repost the same thing but it's ok, I've alredy got the date problem figured out

so, now that I can mimick your initial results

please explain what your expected are supposed to represent

why are some of them "unknown"  ?  why do you want Luo, Drew instead of Tom, Vador?
Avatar of Rao_S
Rao_S

ASKER

yes, i must be a day ahead..!
Avatar of Rao_S
Rao_S

ASKER

1) table 1 has QB01 and UTIL, this entry does not exist in table 2, so i cannot find a primarydba for this entry and hence the result has to show that the primary dba is 'Unknown'.
2) for QB01, KLL the primarydba should be Bill, Smith and not Tom, Vador because in table 2 the only valid entries for QB01 are where 'en' = 'P' similarly only valid entries are where 'org' in ('ABC', 'ABC-XYZ').
3) similarly TS300 Master also does not exist in table 2, hence the primarydba is Unknown.
Avatar of DOSLover
DOSLover
Flag of United States of America image
In the subquery where you select primary dba, I would try the following with an additional condition and NVL functions:
select distinct b.inst, b.db, b.type,
                                NVL((select max(d1.first || ', ' || d1.last) 
                                 from   t2 d1 
                                 where  d1.inst = b.inst and d1.type=b.type
                                ),'Unknown') primarydba,

Open in new window

Avatar of Rao_S
Rao_S

ASKER

tried that, does not work..
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rao_S
Rao_S

ASKER

thanks a ton sdstuber!
Avatar of Sean Stuber
Sean Stuber
And a more efficient version that doesn't need to double query the t2 table


SELECT   inst, db, TYPE, primarydba, COUNT(*) AS times
    FROM (SELECT   b.inst,
                   b.db,
                   b.TYPE,
                   MIN(CASE WHEN d2.db = b.db THEN d2.FIRST || ', ' || d2.LAST ELSE 'Unknown' END)
                       primarydba,
                   b.day
              FROM t1 b
                   LEFT JOIN
                   t2 d2
                       ON     TRUNC(b.mdate) > TRUNC(SYSDATE) - 8 -- change to 9 depending on TZ
                          AND b.inst = d2.inst
                          AND d2.en = 'P'
                          AND d2.old = 'N'
                          AND d2.org IN ('ABC', 'ABC-XYZ')
          GROUP BY b.inst, b.db, b.TYPE, b.day)
GROUP BY inst, db, TYPE, primarydba
ORDER BY times DESC, inst, db, TYPE;
Avatar of Rao_S
Rao_S

ASKER

i din't know one could say "left join" "on" and have -->  TRUNC(b.mdate) > TRUNC(SYSDATE) - 8         as the first entry before join prdicate --->   b.inst = d2.inst !
Avatar of Sean Stuber
Sean Stuber
you could put the date criteria on t1  as a where clause if you'd like


SELECT   inst, db, TYPE, primarydba, COUNT(*) AS times
    FROM (SELECT   b.inst,
                   b.db,
                   b.TYPE,
                   MIN(CASE WHEN d2.db = b.db THEN d2.FIRST || ', ' || d2.LAST ELSE 'Unknown' END)
                       primarydba,
                   b.day
              FROM t1 b
                   LEFT JOIN
                   t2 d2
                       ON     b.inst = d2.inst
                          AND d2.en = 'P'
                          AND d2.old = 'N'
                          AND d2.org IN ('ABC', 'ABC-XYZ')
             WHERE TRUNC(b.mdate) > TRUNC(SYSDATE) - 8                -- change to 9 depending on TZ
          GROUP BY b.inst, b.db, b.TYPE, b.day)
GROUP BY inst, db, TYPE, primarydba
ORDER BY times DESC, inst, db, TYPE;


Logically - the date filter in the ON clause is applied as part of the join,  the date filter in the WHERE clause is applied after the join

Functionally - they do the same thing since the results since the filter is applied to the driving table, not the outer one.  And, checking the explain plan, I see the optimizer applied the filter before doing the join.  Which is again, functionally equivalent, but more efficient since it's not joining to rows it doesn't need.
Avatar of Rao_S
Rao_S

ASKER

i see, thank you..
Avatar of Rao_S
Rao_S

ASKER

Thank you..
Avatar of Sean Stuber
Sean Stuber
I don't understand the split

you selected 3 posts that worked  - one of which you should not use because it's inefficient
and two others where one of them confused you

and one that didn't work at all


Splitting makes sense if there are multiple viable solutions or multiples posts that contribute to a solution

but that doesn't seem to be the case here
if you're trying to award for effort - don't do that.  Award only to the posts that are actually helpful
Avatar of Rao_S
Rao_S

ASKER

sorry, i think i wan to award all the 400 points to the first one, because on executing that query on the our tables i get a row count of 1415 which i am in the process of verifying. when i the 2rd query i get 3331 rows which is not correct, becuase is picking up Q data, i dint try the 3rd query on the real data yet... so i would like to award the points to the first query..?
Avatar of Sean Stuber
Sean Stuber
Can you provide more sample data?

All three queries produce the same results for the data given above.
Avatar of Rao_S
Rao_S

ASKER

hi sdstuber,
i am sorry, i took so long to re-address this question..
below i have given 2 insert statements....
for the above data, please run an update like so.... because our query looks for the past 7 days, we need to update the mdate...
update t1 set mdate = mdate+29;
then..
insert into t1 values (60,to_date('07/08/2013','mm/dd/yyyy'),'CLVT','ClearV','Mon',1,'Log','D');
insert into t1 values (64,to_date('07/08/2013','mm/dd/yyyy'),'FXOQ','FXRecon','Mon',1,'Log','q');

after you enter thses rows and run the 3 queries, you will notice that the 2nd and 3rd queries return 9 rows while the first on returns 7 rows..
Avatar of Sean Stuber
Sean Stuber
what are the results you are looking for?
Avatar of Rao_S
Rao_S

ASKER

in the 2 insert statements above the last column is 'D' and 'Q' for DEV and QA, so i dont want to select D and Q only P which is PROD databases.. the column "en" stands for 'environmenttype' which could be DEV/QA/PROD...
i need to select only P databases...
Avatar of Sean Stuber
Sean Stuber
please "show" the results you want

like you did originally


INST      DB      TYPE      PRIMARYDBA      TIMES
QB01      KLL      Leg      Bill, Smith      5
TD300      Master      SQL      Unknown                      3
TD300      TPL      Log      Ken, Herr                      2
QB01      CTX08      Leg      John, Fed                      1
QB01      UTIL      Leg      Unknown                      1
TD336      ASD      SQL      Luo, Drew      1
TD500      BCR      Leg      Jack, Bean                      1
Avatar of Rao_S
Rao_S

ASKER

hi sdstuber,
here is a result set desired..
INST      DB      TYPE      PRIMARYDBA      TIMES
QB01      KLL      Leg      Bill, Smith      5
TD300      Master      SQL      Unknown                      3
TD300      TPL      Log      Ken, Herr                 2
QB01      CTX08      Leg      John, Fed                      1
QB01      UTIL      Leg      Unknown                      1
TD336      ASD      SQL      Luo, Drew      1
TD500      BCR      Leg      Jack, Bean                      1
Avatar of DOSLover
DOSLover
Flag of United States of America image
Hi Rao_S,
I will give one more try. What I see is your data is mainly coming from t1 table (including count). You are trying to get primarydba from t2. And If primary dba is not found for inst and db, you need a value of 'Unknown'. I will use NVL and get the matching primarydba from t2.
SELECT INST, DB, TYPE
	   , NVL((Select DISTINCT first || ', ' || last from t2 
	           where t2.inst = t1.inst 
			     and t2.db = t1.db 
				 and en = 'P' 
				 and old='N' 
				 and org in ('ABC', 'ABC-XYZ')),'Unknown') as PrimaryDBA
       ,count(*) as TimesFailed
  FROM T1
 WHERE en='P'
   and trunc(mdate) > trunc(sysdate)-8
 GROUP BY INST, DB, TYPE

Open in new window

Avatar of Rao_S
Rao_S

ASKER

hi DOSLover,
this query returns one row short..TD500 is missing from the result set..
Avatar of Rao_S
Rao_S

ASKER

this one is the best solution!!!
thank you sdstuber and DOSLover...
Avatar of Sean Stuber
Sean Stuber
you accepted my answer, but does it work for you?
Avatar of Rao_S
Rao_S

ASKER

yes it works perfectly..!
Avatar of Sean Stuber
Sean Stuber
ok, just making sure since I hadn't responded yet to your followup I wanted to make sure you didn't accept prematurely if there was still outstanding work to do