SQL using Join with subquery

Asked

Viewed 341 times

1

I have the following code:

select FACAV_CDMATCOOP,CCCCO_NOCOOPERAD,FACLA_ORIDAVIAR
FACLA_QTPESOLOTE, FACLA_QTENTREG1,
FACAV_ORCDAVIAR, FACLA_NRLOTE, 
FACLA_DTYALOJA, FATCR_QTPESOMED
from FACAV F WITH(nolock) 

INNER JOIN  CCCCO WITH(nolock)  ON
CCCCO_CDEMPRESA = FACAV_CDEMPRESA AND
CCCCO_CDMATCOOP = FACAV_CDMATCOOP 

-- INNER JOIN FACLA WITH(NOLOCK) ON
-- FACLA_CDEMPRESA = FACAV_CDEMPRESA AND
-- FACLA_ORCDAVIAR = FACAV_ORCDAVIAR AND
-- FACLA_IDSITUACAO = 'A' 
INNER JOIN(
 SELECT TOP 1 *
 FROM FACLA WHERE 
 FACLA_CDEMPRESA = F.FACAV_CDEMPRESA AND -- da erro The multi-part identifier "F.FACAV_CDEMPRESA" could not be bound.
 FACLA_ORCDAVIAR = F.FACAV_ORCDAVIAR AND 
 FACLA_IDSITUACAO = 'A'
 ORDER BY FACLA_DTYALOJA,  FACLA_NRLOTE DESC
) FACLA ON 
FACLA_CDEMPRESA = FACAV_CDEMPRESA AND
FACLA_ORCDAVIAR = FACAV_ORCDAVIAR 

INNER JOIN FATCR WITH(NOLOCK) ON
FATCR_CDEMPRESA = FACAV_CDEMPRESA AND
FATCR_IDSEXOAVE = FACLA_IDSEXOAVE AND
FATCR_IDCLIMA = FACLA_IDCLIMA

where 
FACAV_CDEMPRESA = 'CT' AND
FACAV_IDSITUACAO <> 'I'
ORDER BY FACAV_CDMATCOOP ASC

In the table FACLA I have to pick the first record but ordering. I’m trying this way but this giving error.

  • 1

    What is the error message?

  • I believe that the FACAV_CDEMPRESA column belongs to the FACAV table that is outside the scope of its subquery. I’ve tried putting: FACLA_CDEMPRESA = F.FACAV_CDEMPRESA ?

  • @Christianoribeirosoares is giving another error, I edited the question and put the new error.

No answers

Browser other questions tagged

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