none
Select com 2 Subqueries RRS feed

  • Pergunta

  • Caros colegas,

     

    Preciso montar um select com duas subqueries e não estou conseguindo.

     

    Query 1:

     

    (SELECT A.DT_REF,                                                     
    CASE LENGTH(CAST(B.NR_UND AS VARCHAR (5)))                            
    WHEN 5 THEN CAST(B.NR_UND AS VARCHAR (4))                             
    WHEN 4 THEN CAST(B.NR_UND AS VARCHAR (3))                             
    WHEN 3 THEN CAST(B.NR_UND AS VARCHAR (2))                             
    WHEN 2 THEN CAST(B.NR_UND AS VARCHAR (1))                             
    WHEN 1 THEN CAST(B.NR_UND AS VARCHAR (1)) END ||                      
    CASE LENGTH(CAST(A.NR_OPE_EPT AS VARCHAR (7)))                        
    WHEN 7 THEN '0'||CAST(A.NR_OPE_EPT AS VARCHAR (6))                    
    WHEN 6 THEN '00'||CAST(A.NR_OPE_EPT AS VARCHAR (5))                   
    WHEN 5 THEN '000'||CAST(A.NR_OPE_EPT AS VARCHAR (4))                  
    WHEN 4 THEN '0000'||CAST(A.NR_OPE_EPT AS VARCHAR (3))                 
    WHEN 3 THEN '00000'||CAST(A.NR_OPE_EPT AS VARCHAR (2))                
    WHEN 2 THEN '000000'||CAST(A.NR_OPE_EPT AS VARCHAR (1)) END AS NR_OPE 
    FROM APLIC.TB214_OPE_EPT A, CAPTA.TB035_CC B                          
    WHERE A.NR_CPF_CGC_DEV = B.NR_CPF_CGC                                 
    AND A.NR_CC_DB_PRS = B.NR_CC                                          
    AND CD_CAR_CC IN ('71','75','76')                                     

    AND A.TP_PES IN ('J'))

     

    Query 2:

     

    (SELECT                                                                     
    SUBSTR(CAST(NR_CTR AS VARCHAR (14)),1,LENGTH(CAST(NR_CTR AS VARCHAR(14)))-2)
    AS NR_OPE_1                                                                 
    FROM FINAN.TB067_CRT_CLI                                                    
    WHERE VL_OGN_CTR <>0                                                        
    AND CD_PRD IN (100)                                                         
    AND TP_OPE IN (7,8,10,12,21,25))                                            

     

    O campos que servirão de "chave" são NR_OPE (query 1) e NR_OPE_1 (query 2).

     

    Como eu faço esse "join"?

     

    Obrigado!

     

    cpcremonez

     

    quinta-feira, 29 de janeiro de 2009 18:17

Respostas

  • Boa Tarde,

     

    Na verdade não, pois, a ajuda inicial que o fórum pode fornecer é em SQL Server.

    Podemos tentar mas não há nenhuma garantia.

     

    Code Snippet

    SELECT T1.DT_REF, NR_OPE

    FROM

    (SELECT A.DT_REF,

    CASE LENGTH(CAST(B.NR_UND AS VARCHAR (5)))

    WHEN 5 THEN CAST(B.NR_UND AS VARCHAR (4))

    WHEN 4 THEN CAST(B.NR_UND AS VARCHAR (3))

    WHEN 3 THEN CAST(B.NR_UND AS VARCHAR (2))

    WHEN 2 THEN CAST(B.NR_UND AS VARCHAR (1))

    WHEN 1 THEN CAST(B.NR_UND AS VARCHAR (1)) END ||

    CASE LENGTH(CAST(A.NR_OPE_EPT AS VARCHAR (7)))

    WHEN 7 THEN '0'||CAST(A.NR_OPE_EPT AS VARCHAR (6))

    WHEN 6 THEN '00'||CAST(A.NR_OPE_EPT AS VARCHAR (5))

    WHEN 5 THEN '000'||CAST(A.NR_OPE_EPT AS VARCHAR (4))

    WHEN 4 THEN '0000'||CAST(A.NR_OPE_EPT AS VARCHAR (3))

    WHEN 3 THEN '00000'||CAST(A.NR_OPE_EPT AS VARCHAR (2))

    WHEN 2 THEN '000000'||CAST(A.NR_OPE_EPT AS VARCHAR (1)) END AS NR_OPE

    FROM APLIC.TB214_OPE_EPT A, CAPTA.TB035_CC B

    WHERE A.NR_CPF_CGC_DEV = B.NR_CPF_CGC

    AND A.NR_CC_DB_PRS = B.NR_CC

    AND CD_CAR_CC IN ('71','75','76')

    AND A.TP_PES IN ('J')) AS T1

     

    INNER JOIN

    (SELECT

    SUBSTR(CAST(NR_CTR AS VARCHAR (14)),1,LENGTH(CAST(NR_CTR AS VARCHAR(14)))-2)

    AS NR_OPE_1

    FROM FINAN.TB067_CRT_CLI

    WHERE VL_OGN_CTR <>0

    AND CD_PRD IN (100)

    AND TP_OPE IN (7,8,10,12,21,25)) AS T2

     

    ON T1.NR_OPE = T2.NR_OPE_1

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

    • Marcado como Resposta cpcremonez sexta-feira, 30 de janeiro de 2009 14:02
    quinta-feira, 29 de janeiro de 2009 19:15

