Solved

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

Posted on 2013-06-10
32
372 Views
Last Modified: 2013-07-16
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?
0
Comment
Question by:Rao_S
  • 17
  • 12
  • 2
32 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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?
0
 

Author Comment

by:Rao_S
Comment Utility
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..?
0
 

Author Comment

by:Rao_S
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 

Author Comment

by:Rao_S
Comment Utility
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;
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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?
0
 

Author Comment

by:Rao_S
Comment Utility
yes, i must be a day ahead..!
0
 

Author Comment

by:Rao_S
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:DOSLover
Comment Utility
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

0
 

Author Comment

by:Rao_S
Comment Utility
tried that, does not work..
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
SELECT   inst, db, TYPE, primarydba, COUNT(1) AS times
    FROM (SELECT *
            FROM (SELECT   b.inst,
                           b.db,
                           b.TYPE,
                           nvl((SELECT min(d1.FIRST || ', ' || d1.LAST)
                              FROM t2 d1
                             WHERE d1.inst = b.inst AND d1.db = b.db ),'Unknown')
                               primarydba,
                           b.day
                      FROM t1 b
                     WHERE     TRUNC(b.mdate) > TRUNC(SYSDATE) - 8 -- change to 9 depending on TZ
                           AND b.inst IN
                                   (SELECT inst
                                      FROM t2 d2
                                     WHERE     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;
0
 

Author Comment

by:Rao_S
Comment Utility
thanks a ton sdstuber!
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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;
0
 

Author Comment

by:Rao_S
Comment Utility
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 !
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Rao_S
Comment Utility
i see, thank you..
0
 

Author Comment

by:Rao_S
Comment Utility
Thank you..
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 

Author Comment

by:Rao_S
Comment Utility
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..?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Can you provide more sample data?

All three queries produce the same results for the data given above.
0
 

Author Comment

by:Rao_S
Comment Utility
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..
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
what are the results you are looking for?
0
 

Author Comment

by:Rao_S
Comment Utility
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...
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 

Author Comment

by:Rao_S
Comment Utility
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
0
 
LVL 5

Expert Comment

by:DOSLover
Comment Utility
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

0
 

Author Comment

by:Rao_S
Comment Utility
hi DOSLover,
this query returns one row short..TD500 is missing from the result set..
0
 

Author Closing Comment

by:Rao_S
Comment Utility
this one is the best solution!!!
thank you sdstuber and DOSLover...
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you accepted my answer, but does it work for you?
0
 

Author Comment

by:Rao_S
Comment Utility
yes it works perfectly..!
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now