Select com 2 Subqueries

Question

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

 

Answer 1

Boa Tarde,

 

Seu SQL Server é 2005 ?

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 2

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?

 

 

 

Answer 3

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

Answer 4

Gustavo, obrigado pela ajuda... funcionou perfeitamente.

cpcremonez