Home » SQL & PL/SQL » SQL & PL/SQL » the ZERO didn't appear ..... why ?
icon5.gif   the ZERO didn't appear ..... why ? [message #303718] Sun, 02 March 2008 05:30 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

SQL> declare
2 vv char(2):=lower('&vv');
3 nn varchar2(11);
4 begin
5 nn:=
6 case vv
7 when 'a' then 11
8 when 'b' then 22
9 when 'c' then 33
10 else (to_char(012))
11 end;
12 dbms_output.put_line ('when '||vv||' then '||nn);
13 end;
14 /
Enter value for vv: k
old 2: vv char(2):=lower('&vv');
new 2: vv char(2):=lower('k');
when k then 12

PL/SQL procedure successfully completed.


I want to appear the ZERO in previous code ...... which means I want the result be like that :-
when k then 012

waiting for answer and thanks
Re: the ZERO didn't appear ..... why ? [message #303719 is a reply to message #303718] Sun, 02 March 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want the string '012' then directly use '012'.
What you wrote is "convert to a string the number 12" which is by default the string '12'.

Regards
Michel
Re: the ZERO didn't appear ..... why ? [message #303727 is a reply to message #303719] Sun, 02 March 2008 07:26 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  vv char(2):=lower('&vv');
  3  nn varchar2(11);
  4   begin
  5   nn:=
  6   case vv
  7   when 'a' then 11
  8   when 'b' then 22
  9   when 'c' then 33
 10   else '012'
 11   end;
 12   dbms_output.put_line ('when '||vv||' then '||nn);
 13*  end;
SQL> /
Enter value for vv: k
old   2: vv char(2):=lower('&vv');
new   2: vv char(2):=lower('k');
when k  then 12

PL/SQL procedure successfully completed.


still not working Sad
Re: the ZERO didn't appear ..... why ? [message #303732 is a reply to message #303727] Sun, 02 March 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The type of the values returned by CASE if the one of the FIRST value.
Your first value is 11 which is a number so your '012' is converted to the number 12 before it is returned to you and so you are in the same case as your preceding code.
Try to put a non numeric string instead of '012' and you'll get an error.

Regards
Michel
Re: the ZERO didn't appear ..... why ? [message #303736 is a reply to message #303718] Sun, 02 March 2008 09:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
this is an excellent example of why IMPLICIT DATATYPE CONVERSION is a bad thing.

to understand what Michel is telling you, cut your select statement out of your PL/SQL code and run it directly in SQL. Here, I will do it for you.

SELECT CASE 'k' 
         WHEN 'a' THEN 11
         WHEN 'b' THEN 22
         WHEN 'c' THEN 33
         ELSE (To_char(012))
       END SomeValue
FROM   Dual
/

I want you to do three things:

Quote:
1) run this select in SQLPLUS and observe what happens
2) explain to us what happened
3) render to us your opinion about it

By the way, this is something you should always be doing (running your SQL code independently of your PL/SQL code). It is a basic development, testing, and tuning practice.

Additionally, these three steps are crucial to becomming a great Oracle developer, especially #3. You will have to do #1 and #2 just to survive. But if you want to become a really good Oracle person, then you have to develope your own opinions. Do you like what you see or not? What kinds of problems could it create for you? How can you "cheat" with it, to turn it to an advantage? Developing your own opinions forces you to observe and think. Most people don't do enough of either.

Waiting for the results of your run... Kevin.

[Updated on: Sun, 02 March 2008 09:20]

Report message to a moderator

icon14.gif  Re: the ZERO didn't appear ..... why ? [message #303746 is a reply to message #303736] Sun, 02 March 2008 11:45 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

finally ..... I got what i want Very Happy

have a look :-

SQL> SELECT CASE 'k'
  2           WHEN 'a' THEN 11
  3           WHEN 'b' THEN 22
  4           WHEN 'c' THEN 33
  5           ELSE (To_char(012))
  6         END SomeValue
  7  FROM   Dual
  8  /
         ELSE (To_char(012))
               *
ERROR at line 5:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR


SQL> ed
Wrote file afiedt.buf

  1  SELECT CASE 'k'
  2           WHEN 'a' THEN 11
  3           WHEN 'b' THEN 22
  4           WHEN 'c' THEN 33
  5           ELSE 012
  6         END Value
  7* FROM   Dual
SQL> /

     VALUE
----------
        12

SQL> ed
Wrote file afiedt.buf

  1  SELECT CASE 'k'
  2           WHEN 'a' THEN 11
  3           WHEN 'b' THEN 22
  4           WHEN 'c' THEN 33
  5           ELSE '012'
  6         END Value
  7* FROM   Dual
SQL> /
         ELSE '012'
              *
ERROR at line 5:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR


SQL> ed
Wrote file afiedt.buf

  1  SELECT CASE 'k'
  2           WHEN 'a' THEN '11'
  3           WHEN 'b' THEN '22'
  4           WHEN 'c' THEN '33'
  5           ELSE '012'
  6         END Value
  7* FROM   Dual
SQL> /

VAL
---
012


OK ..... I see now the whole idea
after THEN I decided if all be number or all be char
and now this is the right code :-

SQL> declare
  2   vv char(2):=lower('&vv');
  3   nn varchar2(11);
  4   begin
  5   nn:=
  6   case vv
  7   when 'a' then '11'
  8   when 'b' then '22'
  9   when 'c' then '33'
 10   else '012'
 11   end;
 12   dbms_output.put_line ('when '||vv||' then '||nn);
 13   end;
 14   /
Enter value for vv: i
old   2:  vv char(2):=lower('&vv');
new   2:  vv char(2):=lower('i');
when i  then 012

PL/SQL procedure successfully completed.


thanks for you all ...... and have a nice day
Re: the ZERO didn't appear ..... why ? [message #303756 is a reply to message #303718] Sun, 02 March 2008 12:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Not so fast my friend!

Quote:
1) run this select in SQLPLUS and observe what happens
2) explain to us what happened
3) render to us your opinion about it


I specifically asked for the above three items.

You did an excellent job with #1 and #2. I am glad that you are happy, but if you expect help from this forum in the future, then we are waiting on #3.

What else do you have to say about all of this?

Indeed, there is a specific thing you should be pointing out.

now GIVE!

Kevin

[Updated on: Sun, 02 March 2008 12:58]

Report message to a moderator

Re: the ZERO didn't appear ..... why ? [message #304593 is a reply to message #303718] Wed, 05 March 2008 15:17 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Hany, are you awake? Why have you not replied with the additional information I requested? In case you are unsure about what to offer, let me give you some help...

You ran your original select statement in SQLPLUS and you got a runtime error.

You ran your original select statement in PL/SQL and you did NOT get an error. Instead PL/SQL decided to give you an answer. An answer which upon examination you deemed incorrect.

Do you have no opinion you wish to express about this? Does it no worry you?

Kevin
Previous Topic: ORA-01848 error on view query
Next Topic: Adding a Partition to an existing partioned table
Goto Forum:
  


Current Time: Sun Dec 04 16:51:32 CST 2016

Total time taken to generate the page: 0.07224 seconds