Migration of Oracle SYS_CONNECT_BY_PATH to SQL Ser

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Migration of Oracle SYS_CONNECT_BY_PATH to SQL Ser

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-01 : 07:10:28

SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ', '),', ') catvalues
into v_nomineeinfo
FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
FROM (
--SELECT NOMINEE_ID myvalues
--FROM TBL_RNR_NOMINEES
select myvalues
from
(
select
(name_complete || '(' || eid || ')') myvalues
from HAIL_ESS_USERS_EXTENDED_V
where personid in (
select NOMINEE_ID
from TBL_RNR_NOMINEES
where NOMINATIONID = p_nominationID
)
)
)
) data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn-1;

for that following is the SQL Server code ..

;with cte1 as (
select (isnull(NameComplete, '') + '(' + isnull(EID, '') + ')') MyValues,
1 as level,
row_number()over (order by MyValues)rn,
count(*)cnt
from custom.dbo.viwSSAppsEmpMasterExtended vem
where PersonId in (select NomineeId
from dev_common.dbo.tblRNR_Nominees
where NominationId = @p_NominationId )
), cte as (
select cast(MyValues as varchar(max))MyValues, level, rn, cnt
from cte1
where rn = 1
union all
select cte1.MyValues, cte.level+1, cte1.rn, cte1.cnt, cte.MyValues + ',' + cast(cte1.MyValues as varchar(max))MyValues
from cte1 inner join cte on cte1.rn-1=cte.rn
)
select @v_NomineeInfo = MyValues
from cte
where rn = cnt
group by MyValues

...... here getting an error like

Msg 207, Level 16, State 1, Procedure uspRNR_Notify, Line 61
Invalid column name 'MyValues'.

please somebody help me

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-01 : 08:56:54

One problem is that in 'cte' you have 4 items in the first select list and 5 in the second. Unions must have the same number of items in all the unioned queries. There's another problem: you have the same column name MyValues twice in the recursive part of the cte but try to group on it in the main query. SQL will find that ambiguous.

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-01 : 23:11:34

;with cte1 as (
select (isnull(NameComplete, '') + '(' + isnull(EID, '') + ')') MyValues,
1 as level,
row_number()over (order by MyValues)rn,
count(*)cnt
from custom.dbo.viwSSAppsEmpMasterExtended vem
where PersonId in (select NomineeId
from dev_common.dbo.tblRNR_Nominees
where NominationId = @p_NominationId )
), cte as (
select cast(MyValues as varchar(max))MyValues, level, rn, cnt
from cte1
where rn = 1
union all
select cte.MyValues + ',' + cast(cte1.MyValues as varchar(max))MyValues, cte.level+1, cte1.rn, cte1.cnt
from cte1 inner join cte on cte1.rn-1=cte.rn
)
select @v_NomineeInfo = MyValues
from cte
where rn = cnt
group by MyValues

i changed but error is still there in the following row.............

row_number()over (order by MyValues)rn,

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-01 : 23:12:08

error is ....

Msg 207, Level 16, State 1, Procedure uspRNR_Notify, Line 61
Invalid column name 'MyValues'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 08:56:58

Is MyValues a column in the custom.dbo.viwSSAppsEmpMasterExtended table?

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-12-02 : 23:11:45

No it is just an Alias

row_number()over (order by MyValues)rn

in this row it showing an Error