How to combine results from another query in refcursor resultset?

Question

The below query will retrieve more than one col_grp_id

SELECT   col_grp_id    
  FROM  co_page_col_grp_user_t 
  WHERE  page_id=p_page_id AND security_userid = p_user;

p_check is a user defined variable which should be either string "true" or "false".

It is "false" when the value is present in the first query else it should be "true" . I need to pass all the rows by using refcursor with p_check added in it

OPEN p_out_col_pref FOR
      SELECT page_id,col_grp_nm,col_grp_id,p_check 
      FROM (SELECT p1.page_id, p2.col_grp_nm, p2.col_grp_id, p_check
            FROM co_page_t p1,
             co_page_col_grp_t p2     
           WHERE p1.page_id = p2.page_id
             AND p1.page_nm = p_rptname
             AND p1.appl_cd = p_applcd
             AND p1.page_id = p_page_id);

How to do this query??

This question is pretty much like your previous question [How to return a boolen in refcursor?](http://stackoverflow.com/questions/15243026/how-to-return-a-boolen-in-refcursor), how can we help you further ? — A.B.Cade, Mar 07 '13 at 07:29
I modified the question. It is not about boolean ..I need string only.Its about query logic — Mani, Mar 07 '13 at 07:37
@Satheesh:so it is string then why do u try with ref_cursor?'ve try with collections? — Thiyagu ATR, Mar 07 '13 at 07:39
refucursor i need to pass to the front end developers. I am asking how to set p_check="false" when the values are present in the first query else p_check="true" — Mani, Mar 07 '13 at 07:41

A.B.Cade · Accepted Answer · 2013-03-07T08:30:07.163

You can do it in the query (without using a user defined variable) as in this simplified example:

select p1.*, case when p2.col_grp_id is null then 'true' else 'false' end p_check
from co_page_t p1 left outer join co_page_col_grp_user_t p2
on p1.col_grp_id = p2.col_grp_id

Basically I used a left outer join with your first query and a case to refer nulls as "false" and values as "true"

Or like this:

select p1.*, case when 
(select count(*) from co_page_col_grp_user_t p2 where p1.col_grp_id = p2.col_grp_id ) = 0 then 'true' else 'false'
end p_check
from co_page_t p1;

Here I counted them instead (no need for a distinct)

Here is a sqlfiddle example

How to combine results from another query in refcursor resultset?

1 Answers1