Invalid identifier during select SQL - Oracle

Asked

Viewed 1,012 times

0

I am trying to seek information according to the business rule, which in case is ID and CPF of the Client of a certain City and this client in question can not have Debito active, but the ways I am trying is returning the error message quoted in the title.

Database is not exactly my forte and so I would like to help you to identify where I’m missing and why, I will post two selects, the first works (but brings incomplete information) and the other two are my attempts to get the die the way I need it.

Query that works:

SELECT CLIENTE.ID,CLIENTE.CPF 
FROM CLIENTE 
INNER JOIN CIDADE ON CIDADE.ID = CLIENTE.IDCIDADE 
WHERE NOT EXISTS( SELECT * 
                  FROM LIGACAO 
                  WHERE CLIENTE.ID =  LIGACAO.IDCLIENTE) 
AND CLIENTE.CPF IS NOT NULL 
AND CIDADE.CIDADE = 'CIDADE1' 
AND ROWNUM = 1

Query that does not work:

SELECT CLIENTE.ID,CLIENTE.CPF 
FROM CLIENTE 
INNER JOIN CIDADE ON CIDADE.ID = CLIENTE.IDCIDADE 
WHERE NOT EXISTS (SELECT * 
                  FROM LIGACAO 
                  WHERE CLIENTE.ID =  LIGACAO.IDCLIENTE) 
AND EXISTS (SELECT * 
            FROM DEBITO 
            WHERE LIGACAO.ID = DEBITO.IDLIGACAO 
            AND DEBITO.ATIVO = 'N') 
AND CLIENTE.CPF IS NOT NULL 
AND CIDADE.CIDADE = 'CIDADE1' 
AND ROWNUM = 1

The part that fails is the.ID LINK when compared to DEBITO.IDLINK, these fields are compatible, because DEBITO.IDLINK is fed precisely with LINK.ID during a registration, so the error probably comes from my comparison in this section, I have tried to add DEBIT to the INNER JOIN, but ON just in the LINK part.ID = DEBIT.IDLINK.

This link ID is the PK LINK, so it exists, in the city table the id is used in comparison without any problem in a very similar case.

I’m sorry if it became too extensive or redundant, I wanted to give as much information as possible and I apologize in advance if I missed something, I didn’t want to leave so extensive the post.

sql oracle

  • in the condition WHERE LINK.ID = DEBITO.IDLINK if you make a connection with an un-called table in the subselect , without knowing the model becomes difficult to opine , as your tables relate ? DEBITO connects to CUSTOMER ?

  • The LINK table does not exist in the most external SELECT. In the query that worked you use a table that is in the external SELECT (CLIENT).

  • Guys, thank you so much. The solution I found was to find the previous select that already called the LINK table and inside it I put the LINK.ID attribute to compare the DEBIT select result

  • Hello @Thiagotolentino, welcome to Sopt, do not change the title to indicate that the problem has been solved, I see that you found a solution and already published as a response, but later you can mark it as an accepted answer. = D -- don’t forget to stop by the [Tour] site.

  • I get it, you can let me take a look there.

1 answer

0


The solution I found guided by the comments that were made was:

SELECT CLIENTE.ID,CLIENTE.CPF FROM CLIENTE INNER JOIN CIDADE ON CIDADE.ID = CLIENTE.IDCIDADE WHERE 
NOT EXISTS(SELECT * FROM LIGACAO WHERE CLIENTE.ID =  LIGACAO.IDCLIENTE AND LIGACAO.id in(SELECT DEBITO.idLigacao from DEBITO where DEBITO.ATIVO ='S')) AND CLIENTE.CPF IS NOT NULL AND CIDADE.CIDADE = 'SAO DOMINGOS DO ARAGUAI' AND ROWNUM = 1

The problem was really this, using a table without calling it explicitly (in the case of CLIENT.ID it was called "implicit" within the select outside), I solved this using the LINK table where it had already been called.

Thank you all, problem solved.

Browser other questions tagged sql oracle

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