Thread: Please help me regarding the WITH RECURSIVE query
Hello All,
I am migrating oracle queries to postgres queries
Oracle query is below
select * from (select * from KM_COURSE_MAST where ID in (select OBJ_ID from (select OBJ_ID,PERFORMER_TYPE,PERFORMER_ID from KM_REL_OBJ_PER_ACTION where OBJ_TYPE='COURSETYPE') where PERFORMER_TYPE='GROUP' and PERFORMER_ID in (select PARENT_ID from KM_REL_SELF_GROUP start with CHILD_ID in ( SELECT GROUP_ID FROM KM_REL_GRP_USER WHERE USER_ID=52247) connect by CHILD_ID= prior PARENT_ID union SELECT GROUP_ID PARENT_ID FROM KM_REL_GRP_USER WHERE USER_ID=52247)) union select * from KM_COURSE_MAST where CREATED_BY=52247) order by DISPLAYORDER
We have changed to postgres query like below
select * from (select * from KM_COURSE_MAST where ID in (select OBJ_ID from (select OBJ_ID,PERFORMER_TYPE,PERFORMER_ID from KM_REL_OBJ_PER_ACTION where
OBJ_TYPE='COURSETYPE') g where PERFORMER_TYPE='GROUP' and PERFORMER_ID in (WITH RECURSIVE parents as ( select PARENT_ID from KM_REL_SELF_GROUP where CHILD_ID in ( SELECT
GROUP_ID FROM KM_REL_GRP_USER WHERE USER_ID=52247) UNION select a.PARENT_ID FROM KM_REL_SELF_GROUP a ,parents p where a.CHILD_ID = p.PARENT_ID ) select PARENT_ID from parents order by
PARENT_ID asc)) union select * from KM_COURSE_MAST where CREATED_BY='52247') KM_COURSE_MAST where ID =214
Above postgres query will work fine if resultset has multiple tuples but returns empty if result set has single row.
Again i have changed above query like below
select * from (select * from KM_COURSE_MAST where ID in (select OBJ_ID from (select OBJ_ID,PERFORMER_TYPE,PERFORMER_ID from KM_REL_OBJ_PER_ACTION where
OBJ_TYPE='COURSETYPE') g where PERFORMER_TYPE='GROUP' and PERFORMER_ID in (WITH RECURSIVE parents as ( select PARENT_ID from KM_REL_SELF_GROUP where CHILD_ID in ( SELECT
GROUP_ID FROM KM_REL_GRP_USER WHERE USER_ID=52247) UNION select a.PARENT_ID FROM KM_REL_SELF_GROUP a ,KM_REL_SELF_GROUP p where a.CHILD_ID = p.PARENT_ID ) select PARENT_ID from parents order by
PARENT_ID asc)) union select * from KM_COURSE_MAST where CREATED_BY='52247') KM_COURSE_MAST where ID =214
It returns resultset with single row
Please explain me why it is ?
Thanks,
Gajendra
On Mon, Aug 26, 2013 at 3:17 AM, gajendra s v <svgajendra@gmail.com> wrote:
Please explain me why it is ?
A good place to start would be removing all the parts here that don't seem to matter. Your problem seems to be with the recursive query (since that is the part you're changing). Cut off everything else and compare the results of the recursive queries, and if you still can't figure it out, come back here with your findings (and the isolated recursive queries). You'll be much more likely to get responses if you narrow down the problem you're having instead of asking people on this list to do it.
You might find this a good read: http://sscce.org/. And it wouldn't hurt if you could create a SQL Fiddle that demonstrates your problem; the simpler and more trimmed down the better.