Thanks a lot, quite a lot of option that works.
Appreciate it!
On Wed, Nov 9, 2011 at 3:28 AM, sonty <saurabh.zen...@gmail.com> wrote:
> This might help
>
> create table dummy_temp(col1 number, col2 varchar2(30));
>
> insert into dummy_temp (col1) values (1);
> insert into dummy_temp (col1) values (2);
> insert into dummy_temp (col1) values (3);
> insert into dummy_temp (col1) values (4);
>
> select * from dummy_temp;
> col1 col2
> ----- ----
> 1
> 2
> 3
> 4
>
> update dummy_temp x
> set x.col2 = (select case when 3>x.col1 then 'great' else 'less' end
> from dual)
>
> select * from dummy_temp;
> col1 col2
> ----- ----
> 1 great
> 2 great
> 3 less
> 4 less
>
> On Nov 1, 12:08 pm, choc101 <choc...@gmail.com> wrote:
> > 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 <orat...@msn.com> wrote:
> >
> >
> >
> >
> >
> >
> >
> > > On Oct 27, 4:03 pm, Gayathri <gd0...@gmail.com> 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 <
> 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-Hidequoted 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 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
