Select result feeding other 2 selects

Asked

Viewed 59 times

0

I have the select 1: 

SELECT DISTINCT
SD2.D2_DOC            AS NOTA,
CONCAT(RTRIM(SD2.D2_CLIENTE), ' - ', SA1.A1_NOME)        AS CLIENTE,
SD2.D2_COD              AS CODPRODUTO,
QEK_REVI                AS REVISÃO,
SD2.D2_LOTECTL          AS LOTE,
SB1.B1_DESC             AS DESCPRODUTO,
SB1.B1_X_ESPEC                                           AS NQUIMICO,
SC2.C2_XLOTEF         AS FORLOTE,
SC2.C2_XNPAIS         AS PAIS,
SC2.C2_XNOMFA         AS FABRICANTE,
CONVERT(VARCHAR(10), CAST(SC2.C2_XDTFAB AS DATE),103)    AS FABRICAÇÃO,
CONVERT(VARCHAR(20), CAST(SC2.C2_XDTVALI AS DATE),103)   AS VALIDADE,
SB1.B1_XDCB           AS DCB,
SB1.B1_XDCI           AS DCI,
SB1.B1_XCAS           AS CAS,
QE6.QE6_XMANUS        AS MANUSEIO,
QE6.QE6_XDERRA        AS DERRAMAMENTO,
QE6.QE6_XTRATA        AS TRATAMENTO,
QEK.QEK_CERQUA        AS NLAUDO
FROM SD2020 AS SD2
INNER JOIN SA1020 AS SA1 WITH(NOLOCK) ON SA1.A1_COD = SD2.D2_CLIENTE
INNER JOIN SB1020 AS SB1 WITH(NOLOCK) ON SB1.B1_COD = SD2.D2_COD
INNER JOIN SC2020 AS SC2 WITH(NOLOCK) ON SC2.C2_XLOTE = SD2.D2_LOTECTL
INNER JOIN QEK020 AS QEK WITH(NOLOCK) ON QEK.QEK_XORDEM = SC2.C2_NUM
INNER JOIN QE6020 AS QE6 WITH(NOLOCK) ON QE6.QE6_PRODUT = QEK.QEK_PRODUT
WHERE SD2.D2_DOC = '244788' AND SD2.D2_COD = '000878'
AND SA1.D_E_L_E_T_ = '' AND SB1.D_E_L_E_T_ = '' AND SD2.D_E_L_E_T_ = '' AND QEK.D_E_L_E_T_ = '' AND QE6.D_E_L_E_T_ = ''

Which brings me the following result:

CODPRODUTO  REVISÃO LOTE
000878          02  PS-010435/F01

need that with this first select I feed the fields of other 2 selects and in the end become a single result and possible?

2º select that needs to be powered by the 1st select.

SELECT
QE1.QE1_DESCPO    AS CARACTERISTICAS,
QE8.QE8_TEXTO     AS ESPECIFICAÇÕES,
QE8.QE8_METODO    AS METODO
FROM QE8020 AS QE8
INNER JOIN QE1010 AS QE1 WITH(NOLOCK) ON QE1.QE1_ENSAIO = QE8.QE8_ENSAIO 
WHERE QE8.QE8_PRODUT = '000878' AND QE8.QE8_REVI = '02' 
ORDER BY QE8_SEQLAB

3º select that also needs to be powered by the 1st select.

SELECT
QER_LOTE,
QER_REVI,
QEQ.QEQ_MEDICA   AS RESULTADO
FROM QER020 AS QER
INNER JOIN QEQ020 AS QEQ WITH(NOLOCK) ON QEQ.QEQ_CODMED = QER.QER_CHAVE
WHERE QER.QER_LOTE = 'PS-010435/F01'

Thanks in advance

sql sql-server

  • which bank is using ?

  • Sql server usage 2012

  • 1

    You can join all selects like this: / SELECT * FROM TABELAS_DO_PRIMEIRO ) A LEFT JOIN ( / 2 SELECT / SELECT * FROM TABELAS_DO_SEGUNDO ) B ON A.... = B..... LEFT JOIN ( / 3 SELECT */ SELECT * FROM TABELAS_DO_TERCEIRO ) C ON A..... = C......

  • 1

    Just use joins to join the selects. You just need to set the Join clauses by placing the variables of each select that should be equal.

  • I’m sorry, but I still don’t get it, I’m a database newbie, and I’ve never done a select inside the joins, if you can be a little clearer I really appreciate it...

