Update with a value the record in LEFT JOIN

Question

Update with a value the record in LEFT JOIN

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

Good morning ... I have this query

select  Numeros.n ,coalesce(Numeros.IdPaciente, datos.IdPaciente) IdPaciente,  FechaAnalitica,GlucosaPre,Sodio,Calcio from Numeros 
left join
(
    select ROW_NUMBER () over (order by IdPaciente asc) Row#,
    IdPaciente,FechaAnalitica,GlucosaPre,Sodio,Calcio
    from ANA_BIOQUIMICA where IdPaciente = 2450
)datos
on Numeros.n = datos.Row#

this returns the following result:

But I need, that the column "Patient Id" does not return null, but returns the same value as the records above ...

How can I achieve this?

Thank you for sharing your experience ..

sql-server

asked by Emerson Rios 01.09.2017 в 20:54

source

3 answers

Consuming SOAP with jquery Problem with Chrome download icon

score 0 · Answer 1

You can support yourself with a variable, assuming that IdPaciente is a INT :

Declare variable DECLARE @IdPaciente INT
Assigning the patient's Id to look for that variable SET @IdPaciente = 2450
Validate that if the value of the query is null, put the value of the variable ISNULL(coalesce(Numeros.IdPaciente, datos.IdPaciente), @IdPaciente)
In the conditions put the equality but with the variable WHERE IdPaciente = @IdPaciente

Finally, the following would be:

DECLARE @IdPaciente INT
SET @IdPaciente = 2450

SELECT Numeros.n
    ,ISNULL(coalesce(Numeros.IdPaciente, datos.IdPaciente), @IdPaciente) AS IdPaciente
    ,FechaAnalitica
    ,GlucosaPre
    ,Sodio
    ,Calcio
FROM Numeros
LEFT JOIN (
    SELECT ROW_NUMBER() OVER (
            ORDER BY IdPaciente ASC
            ) Row#
        ,IdPaciente
        ,FechaAnalitica
        ,GlucosaPre
        ,Sodio
        ,Calcio
    FROM ANA_BIOQUIMICA
    WHERE IdPaciente = @IdPaciente
    ) datos ON Numeros.n = datos.Row#

score 0 · Answer 2

Due to the limitations of your comments, use a sub-query with top 1 as 3rd option to coalesce :

select  Numeros.n ,
    coalesce(Numeros.IdPaciente, datos.IdPaciente,( select top 1 IdPaciente from Numeros where IdPaciente is not null ) ) IdPaciente,  
    FechaAnalitica,GlucosaPre,Sodio,Calcio from Numeros 
    left join
    (
    select ROW_NUMBER () over (order by IdPaciente asc) Row#,
    IdPaciente,FechaAnalitica,GlucosaPre,Sodio,Calcio
    from ANA_BIOQUIMICA where IdPaciente = 2450
    )datos
    on Numeros.n = datos.Row#

score 0 · Answer 3

Thanks for the answers, they are functional, in the end choose to use the LEAD SQL function.

select IdPaciente, FechaAnalitica,GlucosaPre,Sodio,Calcio, lead(IdPaciente,1,0) over (Order by IdPaciente ) as Paciente from ( select datos.IdPaciente IdPaciente, FechaAnalitica, GlucosaPre, Sodio, Calcio from Numeros left join ( select ROW_NUMBER ()
over (order by IdPaciente asc) RowN, IdPaciente,FechaAnalitica,GlucosaPre,Sodio,Calcio from ANA_BIOQUIMICA where IdPaciente = 2450 )datos on Numeros.n = datos.RowN )d

this generates just what I needed.