CODEWITHSUNDEEP
sqlpostgresqlt-sqlouter-apply
user13158095
user13158095

Reputation: 3

Postgre equivalent of MSSQL outer apply

The below code works fine with MSSQL. Any suggestion on how to translate this to Postgre?

;with mySource  as (
  SELECT 1050
LineID, 1 SeqNo, NULL Val
UNION SELECT 1050 LineID, 2
SeqNo, NULL Val
UNION SELECT 1050 LineID, 3
SeqNo, 'ABC' Val
UNION SELECT 1050 LineID, 4
SeqNo, NULL Val
UNION SELECT 1050 LineID, 5
SeqNo, NULL Val
UNION SELECT 1050 LineID, 6
SeqNo, 'CDE' Val
UNION SELECT 1050 LineID, 7
SeqNo, NULL Val
UNION SELECT 1050 LineID, 8
SeqNo, NULL Val
UNION SELECT 1050 LineID, 9
SeqNo, 'EFG' Val
UNION SELECT 1050 LineID, 10
SeqNo, NULL Val
UNION SELECT 2222 LineID, 1
SeqNo, NULL Val
UNION SELECT 2222 LineID, 2
SeqNo, 'ABC' Val
UNION SELECT 2222 LineID, 3
SeqNo, 'CDE' Val
UNION SELECT 2222 LineID, 4
SeqNo, NULL Val
UNION SELECT 2222 LineID, 5
SeqNo, NULL Val
UNION SELECT 2222 LineID, 6
SeqNo, 'EFG' Val
UNION SELECT 2222 LineID, 7
SeqNo, NULL Val
UNION SELECT 2222 LineID, 8
SeqNo, 'HIJ' Val
UNION SELECT 2222 LineID, 9
SeqNo, NULL Val
UNION SELECT 2222 LineID, 10
SeqNo, 'KLM' Val
) 
Select LineID,SeqNo, Coalesce(bu,ba) Val 
from mySource m
outer apply (select top 1 Val 
from mySource m1 
WHERE m1.LineID=m.LineID and m1.SeqNo<=m.SeqNo and Val is not null 
Order by SeqNo DESC) d1(bu) 
outer APPLY (SELECT TOP 1 Val 
FROM mySource m3 
WHERE  m3.LineID=m.LineID and m3.SeqNo>= m.SeqNo AND Val IS NOT NULL 
ORDER  BY SeqNo) d3(ba)
ORDER BY m.LineID, m.SeqNo

Upvotes: 0

Views: 1726

Answers (1)

GMB
GMB

Reputation: 222432

The equivalent for outer apply in Posgres would be left join lateral. You also need to replace TOP 1, which is T-SQL specific, with LIMIT.

It is also possible to shorten the common table expression to use the values() syntax.

with mySource(LineID, SeqNo, Val)  as (values
    (1050, 1, null),
    (1050, 2, null),
    (1050, 3, null),
    ...
    (2222, 10, 'KLM')
)
select LineID, SeqNo, Coalesce(bu,ba) Val 
from mySource m
left join lateral (
    select Val bu 
    from mySource m1 
    where m1.LineID = m.LineID and m1.SeqNo <= m.SeqNo and Val is not null 
    order by SeqNo desc
    limit 1
) d1 on true 
left join lateral (
    select Val ba
    from mySource m3 
    where m3.LineID = m.LineID and m3.SeqNo >= m.SeqNo AND Val is not null 
    order  by SeqNo
    limit 1
) d3 on true
order by m.LineID, m.SeqNo

Looking at the query, I tend to suspect that its logic could be largely simplified with window functions (lag() and lead() come to mind). You might want to ask another question with more details on what you are trying to accomplish, along with sample data and expected results.

Upvotes: 1

Related Questions