OraFAQ Forum: SQL & PL/SQL » ORA-00905, ORA-06550

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00905, ORA-06550 (PL/SQL Developer)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00905, ORA-06550 [message #593238]

Thu, 15 August 2013 04:24

kristin_86
Messages: 9
Registered: May 2013

Junior Member

Hi everybody,

I have a problem with the code below. I would like to define a variable (exrate1) which I would like to use in another script. Also, the variable should be different for every month(that's why timestamp is between some period). The problem is that I receive some errors which I don't know haw to fix. Can you please help me

/forum/fa/11066/0/

Attachment: error.png
(Size: 32.49KB, Downloaded 1382 times)

Report message to a moderator

Re: ORA-00905, ORA-06550 [message #593240 is a reply to message #593238]

Thu, 15 August 2013 04:30

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Code should always be posted as text in [code] tags not as an image.
Case statements return values. exrate1 = <whatever> is a Boolean expression not a value that can be returned.
Each THEN clause should just have the number you want to return, nothing else.

Report message to a moderator

Re: ORA-00905, ORA-06550 [message #593253 is a reply to message #593240]

Thu, 15 August 2013 05:28

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Remove "end as ..."

From your previous topic:

Michel Cadot wrote on Thu, 30 May 2013 13:05

Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

...

Always use SQL*Plus and copy and paste your session, in text, inline and formatted.

Regards
Michel

Report message to a moderator

Re: ORA-00905, ORA-06550 [message #593259 is a reply to message #593240]

Thu, 15 August 2013 05:40

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

cookiemonster wrote on Thu, 15 August 2013 10:30

Case statements return values. exrate1 = <whatever> is a Boolean expression not a value that can be returned.

Minor nit pick. CASE statements allow you to perform actions. CASE expressions return values. Case statements are not valid in SQL, whereas Case expressions are.
The OP appears to have mixed the two together.

[Updated on: Thu, 15 August 2013 05:41]

Report message to a moderator

Re: ORA-00905, ORA-06550 [message #593593 is a reply to message #593259]

Mon, 19 August 2013 02:16

kristin_86
Messages: 9
Registered: May 2013

Junior Member

Hi,
I corrected the code in the way which you said:

DECLARE 
  v_exarate1 number;
BEGIN

  select  t.name_table_op_name
  into    v_exarate1
  from    all_tables1 t
  where   v_exarate1 IN 
       (select  
    case when timestamp between '20130101000000' and '20130131235959' 
      then 1.16771
    when timestamp between '20130201000000' and '20130228235959' 
      then  1.15306
    when timestamp between '20130301000000' and '20130331235959' 
      then  1.14979 
   else 0 end v_exarate1
         from   tapmach 
       ); 
   
end;
/

However,it shows me another error:
ORA - 01403:no data found
ORA - 06512: at line 5

What can be the problem with 'into v_exarate1'?

Report message to a moderator

Re: ORA-00905, ORA-06550 [message #593598 is a reply to message #593593]

Mon, 19 August 2013 02:31

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As "v_exarate1" is not initialized, "v_exarate1 IN ..." is never true and so the query returns nothing.

Once again:

Quote:

Always use SQL*Plus and copy and paste your session, in text, inline and formatted.

Regards
Michel

Report message to a moderator

Re: ORA-00905, ORA-06550 [message #593599 is a reply to message #593593]

Mon, 19 August 2013 02:32

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

where v_exarate1 IN
should be a column in your table not the variable.

Report message to a moderator

Re: ORA-00905, ORA-06550 [message #593600 is a reply to message #593593]

Mon, 19 August 2013 02:32

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

The error means your query returned no rows. The code compiled and ran as coded, but no data was returned.

Although the way your code is written I seriously doubt it's going to achieve your functional requirements.

Also, you're passing a string into a timestamp field, don't do that, convert the literal (or better yet, dont use a literal).

Report message to a moderator

Previous Topic:	Query to find number of NULLs for each record.
Next Topic:	How to match two words matching from two table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 27 23:01:34 CDT 2025