Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchial queries with union. (10.2.0.,Linux)
Hierarchial queries with union. [message #404046] Wed, 20 May 2009 03:34 Go to next message
saumendra
Messages: 2
Registered: May 2009
Junior Member
I am having two complex queries with both having hierarchial queries and i have used union to combine both the queries.

If i run both queries individually i get 7 and 0 rows respectively but if i use union of both queries i get 28 rows with some unwanted records Shocked

Plase help.
Thanks in advance
Re: Hierarchial queries with union. [message #404049 is a reply to message #404046] Wed, 20 May 2009 03:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Please show what you have done and what you are getting as an output to help you better.
Provide test case details also.

regards,
Delna
Re: Hierarchial queries with union. [message #404051 is a reply to message #404046] Wed, 20 May 2009 03:40 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
And how many time would you post your problem?

regards,
Delna
Re: Hierarchial queries with union. [message #404052 is a reply to message #404046] Wed, 20 May 2009 03:50 Go to previous messageGo to next message
saumendra
Messages: 2
Registered: May 2009
Junior Member
my query is something like this
SELECT B1.FUNCTIONDESCRIPTION,
            SUM(A1.TOTALCASES) "TotalCases",
       SUM(A1.NEWCASES) "NewCases",
       SUM(A1.CASESFORTODAY) "CasesFortoday",
       SUM(A1.FAILEDCASES) "FailedCases",
       1
  FROM (SELECT W.STAGE STAGE,
               COUNT(*) TOTALCASES,
               SUM(CASE
                     WHEN NVL(W.ACCESSSTATUS, 'N') = 'N' THEN
                      1
                     ELSE
                      0
                   END) NEWCASES,
               SUM(CASE
                     WHEN NVL(W.ACCESSSTATUS, 'N') = 'N' AND
                          TO_CHAR(W.STATUSBUSIDATE, 'dd/mm/yyyy') = '02/02/2009' THEN
                      1
                     ELSE
                      0
                   END) CASESFORTODAY,
               SUM(CASE
                     WHEN INSTR(EXESERVICE(L.COMP_APPL_ID, W.STAGE), 'Fail') > 0 THEN
                      1
                     ELSE
                      0
                   END) FAILEDCASES
          FROM LOT_WORKFLOWSTAGE_DTL   W,
               LOT_LISTVIEW_DTL        L,
               EMPLOYEE                E,
               LOT_APPLICATIONFLAG_DTL FL
         WHERE W.COMP_APPL_ID = L.COMP_APPL_ID
           AND W.EMPLOYEECODE = E.EMPLOYEECODE
           AND W.COMPANYID = 5000
           AND (W.EMPLOYEECODE IN ('67'))
           AND W.STAGESTATUS IN ('P')
           AND W.STATUS != 'X'
           AND E.STATUS != 'X'
           AND FL.COMP_APPL_ID = W.COMP_APPL_ID
           AND (FL.ISCOLDLEAD <> 'Y' OR FL.ISCOLDLEAD IS NULL)
         GROUP BY W.STAGE) A1,
       (SELECT S.FUNCTIONID,
               S.FUNCTIONDESCRIPTION,
               S.PARENTFUNTIONID,
               S.URL,
               S.URL AS URL1,
               S.SEQUENCENO,
               S.PARENTFUNTIONID AS PARENTFUNTIONID1,
               S.STAGE
          FROM SEC_FUNCTION S
         WHERE S.FUNCTIONID IN (SELECT FUNCTIONID
                                  FROM SEC_GROUP             B,
                                       SEC_GROUP_FUNCTION_MX C,
                                       SEC_USER              D,
                                       SEC_GROUP_USER_MX     E
                                 WHERE B.GROUPID = C.GROUPID
                                   AND D.USERID = 'CORPUSR'
                                   AND D.ID = E.USERID
                                   AND E.GROUPID = C.GROUPID
                                   AND B.STATUS <> 'X'
                                   AND C.STATUS <> 'X'
                                   AND D.STATUS <> 'X'
                                   AND E.STATUS <> 'X')
           AND NVL(S.PARENTFUNTIONID, 0) IN
               (SELECT FUNCTIONID
                  FROM SEC_FUNCTION
                 WHERE NVL(PARENTFUNTIONID, 0) = 5)
           AND S.STATUS <> 'X'
           AND PARENTFUNTIONID IS NOT NULL
         START WITH PARENTFUNTIONID IS NULL
        CONNECT BY PRIOR S.FUNCTIONID = S.PARENTFUNTIONID
         ORDER SIBLINGS BY 4, 1) B1
 WHERE INSTR(B1.STAGE, A1.STAGE) > 0
 and b1.PARENTFUNTIONID is not null
 GROUP BY B1.FUNCTIONDESCRIPTION, B1.URL


UNION 



sELECT B.FUNCTIONDESCRIPTION,
      
       SUM(A.TOTALCASES) "TotalCases",
       SUM(A.NEWCASES) "NewCases",
       SUM(A.CASESFORTODAY) "CasesFortoday",
       SUM(A.FAILEDCASES) "FailedCases",
       2
  FROM (SELECT 'KIV' STAGE,
               COUNT(*)  TOTALCASES,
               SUM(CASE
                     WHEN NVL(W.ACCESSSTATUS, 'N') = 'N' THEN
                      1
                     ELSE
                      0
                   END) NEWCASES,
               SUM(CASE
                     WHEN NVL(W.ACCESSSTATUS, 'N') = 'N' AND
                          TO_CHAR(W.STATUSBUSIDATE, 'dd/mm/yyyy') = '02/02/2009' THEN
                      1
                     ELSE
                      0
                   END) CASESFORTODAY,
               SUM(CASE
                     WHEN INSTR(EXESERVICE(L.COMP_APPL_ID, W.STAGE), 'Fail') > 0 THEN
                      1
                     ELSE
                      0
                   END) FAILEDCASES
          FROM LOT_WORKFLOWSTAGE_DTL W,
               LOT_LISTVIEW_DTL      L,
               EMPLOYEE              E,
               LOT_ROUTING_DTL       R
         WHERE W.COMP_APPL_ID = L.COMP_APPL_ID
           AND W.EMPLOYEECODE = E.EMPLOYEECODE
           AND W.COMPANYID = 5000
           AND (R.ROUTEDTO IN ('67'))
           AND W.STAGESTATUS IN ('D')
           AND W.STATUS != 'X'
           AND W.LOANSTAGEID = R.LOANSTAGEID
           AND R.RESUBMITTINGDATE IS NULL
           AND E.STATUS != 'X'
         GROUP BY 'KIV') A,
       (SELECT S.FUNCTIONID,
               S.FUNCTIONDESCRIPTION,
               S.PARENTFUNTIONID,
               S.URL,
               S.URL AS URL1,
               S.SEQUENCENO,
               S.PARENTFUNTIONID AS PARENTFUNTIONID1,
               S.STAGE
          FROM SEC_FUNCTION S
         WHERE S.FUNCTIONID IN (SELECT FUNCTIONID
                                  FROM SEC_GROUP             B,
                                       SEC_GROUP_FUNCTION_MX C,
                                       SEC_USER              D,
                                       SEC_GROUP_USER_MX     E
                                 WHERE B.GROUPID = C.GROUPID
                                   AND D.USERID = 'CORPUSR'
                                   AND D.ID = E.USERID
                                   AND E.GROUPID = C.GROUPID
                                   AND B.STATUS <> 'X'
                                   AND C.STATUS <> 'X'
                                   AND D.STATUS <> 'X'
                                   AND E.STATUS <> 'X')
           AND NVL(S.PARENTFUNTIONID, 0) IN
               (SELECT FUNCTIONID
                  FROM SEC_FUNCTION
                 WHERE NVL(PARENTFUNTIONID, 0) = 5)
           AND S.STATUS <> 'X'
           AND PARENTFUNTIONID IS NOT NULL
         START WITH PARENTFUNTIONID IS NULL
        CONNECT BY PRIOR S.FUNCTIONID = S.PARENTFUNTIONID
         ORDER SIBLINGS BY 4, 1) B
 WHERE INSTR(B.STAGE, A.STAGE) > 0
 GROUP BY B.FUNCTIONDESCRIPTION, B.URL --B.FORMNAME1 ORDER BY 1 DESC
 order by 1 desc


[Mod-Edit: Frank added [notag][code][/notags]-tags to improve readability]

[Updated on: Wed, 20 May 2009 04:04] by Moderator

Report message to a moderator

Re: Hierarchial queries with union. [message #404056 is a reply to message #404052] Wed, 20 May 2009 04:00 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, according to your description you could try adding the undocumented where-clause.

where record is wanted


or even

where record is not unwanted


If you want a real answer you will need to provide a test case.
Previous Topic: Before Insert Trigger
Next Topic: Substr and Instr
Goto Forum:
  


Current Time: Fri Dec 09 08:09:24 CST 2016

Total time taken to generate the page: 0.13086 seconds