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
