OraFAQ Forum: Training & Certification » changing zero into hi?

Home » Other » Training & Certification » changing zero into hi?

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

changing zero into hi? [message #264366]

Mon, 03 September 2007 02:22

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

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

select decode(loc,0,'HI',loc);

Report message to a moderator

Re: changing zero into hi? [message #264371 is a reply to message #264366]

Mon, 03 September 2007 02:30

hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka

Junior Member

select column1,column2... , case when loc=0 then 3 ... from table_name

Report message to a moderator

Re: changing zero into hi? [message #264373 is a reply to message #264366]

Mon, 03 September 2007 02:31

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.

Report message to a moderator

Re: changing zero into hi? [message #264374 is a reply to message #264370]

Mon, 03 September 2007 02:33

tondapi
Messages: 99
Registered: August 2007
Location: usa

Member

i canot understan ur function plz explain with example?

Report message to a moderator

Re: changing zero into hi? [message #264378 is a reply to message #264374]

Mon, 03 September 2007 02:37

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

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

Report message to a moderator

Re: changing zero into hi? [message #264379 is a reply to message #264366]

Mon, 03 September 2007 02:37

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

Report message to a moderator

Re: changing zero into hi? [message #264383 is a reply to message #264379]

Mon, 03 September 2007 02:44

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

Report message to a moderator

Re: changing zero into hi? [message #264384 is a reply to message #264374]

Mon, 03 September 2007 02:47

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

Report message to a moderator

Re: changing zero into hi? [message #264385 is a reply to message #264383]

Mon, 03 September 2007 02:50

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

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

Report message to a moderator

Re: changing zero into hi? [message #264387 is a reply to message #264385]

Mon, 03 September 2007 03:05

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.

Report message to a moderator

Re: changing zero into hi? [message #264388 is a reply to message #264366]

Mon, 03 September 2007 03:06

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

Report message to a moderator

Re: changing zero into hi? [message #264391 is a reply to message #264366]

Mon, 03 September 2007 03:13

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?

Report message to a moderator

Re: changing zero into hi? [message #264396 is a reply to message #264387]

Mon, 03 September 2007 03:21

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

Senior Member
Account Moderator

Quote:

But it canot working in Number type.

'Hi' is NOT a number.

Regards
Michel

Report message to a moderator

Re: changing zero into hi? [message #264400 is a reply to message #264396]

Mon, 03 September 2007 03:28

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

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

HUh!!

That's why I said in previous,

Quote:

in simple case you can't be able to use case.

But you can use decode.

Report message to a moderator

Re: changing zero into hi? [message #264402 is a reply to message #264366]

Mon, 03 September 2007 03:41

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.

Report message to a moderator

Re: changing zero into hi? [message #264408 is a reply to message #264402]

Mon, 03 September 2007 03:53

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

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

Report message to a moderator

Previous Topic:	Insert values into a table using function
Next Topic:	Interwiev Questions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 18:55:47 CST 2024