select comparison with EXISTS

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 24 Jan 2008 7:39 am

Hello guys I have a problem that should be easy for you guys.

SELECT a type that brings a number of queries in a veterinarian and tests these queries with the exists but he's bringing a lot of stuff that you're not supposed to bring, like:
Code: Select all
SELECT a.campo1, a.campo2 ,a.campo3
                   
FROM   tabela 1
                                     
WHERE  a.DTATENDIMENTO BETWEEN '&DATA_INI' AND '&DATA_FIM'


AND a.CDSERVICO IN ('123','456')

and a.CDveterinario = &CDveterinario

AND EXISTS(SELECT * FROM guia_de_atendimento gg, servico_de_atendimento aa--(AQUI ELE TESTA A 1º CONDIÇÃO QUE É AS CONSULTAS 123)--
           WHERE aa.cdserie = gg.cdserie   
             AND gg.dtatendimento BETWEEN '&DATA_INI' AND '&DATA_FIM'
             AND aa.cdservico = '123')
AND EXISTS(SELECT * FROM guia_de_atendimento gg, servico_de_atendimento aa--(AQUI ELE TESTA A 1º CONDIÇÃO QUE É AS CONSULTAS 456)--
           WHERE aa.cdserie = gg.cdserie   
             AND gg.dtatendimento BETWEEN '&DATA_INI' AND '&DATA_FIM'
             AND aa.cdservico = '456')
ORDER BY 1
with that will be checked if you had those two conditions that animal care and the select have to bring only those queries that meets these two condition, only the select all bringing também queries that meets only one of the conditions, how I improve this select to bring only the serving exists?


valeu Folks
carlos12
Location: rio

Poston Thu, 24 Jan 2008 8:09 am

Brother, take a look at this code:
Code: Select all
SELECT a.campo1, a.campo2 ,a.campo3
                   
FROM   tabela1 a 
                                     
WHERE  a.DTATENDIMENTO BETWEEN '&DATA_INI' AND '&DATA_FIM'


AND a.CDSERVICO IN ('123','456')

and a.CDveterinario = &CDveterinario

AND EXISTS(SELECT 1
             FROM guia_de_atendimento gg, servico_de_atendimento aa--(AQUI ELE TESTA A 1º CONDIÇÃO QUE É AS CONSULTAS 123 e 456)--
           WHERE aa.cdserie = gg.cdserie   
             AND gg.dtatendimento BETWEEN '&DATA_INI' AND '&DATA_FIM'
             AND aa.cdservico in ('123','456')   
             AND aa.codigo1 = a.codigo1)-- associar aqui, como este exemplo
             -- Falta associar uma destas tabelas internas (guia_de_atendimento) ou (servico_de_atendimento)
             -- com  a query principal (Tabela 1)
             
ORDER BY 1
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Thu, 24 Jan 2008 11:58 am

Man briadão for the tip touched a few things but it worked.
brigadão comrade.
carlos12
Location: rio

Poston Thu, 24 Jan 2008 12:04 pm

beleza brother.
Need, we're there.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 10 guests