Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help in converting oracle to Teradata query

Status
Not open for further replies.

swag123

Programmer
May 25, 2010
1
IN
We are migrating from oracle to teradata.We are using the below query in Oracle environment:

select max(level) from ${CBG_REPOS_SCHEMA}.t_cbgdw_invlv_party_x_invlv_pa
start with OBJ_INVLV_PARTY_ID=:invlv_party_id
CONNECT BY PRIOR obj_invlv_party_id = subj_invlv_party_id

We converted it like this :

WITH RECURSIVE RECINVLV(obj_invlv_party_id, subj_invlv_party_id, LEVEL)
AS
(
select obj_invlv_party_id, subj_invlv_party_id,1
FROM ${CBG_REPOS_SCHEMA}.t_invlv_party_x_invlv_pa
where OBJ_INVLV_PARTY_ID = =:invlv_party_id
UNION ALL

select A.obj_invlv_party_id, A.subj_invlv_party_id,B.LEVEL+1
FROM ${CBG_REPOS_SCHEMA}.t_invlv_party_x_invlv_pa A INNER JOIN RECINVLV B
ON A.subj_invlv_party_id = B.obj_invlv_party_id

)

SELECT max( level ) as posn
FROM RECINVLV

But we are getting error like " A SQL command cannot contain both host variables and parameter tokens. "

Could anyone give any solution for this ?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top