Solved

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

Posted on 2013-06-10
32
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 12
  • 2
32 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39235273
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
ID: 39235288
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
ID: 39235298
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39235300
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
ID: 39235301
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 74

Expert Comment

by:sdstuber
ID: 39235306
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
ID: 39235316
yes, i must be a day ahead..!
0
 

Author Comment

by:Rao_S
ID: 39235360
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
ID: 39235363
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
ID: 39235382
tried that, does not work..
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39235405
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
ID: 39235430
thanks a ton sdstuber!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39235435
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
ID: 39235454
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 74

Expert Comment

by:sdstuber
ID: 39235468
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
 

Author Comment

by:Rao_S
ID: 39235474
i see, thank you..
0
 

Author Comment

by:Rao_S
ID: 39235540
Thank you..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39235662
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
ID: 39235890
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 74

Expert Comment

by:sdstuber
ID: 39236090
Can you provide more sample data?

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

Author Comment

by:Rao_S
ID: 39311372
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 74

Expert Comment

by:sdstuber
ID: 39311519
what are the results you are looking for?
0
 

Author Comment

by:Rao_S
ID: 39311556
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 74

Expert Comment

by:sdstuber
ID: 39311812
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
ID: 39311831
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
ID: 39315760
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
ID: 39325196
hi DOSLover,
this query returns one row short..TD500 is missing from the result set..
0
 

Author Closing Comment

by:Rao_S
ID: 39325203
this one is the best solution!!!
thank you sdstuber and DOSLover...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39325383
you accepted my answer, but does it work for you?
0
 

Author Comment

by:Rao_S
ID: 39329836
yes it works perfectly..!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39329896
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

726 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