View with double SELECT from the same table

0

I need to mount a VIEW with three fields, but one depends on the other to be calculated (two of them are from the same table in different lines I need to "link" them), how do I add those fields in one place? The select's are as follows:

SELECT Referencia FROM ZSF053 WHERE TpFat = 'ZREK'

SELECT Doc_orig FROM ZFI003 WHERE Referencia = (utiliza as referencias do select acima)

SELECT Referencia FROM ZFI003 WHERE Num_doc = (utiliza os Doc_orig do select acima)
    
asked by anonymous 23.04.2018 / 18:08

2 answers

5

From what I understand about pergutna, you can test the query as below:

SELECT ZS.Referencia, ZF1.Doc_orig, ZF2.Referencia
FROM ZSF053 ZS
JOIN ZFI003 ZF1 ON ZS.Referencia = ZF1.Referencia
JOIN ZFI003 ZF2 ON ZF1.Doc_orig = ZF2.Doc_orig
WHERE ZS.TpFat = 'ZREK'

As commented in the question by Quesado , you should take a look at join .

    
23.04.2018 / 18:13
2

The @rLInhares solution handles this scenario well, just to cite as a second alternative if JOIN is not possible, you can also nest SELECT . Taking your example, it would look like this:

SELECT Referencia 
  FROM ZFI003 
 WHERE Num_doc IN (SELECT Doc_orig 
                     FROM ZFI003 
                    WHERE Referencia IN (SELECT Referencia 
                                           FROM ZSF053 
                                          WHERE TpFat = 'ZREK'))

In this case above, I used IN instead of = in case the query returned more than one record, but could also limit using TOP eg:

SELECT Referencia 
  FROM ZFI003 
 WHERE Num_doc = (SELECT TOP 1 Doc_orig 
                     FROM ZFI003 
                    WHERE Referencia IN (SELECT TOP 1 Referencia 
                                           FROM ZSF053 
                                          WHERE TpFat = 'ZREK'))

Just as note if someone needs another way to solve

    
23.04.2018 / 18:24