James James - 5 months ago 18
SQL Question

Converting DB2 Function to SQL Server Function

I am in the process of converting a bunch of function from DB2 to SQL server and this one has got me stuck, I've converted the function from this:

create function prf_t_excinter_nzw(AC_ID_i int, TFH_i double) specific prf_t_excinter_nzw
RETURNS TABLE(ft_id int,rg_id int,thr double,value double )
R1: BEGIN ATOMIC
RETURN select l.ft_id, l.rg_id, r.thr, case when l.from is null
then to_value else ( thr - from ) / ( to - from ) * ( to_value - from_value ) + from_value end as value
from ( select ft_id, rg_id, to, from, to_value, from_value from ( select row_number() over (
partition by l.ft_id, l.rg_id, l.thr order by l.ft_id, l.rg_id, l.thr, r.thr desc) as #,
l.ft_id, l.rg_id, l.thr as to, r.thr as from, l.value as to_value, r.value as from_value
from (select ft_id, rg_id, thr, value from table ( prf_t_ftexc_nzw( AC_ID_i, TFH_i)) as d
order by ft_id, rg_id, thr asc ) as l
left outer join ( select ft_id, rg_id, thr, value from table ( prf_t_ftexc_nzw( AC_ID_i, TFH_i)) as d order by ft_id, rg_id, thr asc) as r
on l.ft_id = r.ft_id and l.rg_id = r.rg_id and l.thr > r.thr ) as l_r where # = 1 ) as l join ( select prm_id, thr from prf_t_prm_1d_thr as r
where prm_id in ( select id from prf_t_prm where name like '%RG_NZW') ) as r on l.rg_id = r.prm_id and( l.from < r.thr or l.from is null) and ( r.thr <= l.to )
order by l.ft_id, l.rg_id, r.thr ; END


to this:

create function prf_t_excinter_nzw(@AC_ID_i int,@TFH_i float)
RETURNS TABLE
RETURN select l.ft_id, l.rg_id, r.thr,case when l.from_value is null then to_value else ( thr-from_value )/(to_value-from_value)*(to_value-from_value)+from_value end as value
from (
select ft_id,rg_id,to_value,from_value,to_value,from_value
from (
select row_number() over ( partition by l.ft_id, l.rg_id, l.thr order by l.ft_id,l.rg_id, l.thr, r.thr desc) as num, l.ft_id, l.rg_id, l.to_value, r.from_value
from (
select ft_id, rg_id, thr, ac_value as to_value
from prf_t_ftexc_nzw(@AC_ID_i,@TFH_i) as d) as l
left outer join (
select ft_id, rg_id, thr, ac_value as from_value
from prf_t_ftexc_nzw(@AC_ID_i,@TFH_i) as d) as r
on l.ft_id = r.ft_id
and l.rg_id=r.rg_id
and l.thr > r.thr) as l_r
where num = 1) as l
join (
select prm_id, thr from prf_t_prm_1d_thr as r
where prm_id in (select id from prf_t_prm where name like '%RG_NZW')) as r
on l.rg_id=r.prm_id and (l.from_value<r.thr or l.from_value is null)
and (r.thr<=l.to_value)


At this point i am getting the error Msg 8156, Level 16, State 1, Procedure prf_t_excinter_nzw, Line 18
The column 'to_value' was specified multiple times for 'l'
any tips/suggestions to fix?

Answer

The 5th line is definitely generating an error:

select ft_id,rg_id,to_value,from_value,to_value,from_value 

You have to_value and from_value listed twice in the SELECT list. This would be fine if not a subquery, but since it's a subquery you need unique names for each field, and in this case they're redundant so the duplicates just need to be removed.

The error message points you to the l object which is the alias you've given the subquery that starts with the offending SELECT line mentioned above.

These issues become easier to ferret out if you don't re-use aliases for different objects in a query, since there are a few things labeled l here.

Comments