Hi The expression posted below is the actual SQL Expression which is required in my report:
((select name from (
select loc_id,name,row_number()over( order by r) rn from (
SELECT 0, loc_id, Misc1_txt NAME,'A' STATUS ,rownum r
FROM dvxloc
WHERE loc_id = "XXXLOC"."LOC_ID"
union
SELECT parent_loc_id, loc_id, (SELECT a.Misc1_txt
FROM dvxloc a
WHERE a.loc_id =b.loc_id) NAME,'B' ,ROWNUM
FROM dvxlocpath b
START WITH b.loc_id = "XXXLOC"."LOC_ID"
CONNECT BY PRIOR parent_loc_id=loc_id
) where name is NOT NULL order by STATUS, R
) where rn = 1))
It gives an error while parsing: "XXXLOC"."LOC_ID" invalid identifier
LOC_ID is of numeric data type in the database. and when I run thi query in SQL editor after replacing "XXXLOC"."LOC_ID" with a numeric value say 456 . The query can be put as
((select name from (
select loc_id,name,row_number()over( order by r) rn from (
SELECT 0, loc_id, Misc1_txt NAME,'A' STATUS ,rownum r
FROM dvxloc
WHERE loc_id = 456 union
SELECT parent_loc_id, loc_id, (SELECT a.Misc1_txt
FROM dvxloc a
WHERE a.loc_id =b.loc_id) NAME,'B' ,ROWNUM
FROM dvxlocpath b
START WITH b.loc_id = 456 CONNECT BY PRIOR parent_loc_id=loc_id
) where name is NOT NULL order by STATUS, R
) where rn = 1))
The above query runs flawlessly in a SQL editor and also it is parsed in Crystal SQL Expression editor without any errors.
I'm totally messed up of looking for altenatives. I have to deliver a report to the client and stuck on this part only.
The below query parses perfectly in crystal:
(select locname
from dvxloc where loc_id in(select loc3 from dvxlocpath
where loc_id = "XXXLOC"."LOC_ID"))
Edited by vipulbhatia29 - 08 May 2009 at 2:17am
IP Logged
