Home » Other » Training & Certification » changing zero into hi?
changing zero into hi? [message #264366] Mon, 03 September 2007 02:22 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi


I have small problem,

Loc is number type and it contain values like
0
1
2
3 in place of 0 user want to see HI.what function i want to use?

[Updated on: Mon, 03 September 2007 02:34]

Report message to a moderator

Re: changing zero into hi? [message #264370 is a reply to message #264366] Mon, 03 September 2007 02:27 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

select decode(loc,0,'HI',loc);
Re: changing zero into hi? [message #264371 is a reply to message #264366] Mon, 03 September 2007 02:30 Go to previous messageGo to next message
hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka
Junior Member


select column1,column2... , case when loc=0 then 3 ... from table_name
Re: changing zero into hi? [message #264373 is a reply to message #264366] Mon, 03 September 2007 02:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
what function i

Hasan he wanted to use function not case one.
Re: changing zero into hi? [message #264374 is a reply to message #264370] Mon, 03 September 2007 02:33 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
i canot understan ur function plz explain with example?
Re: changing zero into hi? [message #264378 is a reply to message #264374] Mon, 03 September 2007 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide.
Don't use IM speak.
Search in the documentation, DECODE is there as well as CASE.

Regards
Michel
Re: changing zero into hi? [message #264379 is a reply to message #264366] Mon, 03 September 2007 02:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> create table loc ( a number);

Table created.

SQL> insert into loc values(1);

1 row created.

SQL> insert into loc values(0);

1 row created.

SQL> commit;

Commit complete.
SQL>  select decode(a,0,'HI',a) from loc;

DECODE(A,0,'HI',A)
----------------------------------------
1
HI


Now satisfy? Laughing
Re: changing zero into hi? [message #264383 is a reply to message #264379] Mon, 03 September 2007 02:44 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
By using decode function it showing error,THE ERROR IS

EXPRESSION TYPE IS NOT COMPATIBLE WITH THE OBJECT TYPE

The function i used

select decode(loc,0,'hi',loc) from tablename
Re: changing zero into hi? [message #264384 is a reply to message #264374] Mon, 03 September 2007 02:47 Go to previous messageGo to next message
hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka
Junior Member

for doing this you do not need to use a function ...

you can use CASE

suppose
table T (cl number)

T

0
1
2
3

to see 0 to HI

select CAASE WHEN cl =0 then 'Hi'
ELSE cl
END from t
Re: changing zero into hi? [message #264385 is a reply to message #264383] Mon, 03 September 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
EXPRESSION TYPE IS NOT COMPATIBLE WITH THE OBJECT TYPE

I wonder why?
Just let us see the formula: "decode(loc,0,'hi',loc)"
In first case your return a string ('hi'), in latter you return a number (loc). Not quite the same.
Conclusion: use TO_CHAR on number.

Regards
Michel

Re: changing zero into hi? [message #264387 is a reply to message #264385] Mon, 03 September 2007 03:05 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
I change type into char and use decode function it's working now.

But it canot working in Number type.
Re: changing zero into hi? [message #264388 is a reply to message #264366] Mon, 03 September 2007 03:06 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
In first case your return a string ('hi'), in latter you return a number (loc). Not quite the same.


I wonder about his error message. Does conversion necessary.Decode should automatically convert it. It works here quite well.


SQL> select decode(a,0,'HI',a) from loc;

DECODE(A,0,'HI',A)
----------------------------------------
1
HI
8


SQL> desc loc;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

Re: changing zero into hi? [message #264391 is a reply to message #264366] Mon, 03 September 2007 03:13 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

@Hasan , What will be your answer in this example .

SQL> select case when a=0 then 'Hi' else a end from loc;
select case when a=0 then 'Hi' else a end from loc
                                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER


so in simple case you can't be able to use case. As in case of case once you return number and another time you return character.

How?
Re: changing zero into hi? [message #264396 is a reply to message #264387] Mon, 03 September 2007 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But it canot working in Number type.

'Hi' is NOT a number.

Regards
Michel
icon12.gif  Re: changing zero into hi? [message #264400 is a reply to message #264396] Mon, 03 September 2007 03:28 Go to previous messageGo to next message
hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka
Junior Member

for avoiding the error message please do it....

SELECT CASE WHEN id = 0 THEN 'Hi' else TO_CHAR(id) end FROM test;


buy i wonder the error is too common that any one can solve it...
I think In this forum every one has the basic knowledge ... so we should give the guide line how to solve the problem not the correct or exact solution... 'hi' ans 1,2,3 are not the same data type we all know it...Smile

[Updated on: Mon, 03 September 2007 03:34]

Report message to a moderator

Re: changing zero into hi? [message #264401 is a reply to message #264366] Mon, 03 September 2007 03:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

HUh!! Laughing
That's why I said in previous,
Quote:
in simple case you can't be able to use case.


But you can use decode.
Re: changing zero into hi? [message #264402 is a reply to message #264366] Mon, 03 September 2007 03:41 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel , my question was about decode one not any other else.

Questioner says that he cannot get expected result, i.e, he got

error. But in my environment everything of decode working fine.No
conversion needed.I experiment over number data type. And questioner failed and then he changed his data type to char.
Re: changing zero into hi? [message #264408 is a reply to message #264402] Mon, 03 September 2007 03:53 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel , my question was about decode one not any other else.

I was not answering to you but anyway this seems to work or not, 'Hi' is not a number, a number is not a string, never rely on an implicit conversion, always use same datatype at same place.

Regards
Michel
Previous Topic: Insert values into a table using function
Next Topic: Interwiev Questions
Goto Forum:
  


Current Time: Thu Mar 28 14:13:31 CDT 2024