Home » SQL & PL/SQL » SQL & PL/SQL » Values b/w 0 and 1 needs to be decoded
Values b/w 0 and 1 needs to be decoded [message #416620] Mon, 03 August 2009 09:45 Go to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi Friends,

I have a requirement of converting values of a column between
0 and 1 to 1.

The other values needs to remain same.

Looking for your esteemed help

Thanks In Advance!

Ramya Nomula
Re: Values b/w 0 and 1 needs to be decoded [message #416621 is a reply to message #416620] Mon, 03 August 2009 09:47 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Look up the syntax for the CASE expression
Re: Values b/w 0 and 1 needs to be decoded [message #416622 is a reply to message #416620] Mon, 03 August 2009 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
UPDATE TAB1 SET COL1 = 1 WHERE COL1 >= 0 AND COL1 <= 1
Re: Values b/w 0 and 1 needs to be decoded [message #416623 is a reply to message #416621] Mon, 03 August 2009 09:57 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi Pablolee,

I tried the following case expresson and its not working -

select (case net_weight WHEN 0.% then 1
else net_weight
end) from SO_PRICE_ADJUSTMENTS;

Example of my requirement -

Existing Column Values Required Column Values
0.23 1
1.23 1.23
2.45 2.45
0.78 1.00
0.56 1.00
0.41 1.00
0.96 1.00



Thanks & Regards
Dilip Nomula
Re: Values b/w 0 and 1 needs to be decoded [message #416624 is a reply to message #416623] Mon, 03 August 2009 09:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reread your college notes / study book before going any further.
Focus on the section "Datatypes"

[Edit: On second thought, reread them all]

[Updated on: Mon, 03 August 2009 10:01]

Report message to a moderator

Re: Values b/w 0 and 1 needs to be decoded [message #416625 is a reply to message #416622] Mon, 03 August 2009 10:00 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi Blackswan,

I dont want to update in the table,but i only want to query the result and use it for reports.

Thanks for your immediate reply.


Thanks & Regards
Ramya Nomula
Re: Values b/w 0 and 1 needs to be decoded [message #416628 is a reply to message #416625] Mon, 03 August 2009 10:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Or, if you don't have notes / a study book start here.
Re: Values b/w 0 and 1 needs to be decoded [message #416632 is a reply to message #416628] Mon, 03 August 2009 10:31 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi ThomasG,

Thanks for your guidance.

My question is not related to datatypes,i dont know why you giving links related to DataTypes.

Thanks & Regards
Ramya Nomula
Re: Values b/w 0 and 1 needs to be decoded [message #416633 is a reply to message #416632] Mon, 03 August 2009 10:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Because you treat a number datatype like a character datatype in your query.
Re: Values b/w 0 and 1 needs to be decoded [message #416634 is a reply to message #416633] Mon, 03 August 2009 10:37 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
ThomasG wrote on Mon, 03 August 2009 16:32
Because you treat a number datatype like a character datatype in your query.


Sort of, the actual syntax isn't valid even for chars.
Re: Values b/w 0 and 1 needs to be decoded [message #416635 is a reply to message #416633] Mon, 03 August 2009 10:41 Go to previous messageGo to next message
dilipn_nitw@yahoo.com
Messages: 16
Registered: July 2009
Location: India
Junior Member

Hi ThomasG,

For explaining my question better,i have mentioned as 0.%,my intention is to convert all the values b/n 0 and 1.

Hope you understands.

Thanks & Regards
Ramya Nomula
Re: Values b/w 0 and 1 needs to be decoded [message #416636 is a reply to message #416620] Mon, 03 August 2009 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions040.htm#sthref1270
Re: Values b/w 0 and 1 needs to be decoded [message #416637 is a reply to message #416620] Mon, 03 August 2009 10:45 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
And you can't work out how to code a between?
There's one way of doing it already posted in this thread.
Re: Values b/w 0 and 1 needs to be decoded [message #416686 is a reply to message #416620] Mon, 03 August 2009 23:36 Go to previous messageGo to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
SELECT CASE net_weight 
       WHEN net_weight < 1.00 THEN 1
       ELSE net_weight
       END 
FROM SO_PRICE_ADJUSTMENTS;


(assuming the column is a numeric data type)

Migth work this way, not sure.. coz right now I dont have access to DB to check... am a novice.. Smile

[Updated on: Mon, 03 August 2009 23:39]

Report message to a moderator

Re: Values b/w 0 and 1 needs to be decoded [message #416688 is a reply to message #416686] Tue, 04 August 2009 00:08 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
@Neo06
You provide erroneous code

SQL> create table tbl
  2  (net_weight number(5,2));

Table created.

SQL> insert into tbl values(0.5);

1 row created.

SQL> insert into tbl values(1.5);

1 row created.

SQL> SELECT CASE net_weight 
  2         WHEN net_weight < 1.00 THEN 1
  3         ELSE net_weight
  4         END 
  5  FROM tbl;
       WHEN net_weight < 1.00 THEN 1
                       *
ERROR at line 2:
ORA-00905: missing keyword


regards,
Delna
Re: Values b/w 0 and 1 needs to be decoded [message #416689 is a reply to message #416688] Tue, 04 August 2009 00:10 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Here is the correct one.
SQL> ed
Wrote file afiedt.buf

  1  SELECT CASE
  2         WHEN net_weight < 1.00 THEN 1
  3         ELSE net_weight
  4         END
  5* FROM tbl
SQL> /

CASEWHENNET_WEIGHT<1.00THEN1ELSENET_WEIGHTEND
---------------------------------------------
                                            1
                                          1.5


regards,
Delna
Re: Values b/w 0 and 1 needs to be decoded [message #416690 is a reply to message #416686] Tue, 04 August 2009 00:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The provided code in the last few post all forget to take into account negative numbers. The Orignal Poster clearly stated that only numbers between 0 and 1 need to be converted.
That is NOT the same as all numbers < 1
Re: Values b/w 0 and 1 needs to be decoded [message #416691 is a reply to message #416690] Tue, 04 August 2009 00:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Shocked
Yes Frank sir...

regards,
Delna
Re: Values b/w 0 and 1 needs to be decoded [message #416701 is a reply to message #416620] Tue, 04 August 2009 00:46 Go to previous messageGo to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
@Delna
Thanks for correcting me..

@Frank
Oops...may be from next time, I need to concentrate on details...

anyway I correted the code.. hope this should be fine.. using Delnas code..


SELECT CASE  
           WHEN net_weight  > 0.00 and net_weight < 1.00 THEN 1
           ELSE net_weight
           END ,net_weight
    FROM tbl;
Re: Values b/w 0 and 1 needs to be decoded [message #416702 is a reply to message #416701] Tue, 04 August 2009 00:48 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Couple of minor things neo, neither of them actual problems as far as execution goes:
No need for the extra zeros:
WHEN net_weight  > 0 and net_weight < 1 THEN 1

add an alias nto expression columns, just to keep things tidy.

Re: Values b/w 0 and 1 needs to be decoded [message #416703 is a reply to message #416701] Tue, 04 August 2009 00:54 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Agree with pablolee sir

Quote:
I have a requirement of converting values of a column between
0 and 1 to 1.


Quote:
select (case net_weight WHEN 0.% then 1


And to convert 0 into 1, condition should be like

WHEN net_weight  >= 0 and net_weight < 1 THEN 1


regards,
Delna
Previous Topic: converting ibatis sql code to pl/sql
Next Topic: Getting the error 'ORA-01732' while removing Duplicates
Goto Forum:
  


Current Time: Sun Dec 11 00:41:57 CST 2016

Total time taken to generate the page: 0.11126 seconds