Todas as Respostas

  • Boa Tarde,

     

    Seu SQL Server é 2005 ?

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

     

    quinta-feira, 29 de janeiro de 2009 18:32
  • Gustavo, na realidade estou usando uma ferramenta aqui da empresa o "QMF" e as consultas são realizadas com linguagem sql...

     

    Será que isso ajuda?

     

     

     

    quinta-feira, 29 de janeiro de 2009 18:39
  • Boa Tarde,

     

    Na verdade não, pois, a ajuda inicial que o fórum pode fornecer é em SQL Server.

    Podemos tentar mas não há nenhuma garantia.

     

    Code Snippet

    SELECT T1.DT_REF, NR_OPE

    FROM

    (SELECT A.DT_REF,

    CASE LENGTH(CAST(B.NR_UND AS VARCHAR (5)))

    WHEN 5 THEN CAST(B.NR_UND AS VARCHAR (4))

    WHEN 4 THEN CAST(B.NR_UND AS VARCHAR (3))

    WHEN 3 THEN CAST(B.NR_UND AS VARCHAR (2))

    WHEN 2 THEN CAST(B.NR_UND AS VARCHAR (1))

    WHEN 1 THEN CAST(B.NR_UND AS VARCHAR (1)) END ||

    CASE LENGTH(CAST(A.NR_OPE_EPT AS VARCHAR (7)))

    WHEN 7 THEN '0'||CAST(A.NR_OPE_EPT AS VARCHAR (6))

    WHEN 6 THEN '00'||CAST(A.NR_OPE_EPT AS VARCHAR (5))

    WHEN 5 THEN '000'||CAST(A.NR_OPE_EPT AS VARCHAR (4))

    WHEN 4 THEN '0000'||CAST(A.NR_OPE_EPT AS VARCHAR (3))

    WHEN 3 THEN '00000'||CAST(A.NR_OPE_EPT AS VARCHAR (2))

    WHEN 2 THEN '000000'||CAST(A.NR_OPE_EPT AS VARCHAR (1)) END AS NR_OPE

    FROM APLIC.TB214_OPE_EPT A, CAPTA.TB035_CC B

    WHERE A.NR_CPF_CGC_DEV = B.NR_CPF_CGC

    AND A.NR_CC_DB_PRS = B.NR_CC

    AND CD_CAR_CC IN ('71','75','76')

    AND A.TP_PES IN ('J')) AS T1

     

    INNER JOIN

    (SELECT

    SUBSTR(CAST(NR_CTR AS VARCHAR (14)),1,LENGTH(CAST(NR_CTR AS VARCHAR(14)))-2)

    AS NR_OPE_1

    FROM FINAN.TB067_CRT_CLI

    WHERE VL_OGN_CTR <>0

    AND CD_PRD IN (100)

    AND TP_OPE IN (7,8,10,12,21,25)) AS T2

     

    ON T1.NR_OPE = T2.NR_OPE_1

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

    • Marcado como Resposta cpcremonez sexta-feira, 30 de janeiro de 2009 14:02
    quinta-feira, 29 de janeiro de 2009 19:15
  • Gustavo, obrigado pela ajuda... funcionou perfeitamente.

    cpcremonez

    sexta-feira, 30 de janeiro de 2009 14:03