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
