Thanks Mike,
But these are part of Update statement.
something like this:
Update mbr_id = (Query 1)
where exists (Query 1)
which sometime would return with no updates
Then I do:
Update mbr_id = (Query 2)
where exists (Query 2)
On Wed, Oct 26, 2011 at 5:19 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
> 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 <gd0...@gmail.com> 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 Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> 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 Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