1 answer

0


Do so:

You must ensure that the link fields in JOIN appear in tables.

That the fields COD_PRODUTO and REVISAO are selected in SELECT A and SELECT B and that the column QER_LOTE is selected in both select A and C

SELECT * FROM  (

SELECT DISTINCT
SD2.D2_DOC            AS NOTA,
CONCAT(RTRIM(SD2.D2_CLIENTE), ' - ', SA1.A1_NOME)        AS CLIENTE,
SD2.D2_COD              AS CODPRODUTO,
QEK_REVI                AS REVISÃO,
SD2.D2_LOTECTL          AS LOTE,
SB1.B1_DESC             AS DESCPRODUTO,
SB1.B1_X_ESPEC                                           AS NQUIMICO,
SC2.C2_XLOTEF         AS FORLOTE,
SC2.C2_XNPAIS         AS PAIS,
SC2.C2_XNOMFA         AS FABRICANTE,
CONVERT(VARCHAR(10), CAST(SC2.C2_XDTFAB AS DATE),103)    AS FABRICAÇÃO,
CONVERT(VARCHAR(20), CAST(SC2.C2_XDTVALI AS DATE),103)   AS VALIDADE,
SB1.B1_XDCB           AS DCB,
SB1.B1_XDCI           AS DCI,
SB1.B1_XCAS           AS CAS,
QE6.QE6_XMANUS        AS MANUSEIO,
QE6.QE6_XDERRA        AS DERRAMAMENTO,
QE6.QE6_XTRATA        AS TRATAMENTO,
QEK.QEK_CERQUA        AS NLAUDO
FROM SD2020 AS SD2
INNER JOIN SA1020 AS SA1 WITH(NOLOCK) ON SA1.A1_COD = SD2.D2_CLIENTE
INNER JOIN SB1020 AS SB1 WITH(NOLOCK) ON SB1.B1_COD = SD2.D2_COD
INNER JOIN SC2020 AS SC2 WITH(NOLOCK) ON SC2.C2_XLOTE = SD2.D2_LOTECTL
INNER JOIN QEK020 AS QEK WITH(NOLOCK) ON QEK.QEK_XORDEM = SC2.C2_NUM
INNER JOIN QE6020 AS QE6 WITH(NOLOCK) ON QE6.QE6_PRODUT = QEK.QEK_PRODUT
WHERE SD2.D2_DOC = '244788' AND SD2.D2_COD = '000878'
AND SA1.D_E_L_E_T_ = '' AND SB1.D_E_L_E_T_ = '' AND SD2.D_E_L_E_T_ = '' AND QEK.D_E_L_E_T_ = '' AND QE6.D_E_L_E_T_ = '' ) A 

LEFT JOIN ( 

SELECT
QE1.QE1_DESCPO    AS CARACTERISTICAS,
QE8.QE8_TEXTO     AS ESPECIFICAÇÕES,
QE8.QE8_METODO    AS METODO
FROM QE8020 AS QE8
INNER JOIN QE1010 AS QE1 WITH(NOLOCK) ON QE1.QE1_ENSAIO = QE8.QE8_ENSAIO 
WHERE QE8.QE8_PRODUT = '000878' AND QE8.QE8_REVI = '02' 
ORDER BY QE8_SEQLAB ) B

ON A.COD_PRODUTO = B.COD_PRODUTO AND A.REVISAO = B.REVISAO 

LEFT JOIN ( 

SELECT
QER_LOTE,
QER_REVI,
QEQ.QEQ_MEDICA   AS RESULTADO
FROM QER020 AS QER
INNER JOIN QEQ020 AS QEQ WITH(NOLOCK) ON QEQ.QEQ_CODMED = QER.QER_CHAVE
WHERE QER.QER_LOTE = 'PS-010435/F01' ) C

ON A.QER_LOTE = C.A.QER_LOTE

  • Reginaldo did not understand this last line ON A.QER_LOTE = C.A.QER_LOTE because A is also along with C and that’s because when I put the code in sql it does not recognize this A that is along with C

  • If the QER_LOTE column does not exist in the first SELECT you need to add it.

  • Vlw Reginaldo thank you.

Browser other questions tagged sql sql-server

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