Carry the last result

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

Poston Mon, 29 Aug 2016 11:00 am

Good day guys, I'm starting to work with SQL and I have a problem I need to always bring the last record from a table in the following SQL
Code: Select all
select      substr(obter_idade_pf(c.cd_pessoa_fisica, sysdate, 'A'),1,100)qt_idade,   
            substr(obter_nome_paciente_setor(a.nr_seq_paciente),1,60) nm_paciente,
            substr(obter_nome_medico_setor(a.nr_seq_paciente),1,60) nm_medico,
            substr(obter_desc_prot_medic(a.nr_seq_paciente),1,255) ds_protoc, 
            obter_checagem_quimioterapia(a.nr_seq_atendimento)status,               
            substr(obter_desc_cid(d.CD_CID),1,200)ds_doenca,
            a.qt_peso             
from     paciente_atendimento a,
           atendimento_paciente c,
           [b]uop_cid_paciente d[/b]
           where   dt_prevista  between  :dt_inicial and fim_dia(:dt_final)
   and  d.cd_pessoa_fisica = c.cd_pessoa_fisica
   and       exists (select 1
               from paciente_setor b
               where b.nr_seq_paciente = a.nr_seq_paciente
               and b.ie_status = 'A')                       
and          a.dt_cancelamento is null
and          a.cd_estabelecimento = :cd_estabelecimento
and          a.nr_atendimento = c.nr_atendimento
order by     nm_paciente
I need you to bring only and always the first record of this table in bold
Dualogic

Poston Mon, 29 Aug 2016 2:29 pm

Good afternoon, In uop_cid_paciente table you have a field that can be used as a parameter?

for example: And d. dt_incl = (Select max (dt_incl) from uop_cid_paciente where cd_pessoa_fisica = d.cd _ pessoa_fisica) is more or less what you want?
spernega
Location: São Paulo - SP

Poston Mon, 29 Aug 2016 5:36 pm

This, exactly, just do not understand this part dt_incl d., what parameter I have to spend?
Dualogic

Poston Tue, 30 Aug 2016 7:46 am

Hi, in the example, if the uop_cid_paciente table has more than one record for each patient and you want to carry the last record of him, you could filter by the date of inclusion of the registry.
The subquery will bring the last record released based on the date of incorporation or other single field that exists in the table.

That is, you will search only the queries for the last entry of the patient.

Now a strange " " answer: If you just want the select returns only one row, you can add one more clause to the query:--to bring the first record that is: where rownum < = 1 and:d t_inicial and dt_prevista between fim_dia (t_final:d) and pessoa_fisica = _ c.cd _ d.cd pessoa_fisica and exists (select 1 from paciente_setor b where b.nr = a.nr _ seq_paciente _ seq_paciente and b.ie _ status = ' A ') and a. dt_cancelamento is null
and a.cd _ = cd_estabelecimento and a.nr establishment: _ attendance = c.nr _ order by nm_paciente service to bring the last record you would put an order by field desc But I don't think that's what you need.

Anyway, if it does, please explain in more detail what you need.
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 9:13 am

Hello, I improved and a drying " " in my code and was thus, select substr (obter_idade_pf (c.cd _ pessoa_fisica, sysdate, ' A ') .1 .100) qt_idade, substr (obter_nome_paciente_setor (a.nr _ seq_paciente) .1 .60) nm_paciente, substr (obter_nome_medico_setor (a.nr _ seq_paciente) .1 .60) nm_medico, substr (obter_desc_prot_medic (a.nr _ seq_paciente) .1 .255) ds_protoc , substr (obter_desc_cid (d.CD _ CID) .1 .200) ds_doenca, a. paciente_atendimento, qt_peso from atendimento_paciente c, uop_cid_paciente d where dt_prevista between t_inicial and:d. fim_dia (t_final:d) and d. dt_diagnostico = (select max (dt_diagnostico) from uop_cid_paciente where pessoa_fisica = _ c.cd _ d.cd pessoa_fisica) and a. dt_cancelamento is null and a.cd =: cd_estabelecimento and establishment _ a.nr _ customer service = c.nr _ and _ pessoa_fisica d.cd service = c.cd _ pessoa_fisica order by nm_paciente But even informing the query parameters comes out blank, and I don't know what else to do to solve this problem:(
Dualogic

Poston Tue, 30 Aug 2016 9:21 am

You generate a script with the creation of tables and loads?
then I try to play here.
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 9:28 am

You want a script of my Bank?
Dualogic

Poston Tue, 30 Aug 2016 9:32 am

All of the tables involved in the query.
With the inserts, if not very big +
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 9:51 am

I say again, I'm starting with SQL and know little of the tools to do this, how do I generate this script in SQL Navigator 6.1
Dualogic

Poston Tue, 30 Aug 2016 9:58 am

I don't use SQL Navigator, I'm old school, just use the sqlplus:?

Must have options to generate DDL for tables and INSERTS, at least in sqldeveloper has.
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 9:58 am

One of the tables this query has 136008 records
Dualogic

Poston Tue, 30 Aug 2016 9:59 am

It's a lot, if you can, just send the same scripts
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 12:46 pm

OPA!

Try it like this:
Code: Select all
select
   substr(obter_idade_pf(c.cd_pessoa_fisica,sysdate,'A'),1,100) qt_idade,
   substr(obter_nome_paciente_setor(a.nr_seq_paciente),1,60) nm_paciente,
   substr(obter_nome_medico_setor(a.nr_seq_paciente),1,60) nm_medico,
   substr(obter_desc_prot_medic(a.nr_seq_paciente),1,255) ds_protoc,
   substr(obter_desc_cid(d.CD_CID),1,200)mds_doenca,
   a.qt_peso 
from
   paciente_atendimento a,
   atendimento_paciente c,
   uop_cid_paciente d 
where
   a.dt_prevista between :dt_inicial and fim_dia (:dt_final) 
   and d.dt_diagnostico = (
      select
         max(ultimo.dt_diagnostico)
      from
         uop_cid_paciente ultimo
      where
         d.cd_pessoa_fisica = ultimo.cd_pessoa_fisica
   ) 
   and a.dt_cancelamento is null 
   and a.cd_estabelecimento = :cd_estabelecimento 
   and a.nr_atendimento = c.nr_atendimento 
   and d.cd_pessoa_fisica = c.cd_pessoa_fisica
geovani
Location: Joinville-SC

Geovani Cristofolini
Consultor de Business intelligence
http://useweknow.com



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests