How to compare Select

Asked

Viewed 939 times

1

I have a query in Oracle a select with some sub selects, I have to consult several tables and bring all the data in the main select a single Row, I have two sub selects of the same table and I can not repeat the data, have to compare two selects, in the case would be the tbl_contrato_avalista, I have to bring the fields of two evaluators, have as I compare with the previous sub-select, or aramazenar select and compare. I searched for SQL features and couldn’t get an idea of how to compare selects. Select without sub-query of endorsers TBL_CONTRATOS_AVALISTA works perfectly. This query would be to fill out a single Row dataReader to send to the crystal report

    SELECT R.NOM_CLIEN
      ,(SELECT COD_CONTEUDO
               FROM TBL_REGISTROS_INDICADORES RI
               WHERE RI.COD_CREDOR = C.COD_CREDOR AND
                     RI.DES_REGIS = C.DES_REGIS AND                               
                     RI.COD_INDICADOR = 'NACIONA') AS NACIONALIDADE
      ,(SELECT COD_CONTEUDO
        FROM TBL_REGISTROS_INDICADORES RI
        WHERE RI.COD_CREDOR = C.COD_CREDOR AND
              RI.DES_REGIS = C.DES_REGIS AND                               
              RI.COD_INDICADOR = 'PROFISSA') AS PROFISSAO                                
      ,R.IND_ESTAD_CIVIL
      ,R.DES_RG
      ,R.DES_CPF
      ,R.DES_ENDER_RESID
      ,R.DES_NUMER_RESID
      ,R.DES_BAIRR_RESID
      ,R.DES_CIDAD_RESID
      ,R.DES_CEP_RESID
      ,R.DES_ESTAD_RESID
      ,(SELECT SUM(VAL_PAGO)                
        FROM TBL_REGISTROS_COMPL_BAIXAS RCB
        WHERE C.DES_REGIS = RCB.DES_REGIS AND
              C.COD_CREDOR = RCB.COD_CREDOR AND
              C.DES_CONTR = RCB.DES_CONTR) AS VALOR_PAGO      
      ,(SELECT SUM(VAL_PRINC)
        FROM  TBL_REGISTROS_COMPLEMENTOS RC
        WHERE C.COD_CREDOR = RC.COD_CREDOR AND
              C.DES_REGIS = RC.DES_REGIS   AND
              --C.DES_CONTR = RC.DES_CONTR AND --VERIFICAR SE PRECISA COMPARAR CONTRATOS
              COD_PRODUT <> '002') AS SALDO_DEVEDOR                        
      ,(SELECT  TCA.NOM_AVALI
               ,TCA.DES_CPF
        FROM TBL_CONTRATOS_AVALISTA TCA 
        WHERE DES_REGIS = '436402114' 
        AND COD_TIPO = 'AVALISTA'
        LIMIT 1)
        ,(SELECT  TCA.NOM_AVALI
               ,TCA.DES_CPF
        FROM TBL_CONTRATOS_AVALISTA TCA 
        WHERE DES_REGIS = '436402114' 
        AND COD_TIPO = 'AVALISTA'
        LIMIT 1)
FROM TBL_REGISTROS_COMPLEMENTOS RC
INNER JOIN TBL_CONTRATOS C 
      ON C.COD_CREDOR = RC.COD_CREDOR AND 
         C.DES_REGIS = RC.DES_REGIS AND
         C.COD_PRODUT = RC.COD_PRODUT AND
         C.DES_CONTR = RC.DES_CONTR                             

INNER JOIN TBL_REGISTROS R
      ON R.DES_REGIS = C.DES_REGIS AND
         R.COD_CREDOR = C.COD_CREDOR

INNER JOIN TBL_REGISTROS_COMPL_BAIXAS RCB2
      ON C.DES_REGIS = RCB2.DES_REGIS AND
         C.COD_CREDOR = RCB2.COD_CREDOR AND
         C.DES_CONTR = RCB2.DES_CONTR 

LEFT JOIN (SELECT CA.COD_CREDOR
                 ,CA.DES_REGIS
                 ,CA.COD_PRODUT
                 ,CA.DES_CONTR
             FROM TBL_CONTRATOS_AVALISTA CA
            WHERE CA.COD_TIPO = 'SOCIO'
           GROUP BY CA.COD_CREDOR
                   ,CA.DES_REGIS
                   ,CA.COD_PRODUT
                   ,CA.DES_CONTR) SOCIO                                                
      ON SOCIO.COD_CREDOR = C.COD_CREDOR AND
         SOCIO.DES_REGIS = C.DES_REGIS AND
         SOCIO.COD_PRODUT = C.COD_PRODUT AND
         SOCIO.DES_CONTR = C.COD_PRODUT                              
WHERE R.DES_REGIS = '436402114' AND R.COD_CREDOR = '1' AND C.COD_PRODUT <> '002'
GROUP BY R.NOM_CLIEN
        ,R.IND_ESTAD_CIVIL
        ,R.DES_RG
        ,R.DES_CPF
        ,R.DES_ENDER_RESID
        ,R.DES_NUMER_RESID
        ,R.DES_BAIRR_RESID
        ,R.DES_CIDAD_RESID
        ,R.DES_CEP_RESID
        ,R.DES_ESTAD_RESID
        ,C.COD_CREDOR      
        ,C.DES_REGIS
        ,R.DES_REGIS
        ,C.DES_CONTR
sql select

  • Compare in what way? Why not pass subSELECT to a JOIN? In this case they would be LEFT JOIN.

  • Even using Join are two guarantors, but can occur a támbem, have as I compare the top Join with the bottom.

  • How do you know if you’re just a guarantor or more?

  • what is the difference between the two subselects TBL_CONTRATOS_AVALISTA? they look identical to me.

  • I don’t understand why you want to treat this in the bank, but you have the option of creating a Function for this, if you want to save the select and compare the two, but I think if you describe why you need this comparison it is easier to give you a light. Because treating something like this directly in the bank as much as possible will make it difficult to maintain the code, the right thing would be to treat it in your Controller, if I understood what you want to do.

  • Thanks for the help, but I handled in the code, the evaluator table has endorsers of several contracts, but usually 2 per contract, and I needed to fill out a dataReader with two different and different endorsers and everything in a single query, the solution was to make a separate consultation for the guarantor.

  • This query would be to fill out a single Row dataReader to send to the crystal report.

Show 2 more comments

2 answers

0


0

Try something like:

(SELECT  TCA.NOM_AVALI ,TCA.DES_CPF, TCB.NOM_AVALI ,TCB.DES_CPF
        FROM TBL_CONTRATOS_AVALISTA TCA 
        LEFT OUTER JOIN  TBL_CONTRATOS_AVALISTA TCB 
        ON (TCA.DES_CPF <> TCB.DES_CPF)
    WHERE TCA.DES_REGIS = '436402114' AND TCB.DES_REGIS = '436402114'
        AND TCA.COD_TIPO = 'AVALISTA' AND TCB.COD_TIPO = 'AVALISTA')

If necessary add a LIMIT 1.

Browser other questions tagged sql select

You are not signed in. Login or sign up in order to post.