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 ?
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 ?