3
I have the following question below when making the query in Oracle 10G.
I have the table med_item_avaliar
that has in its structure items that reference other items within itself (Parents and Children).
When making the simple query is quiet, the above items are returned, however, when trying to make the query linking the item to be evaluated to the Results table, in that of Results I am not able to bring the item Father, only the son by the fact that his bond is giving for the children.
The query below refers to the table in question:
select
t.nr_sequencia nr_sequencia_tipo,
t.ds_tipo ds_tipo,
i.ds_item ds_item,
i.nr_seq_tipo,
i.nr_sequencia nr_sequencia_item, --aqui fica o item
i.nr_seq_superior --aqui fica a posição acima
from med_tipo_avaliacao t,
med_item_avaliar i
where t.nr_sequencia = i.nr_seq_tipo
and t.nr_sequencia = 2281;
Below is the survey I’m trying to do looking for the items that were answered. The question is that the parents of these items are missing.
--select dos itens
FROM
med_avaliacao_paciente a, -- aqui o cabeçalho da avaliação no atendimento
med_avaliacao_result b, -- aqui o resultado da avaliação, liga-se com o cabeçalho
pessoa_fisica c, -- aqui o cadastro da PF pra pegar o nome
med_tipo_avaliacao d, -- aqui fica o tipo de avaliação
med_item_avaliar e -- aqui o item do tipo
WHERE
a.nr_sequencia = b.nr_seq_avaliacao
AND d.nr_sequencia = e.nr_seq_tipo --ligar o tipo de avaliação ao item
AND d.nr_sequencia = a.nr_seq_tipo_avaliacao -- ligar o tipo de avalização ao tipo de atendimento
AND b.nr_seq_item = e.nr_sequencia -- ligar o item da pesquisa ao item respondido
AND b.nr_seq_avaliacao = 1377920
AND d.nr_sequencia = 2281
AND a.nr_atendimento = 2147535;
This way I’m just taking the items that were answered, but I don’t know which is the Father of this item, only the item answered that varies between: Adequate, Inadequate, as the image below:
How to bring also the Father of the children in this consultation and who really corresponds to the children?
Oracle, as I recall, allows you to declare recursive Ctes. CTE is that DML clause before the Insert, update, delete or select that begins with
with
– Jefferson Quesado
This was made for Sqlite, but I think it can be used almost the same with Oracle: https://answall.com/a/214626/64969
– Jefferson Quesado
Recursive in ORACLE , use CONNECT BY
– Motta
You need to use a
WITH
as @Jeffersonquesado had commented– Sorack
@Motta I’d love to see an example, I don’t know this building.
– Jefferson Quesado
https://www.devmedia.com.br/uso-do-connect-by-no-oracle/23647
– Motta