May be this will 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 <[email protected]> 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 <[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-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 [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
