I can't really tell what you're looking for exactly, but from what I
gather you need to use something like SQL%ROWCOUNT. When your DML
statements run you can use this to return the count. If it's zero
proceed to the next statement. I hope you're not trying to do this w/o
using an anonymous block because you can't that i know.
Also I noticed you're declaring a variable of type DATE, but are doing
date conversion.
v_start_dt date := to_date('12/01/2009', 'mm/dd/yyyy');
v_end_dt date := to_date(NULL, 'mm/dd/yyyy');
This isn't really necessary and is somewhat redundant. If you've
declared something to be a date you can just assign the date you wish
to use directly...so this would suffice.
v_start_dt date := '12/01/2009';
v_end_dt date := NULL;
I know this isn't your question, but I thought I'd mention it. Not
sure if this helps, but I thought I'd chime in.
On Oct 28, 7:59 am, ddf <[email protected]> wrote:
> On Oct 27, 4:03 pm, Gayathri <[email protected]> wrote:
>
>
>
>
>
>
>
>
>
> > 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
> > <[email protected]>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 <[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-Hide quoted text -
>
> > - Show quoted text -
>
> When you won't state the problem clearly the you don't get answers you
> can use. Your example shows no use of an update statement; post an
> example of what you ARE doing if you want a usable response.
>
> David Fitzjarrell
--
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
