Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Error
PL/SQL Error [message #346637] Tue, 09 September 2008 03:55 Go to next message
ravisudireddy
Messages: 2
Registered: September 2008
Location: Singapore
Junior Member
Hi Friends


I want to include Select statment in 'IF' Condition as mentioned below. While Compiling System is givin giving error.

as i am new to PL SQL .Could you please help me

Coding:

select substr(a.CHARG,2,10) FROM ZODSS1B2 a ,utaccpsc_psc_deduction b
where a.kunag='PW1001'
and a.zrunindx='10'
OR substr(a.CHARG,2,10)= b.custlotid
and b.status in ('DELETED','COMPLETED')and (wday = 'MON' and substr(v_timestamp,9,6) = '070000') or
v_timestamp= v_monthend)


then
update zodss1b2 set zrunindx = seq_ZODSS1B2.nextval

where zrunindx = 10
and kunag in (
select sap_custcode
-- from t_cust_config
from ZSDSHR05
where active_customer = 'Y'
and billing_schedule = 'W'
and custcode='PW1'); '
end if;


Thanks in Advance

Ravi
Re: PL/SQL Error [message #346641 is a reply to message #346637] Tue, 09 September 2008 04:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't work out what you're asking for.
Are you looking to do a Select and then conditionally do an Update, or do you want a single statement that Selects and possibly updates as well.

If it's the latter, then you're out of luck.
Re: PL/SQL Error [message #346642 is a reply to message #346637] Tue, 09 September 2008 04:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also:

Error? What error? I don't see any error.

Read the Forum Guide and post the required information.
Re: PL/SQL Error [message #346646 is a reply to message #346641] Tue, 09 September 2008 04:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is how I understood the question: Ravi tries to do something like this:
IF (select sal 
    from emp
    where ename = 'KING' 
   ) = 200
THEN
   update some_table set
     this_column = 'blah blah'
   where some_condition;
END IF;

If that's so, well, you'll first have to SELECT into a variable, and then use this variable in the IF construct:
declare
  l_variable emp.sal%type;
begin
  select sal into l_variable
    from emp
    where ename = 'KING';
 
  if l_variable = 200 then
     update some_table set ...
  end if;
end;

Re: PL/SQL Error [message #346648 is a reply to message #346646] Tue, 09 September 2008 04:21 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
Quote:


IF (select sal
from emp
where ename = 'KING'
) = 200






if the above returns multiple values what will happen with update statement

yours
dr.s.raghunathan


Re: PL/SQL Error [message #346649 is a reply to message #346646] Tue, 09 September 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And it is better (if possible) to include the condition inside the update statement as the condition may be switch from true to false between select and update:
update some_table set ...
where exists select null from emp where ename='KING and sal=200

Regards
Michel
Re: PL/SQL Error [message #346659 is a reply to message #346641] Tue, 09 September 2008 04:43 Go to previous message
ravisudireddy
Messages: 2
Registered: September 2008
Location: Singapore
Junior Member
Hi Friend

Thanks reply

I want to update (update zodss1b2 set zrunindx = seq_ZODSS1B2.nextval) based on below conditions


where a.kunag='PW1001'
and a.zrunindx='10'
OR substr(a.CHARG,2,10)= b.custlotid
and b.status in ('DELETED','COMPLETED')and (wday = 'MON' and substr(v_timestamp,9,6) = '070000') or
v_timestamp= v_monthend)


Regards
Ravikanth
Previous Topic: how many insert triggers can a table have?
Next Topic: Updating columns of one table based on other table
Goto Forum:
  


Current Time: Sat Dec 03 03:44:53 CST 2016

Total time taken to generate the page: 0.05180 seconds