You can use this as a model.
CREATE OR REPLACE TYPE integer_table IS TABLE OF INTEGER;
DECLARE
integers integer_table;
BEGIN
SELECT CAST (COLLECT (xx) AS integer_table)
INTO integers
FROM (SELECT 1 xx FROM DUAL
UNION ALL
SELECT 2 xx FROM DUAL);
DBMS_OUTPUT.put_line ('val(1):' || TO_CHAR (integers (1)));
DBMS_OUTPUT.put_line ('val(2):' || TO_CHAR (integers (2)));
END;
The two (select from dual) statements represent your two statements.
Mike
On Wed, Oct 26, 2011 at 4:37 PM, Gayathri <[email protected]> wrote:
> Hi All,
>
> really in need your help!
> How can I combine these 2 queries ? Please let me know asap.
>
> Thanks in advance!
>
>
> --------------------------------------------------------------------------------------------------------------
>
> declare
> v_start_dt date := to_date('12/01/2009', 'mm/dd/yyyy');
> v_end_dt date := to_date(NULL, 'mm/dd/yyyy');
> v_mbr_id number;
> begin
>
>
> SELECT MIN(b.mbr_id) KEEP(DENSE_RANK FIRST ORDER BY b.mbr_dt_start)
> into v_mbr_id
> FROM mbr b
> WHERE b.status_id IN (13, 14)
> AND b.mbr_b_id = 47115
> AND (b.mbr_dt_start != v_start_dt and NVL(b.mbr_dt_end,
> TO_DATE('12/31/9999', 'mm/dd/yyyy')) > v_start_dt and
> NVL(b.mbr_dt_end, TO_DATE('12/31/9999', 'mm/dd/yyyy')) <
> NVL(v_end_dt, TO_DATE('12/31/9999', 'mm/dd/yyyy')));
>
> dbms_output.put_line('v_mbr_id(1): ' || v_mbr_id);
>
> SELECT MAX(b.mbr_id) KEEP(DENSE_RANK FIRST ORDER BY b.mbr_dt_start desc)
> into v_mbr_id
> FROM mbr b
> WHERE b.status_id IN (13, 14)
> AND b.mbr_b_id = 47115
> AND v_end_dt is not null
> and v_end_dt > b.mbr_dt_start;
>
> dbms_output.put_line('v_mbr_id(2): ' || v_mbr_id);
>
> exception
> when others then
> dbms_output.put_line('Error: ' || SQLERRM);
> end;
> /
>
> thanks
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
