Skip to Content
0
Former Member
May 08, 2009 at 09:06 AM

Help: SQL query when parsed returns Invalid Identifier error

423 Views

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 this is parsed by the crystal sql expression editer

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 on May 8, 2009 11:06 AM

Edited by: vipulbhatia29 on May 8, 2009 11:14 AM