CODEWITHSUNDEEP
sqloracle-databasequery-performance
BugsOverflow
BugsOverflow

Reputation: 538

Is there a way to improve more this query

I have been learning how to use INNER JOIN so I modified the following query to use inner join but im having trouble with the last fragment of the query, is it possible to use inner join there? Im having trouble beacuse "MOV" is the query done before this fragment... Could someone help me understand how to use there? The following queries work, just trying to improve them as much as I can

fragment:

FROM   TBLMOVOBLIGACIONES MOV1
                                  WHERE MOV1.STRMOVANOMES = :periodo
                                   AND  MOV1.STRCLINIT = MOV.STRCLINIT
                                   AND  MOV1.STROBLOBLIGSARC = MOV.STROBLOBLIGSARC

Full Query:

SELECT * FROM (

 

SELECT MOV.NUMPROCODIGO APLICATIVO,
       MOV.STRCLINIT NIT,
       CL.STRCLINOMBRE CLIENTE,
       MOV.STROBLOBLIGSARC OBLIGACION,
       MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO SALDO_OBL,
       MOV.NUMMOVTIPOCREDITO TIPO_CREDITO,
       MOV.NUMMOVNRODIASCAP DIAS_CAP,
       MOV.NUMMOVNRODIASINT DIAS_INT,
       MOV.STRMOVCALIFEDAD,
       CASE 
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 30 THEN 'A'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
        WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
       END AS CALIFEDAD_REAL
FROM TBLMOVOBLIGACIONES MOV INNER JOIN TBLCLIENTES CL ON MOV.STRCLINIT = CL.STRCLINIT 
WHERE MOV.STRMOVANOMES = :periodo
 AND  MOV.NUMPROCODIGO NOT IN (24)
 AND MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO > 0
 AND  MOV.NUMMOVTIPOCREDITO = 1
 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != (SELECT CASE 
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 30  THEN 'A'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
                                     END AS CALIF_CALC
                              FROM   TBLMOVOBLIGACIONES MOV1
                              WHERE MOV1.STRMOVANOMES = :periodo
                               AND  MOV1.STRCLINIT = MOV.STRCLINIT
                               AND  MOV1.STROBLOBLIGSARC = MOV.STROBLOBLIGSARC)

Upvotes: 0

Views: 59

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

I would question this part of your query:

 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != (SELECT CASE 
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 30  THEN 'A'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                                      WHEN GREATEST(NVL(MOV1.NUMMOVNRODIASCAP,0), NVL(MOV1.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
                                     END AS CALIF_CALC
                              FROM   TBLMOVOBLIGACIONES MOV1
                              WHERE MOV1.STRMOVANOMES = :periodo
                               AND  MOV1.STRCLINIT = MOV.STRCLINIT
                               AND  MOV1.STROBLOBLIGSARC = MOV.STROBLOBLIGSARC)

Either (strmovanomes, strclinit, stroblobligsarc) includes (or exactly is) the primary key of tblmovobligaciones or it doesn't.

If if does, then the subquery is querying the same row from tblmovobligaciones that you already have. In that case, the subquery should be removed and the condition replaced with:

 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != CASE 
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 30  THEN 'A'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                                      WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
                                     END

If it does not then what is preventing that subquery from returning multiple rows (which would cause an error at runtime)?

You might also select that big expression one time and reuse it. It's a matter of preference. That would look like this:

SELECT MOV.NUMPROCODIGO APLICATIVO,
       MOV.STRCLINIT NIT,
       CL.STRCLINOMBRE CLIENTE,
       MOV.STROBLOBLIGSARC OBLIGACION,
       MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO SALDO_OBL,
       MOV.NUMMOVTIPOCREDITO TIPO_CREDITO,
       MOV.NUMMOVNRODIASCAP DIAS_CAP,
       MOV.NUMMOVNRODIASINT DIAS_INT,
       MOV.STRMOVCALIFEDAD,
       mov.califedad_real_expr CALIFEDAD_REAL
FROM ( SELECT mov.*,
              CASE 
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=0 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 30 THEN 'A'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=31 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 90 THEN 'B'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=91 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 180 THEN 'C'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=181 AND GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) <= 360 THEN 'D'
                WHEN GREATEST(NVL(MOV.NUMMOVNRODIASCAP,0), NVL(MOV.NUMMOVNRODIASINT,0)) >=361 THEN 'E'
              END AS califedad_real_expr
        FROM TBLMOVOBLIGACIONES MOV) MOV 
INNER JOIN TBLCLIENTES CL ON MOV.STRCLINIT = CL.STRCLINIT 
WHERE MOV.STRMOVANOMES = :periodo
 AND  MOV.NUMPROCODIGO NOT IN (24)
 AND MOV.NUMMOVVLRCAPCREDITO + MOV.NUMMOVVLRINTCREDI + MOV.NUMMOVVLRCAPOTRO > 0
 AND  MOV.NUMMOVTIPOCREDITO = 1
 AND  NVL(MOV.STRMOVCALIFEDAD,'N') != mov.califedad_real_expr

(Assuming I'm right about the subquery being unnecessary)

Upvotes: 1

Related Questions