Home » SQL & PL/SQL » SQL & PL/SQL » how to rewrite this query (oracle 10g)
how to rewrite this query [message #417727] Tue, 11 August 2009 05:02 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member





  SELECT EMP.EMPNO
  (SELECT EMP.AMOUNT FROM EMP_MASTER EMP1
    WHERE EMP1.EMPCODE= EM.EMPCODE
    and EMP1.CODE IN 
                 (SELECT DISTINCT (CASE
                                    WHEN (DTL.CODE = 'SBA' OR
                                         DTL.CODE = 'BAS') THEN
                                     'BAS'
                                    WHEN (DTL.CODE = 'SCS' OR
                                         DTL.CODE = 'CHA') THEN
                                     'CHA'
                                    WHEN (DTL.CODE = 'SOS' OR
                                         DTL.CODE = 'CUS') THEN
                                     'CUS'
                                    ELSE
                                     DTL.CODE
                                  END)
                    FROM EMP_DETAILS DTL
                   WHERE DTL.EMPCODE = EM.EMPCODE
                     AND DTL.INDICATOR = 'E'
                     AND DTL.EMPID = EM.EMPID
                     )) AMOUNT

  FROM EMP_MASTER EM;


  In the above query below part is taking lot of time ;i created indexes on EMPCODE and EMPID but it taking lot of time.how to rewrite this DISTINCT CASE query.

   (SELECT DISTINCT (CASE
                                    WHEN (DTL.CODE = 'SBA' OR
                                         DTL.CODE = 'BAS') THEN
                                     'BAS'
                                    WHEN (DTL.CODE = 'SCS' OR
                                         DTL.CODE = 'CHA') THEN
                                     'CHA'
                                    WHEN (DTL.CODE = 'SOS' OR
                                         DTL.CODE = 'CUS') THEN
                                     'CUS'
                                    ELSE
                                     DTL.CODE
                                  END)
                    FROM EMP_DETAILS DTL
                   WHERE DTL.EMPCODE = EM.EMPCODE
                     AND DTL.INDICATOR = 'E'
                     AND DTL.EMPID = EM.EMPID
                     )

Re: how to rewrite this query [message #417729 is a reply to message #417727] Tue, 11 August 2009 05:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
DISTINCT is not needed for an IN clause

Without further info this is about as much as we can tell you about your query, apart from the fact that you might have mixed up your aliases..

[Updated on: Tue, 11 August 2009 05:15]

Report message to a moderator

Re: how to rewrite this query [message #417730 is a reply to message #417727] Tue, 11 August 2009 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
In sub-queries never need DISTINCT - so remove it and see what happens.

And next time you post a query make sure it's syntax is correct - that one won't compile.
Re: how to rewrite this query [message #417734 is a reply to message #417727] Tue, 11 August 2009 05:20 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Also, your CASE can be simplified to:
CASE DTL.CODE WHEN 'SBA' THEN 'BAS'
              WHEN 'SCS' THEN 'CHA'
              WHEN 'SOS' THEN 'CUS'
              ELSE  DTL.CODE
END
Previous Topic: Identifying Dates and Create Coverage Periods
Next Topic: ORA-06530: Reference to uninitialized composite
Goto Forum:
  


Current Time: Sat Dec 10 04:55:14 CST 2016

Total time taken to generate the page: 0.18072 seconds