OraFAQ Forum: SQL & PL/SQL » how to rewrite this query

Home » SQL & PL/SQL » SQL & PL/SQL » how to rewrite this query (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to rewrite this query [message #417727]

Tue, 11 August 2009 05:02

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
                     )

Report message to a moderator

Re: how to rewrite this query [message #417729 is a reply to message #417727]

Tue, 11 August 2009 05:13

Frank
Messages: 7901
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

cookiemonster
Messages: 13960
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.

Report message to a moderator

Re: how to rewrite this query [message #417734 is a reply to message #417727]

Tue, 11 August 2009 05:20

pablolee
Messages: 2882
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

Report message to a moderator

Previous Topic:	Identifying Dates and Create Coverage Periods
Next Topic:	ORA-06530: Reference to uninitialized composite

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 07:10:59 CST 2024