Written by

Question Davidson Faria · Jul 22, 2021

Strange query result

#Caché

Can someone tell me why this query

select vet.ID, svp.ID_PACIENTE, vet.conselho, vet.uf, vet.numConselho, vet.nome, svp.SEQ_SOLICITANTE, vet.numConselho , vlab.numConselho
from RPE.Veterinario vet
inner join RPE.VeterinarioLab vlab
    ON vet.conselho = vlab.conselho
    AND vet.numConselho = vlab.numConselho
    AND vet.uf = vlab.uf
left join MySevi.SolicitanteVeterinarioPaciente svp
    on svp.ID_VETERINARIO = vet.ID
where svp.ID_PACIENTE in ('6408||284144||47633', '6408||284145||47634')

does't return any value and this query

select vet.ID, svp.ID_PACIENTE, vet.conselho, vet.uf, vet.numConselho, vet.nome, svp.SEQ_SOLICITANTE, vet.numConselho , vlab.numConselho
from RPE.Veterinario vet
inner join RPE.VeterinarioLab vlab
    ON vet.conselho = vlab.conselho
    AND vet.numConselho = vlab.numConselho
    AND vet.uf = vlab.uf
left join MySevi.SolicitanteVeterinarioPaciente svp
    on svp.ID_VETERINARIO = vet.ID
where svp.ID_PACIENTE in ('6408||284144||47633', '6408||284145||47634') and vet.numConselho > 0

does?

Discussion (4)1

Add reply

Comments

Julie Bolinsky · Jul 22, 2021

You would need to do a Show Plan (button within Management Portal - SQL). This will show you the path that each query is taking, and allow you to troubleshoot what may be the issue.

0 0

Eduard Lebedyuk · Jul 22, 2021

This looks more like a WRC issue, but I'd wager a guess that the first and second query use different indices.

To be more specific the second query does not use some index due to the need to traverse all RPE.Veterinario rows due to the vet.numConselho > 0 condition.

To solve this issue try to rebuild all indices for these 3 tables.

0 0

Yaron Munz · Jul 27, 2021

what is the vet.numConselho for those 2 records ?

0 0

Martin Weissenborn Aug 26, 2021 to Yaron Munz

Try %ignoreindices * perheps you have a problem with the indices
If you get an result you must rebuild the indices.

select vet.ID, svp.ID_PACIENTE, vet.conselho, vet.uf, vet.numConselho, vet.nome, svp.SEQ_SOLICITANTE, vet.numConselho , vlab.numConselho

from %ignoreindices * RPE.Veterinario vet

inner join RPE.VeterinarioLab vlab ON vet.conselho = vlab.conselho
AND vet.numConselho = vlab.numConselho
AND vet.uf = vlab.uf
left join MySevi.SolicitanteVeterinarioPaciente svp on svp.ID_VETERINARIO = vet.ID where svp.ID_PACIENTE in ('6408||284144||47633', '6408||284145||47634')

0 0