Home » SQL & PL/SQL » SQL & PL/SQL » updating based on multiple condition
updating based on multiple condition [message #411911] Tue, 07 July 2009 05:17 Go to next message
VIPINMADAAN
Messages: 7
Registered: July 2009
Junior Member
Hi,
I am using oracle 10g. I need to build a query which should work like this .

Shipment table
Shipment_gid
Actual_Image
weight_utilization

I have to use multiple conditions :

if the weight_utilization<10 then Actual_Image should have the value as utilization_0,

if weight_utilization=10 and weight_utilization <20 then actual_image should have value = utilization_10

if weight_utilization=20 and weight_utilization <30 then actual_image should have value = utilization_20

if weight_utilization=30 and weight_utilization <40 then actual_image should have value = utilization_30


if weight_utilization=40 and weight_utilization <50 then actual_image should have value = utilization_40

and so on...till weight_utilization = 100

I was trying to do it using decode function but I am not able to get it properly.

my query was like this:

update shipment
set actual_image=
DECODE(UTILIZATION_0,
DECODE(SIGN(WEIGHT_UTILIZATION-90),UTILIZATION_100,
DECODE(SIGN(WEIGHT_UTILIZATION-80),UTILIZATION_90,
DECODE(SIGN(WEIGHT_UTILIZATION-70),UTILIZATION_
---
I am very new bie to sql. So just explain me whether the above approach is correct or not?

regards,
Vipin



Re: updating based on multiple condition [message #411920 is a reply to message #411911] Tue, 07 July 2009 05:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps CASE (instead of DECODE) would make query maintenance easier.
Re: updating based on multiple condition [message #411923 is a reply to message #411911] Tue, 07 July 2009 05:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
this:
if weight_utilization=10 and weight_utilization <20 then actual_image should have value = utilization_10

is logically equivalent to this:
if weight_utilization=10 then actual_image should have value = utilization_10


Suspect you mean:
if weight_utilization between 11 and 20 then actual_image should have value = utilization_10


As Littlefoot suggests, use case, it'll be a lot easier.
Re: updating based on multiple condition [message #411954 is a reply to message #411923] Tue, 07 July 2009 06:07 Go to previous messageGo to next message
VIPINMADAAN
Messages: 7
Registered: July 2009
Junior Member
Hi,

I am trying my query like this but this is ending in error. Please suggest what can be the issue:

Update shipment
set actual_image =
case
when weight_utilization <10 then utilization_0;
when (weight_utilization=10 && weight_utilization <20) then utilization_10;
when (weight_utilization=20 && weight_utilization <30) then utilization_20;
when (weight_utilization=30 && weight_utilization <40) then utilization_30;
when (weight_utilization=40 && weight_utilization <50) then utilization_40;
when (weight_utilization=90 && weight_utilization <100) then utilization_90;
when weight_utilization=100 then utilization_100;
where shipment_gid= test.01367

Regards,
Vipin
Re: updating based on multiple condition [message #411955 is a reply to message #411954] Tue, 07 July 2009 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: updating based on multiple condition [message #411973 is a reply to message #411954] Tue, 07 July 2009 06:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want to remove the semi-colons from your CASE, and add an END after the last WHEN clause
Re: updating based on multiple condition [message #411978 is a reply to message #411911] Tue, 07 July 2009 06:36 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Ofcourse it will end up in errors, and we cannot test it because we dont have DDL/DML for it.

UPDATE shipment 
SET    actual_image = CASE 
                        WHEN weight_utilization < 10 THEN 'utilization_0' 
                        WHEN (weight_utilization = 10 
                              AND weight_utilization < 20) THEN 'utilization_10' 
                        WHEN (weight_utilization = 20 
                              AND weight_utilization < 30) THEN 'utilization_20' 
                        WHEN (weight_utilization = 30 
                              AND weight_utilization < 40) THEN 'utilization_30' 
                        WHEN (weight_utilization = 40 
                              AND weight_utilization < 50) THEN 'utilization_40' 
                        WHEN (weight_utilization = 90 
                              AND weight_utilization < 100) THEN 'utilization_90' 
                        WHEN weight_utilization = 100 THEN 'utilization_100' 
                      END 
WHERE  shipment_gid = 'test.01367' 
/ 


Compare your query with the above..atleast it doesn't have Syntactical errors ( that believe how did u designed the table)
Re: updating based on multiple condition [message #411996 is a reply to message #411923] Tue, 07 July 2009 07:11 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 07 July 2009 11:30
this:
if weight_utilization=10 and weight_utilization <20 then actual_image should have value = utilization_10

is logically equivalent to this:
if weight_utilization=10 then actual_image should have value = utilization_10


Suspect you mean:
if weight_utilization between 11 and 20 then actual_image should have value = utilization_10




Re: updating based on multiple condition [message #412001 is a reply to message #411996] Tue, 07 July 2009 07:19 Go to previous messageGo to next message
VIPINMADAAN
Messages: 7
Registered: July 2009
Junior Member
cookiemonster wrote on Tue, 07 July 2009 07:11
cookiemonster wrote on Tue, 07 July 2009 11:30
this:
if weight_utilization=10 and weight_utilization <20 then actual_image should have value = utilization_10

is logically equivalent to this:
if weight_utilization=10 then actual_image should have value = utilization_10


Suspect you mean:
if weight_utilization between 11 and 20 then actual_image should have value = utilization_10







not really, as my condition reads as

if weight_utilization >=10 and weight_utilization <20
then throw the value as utilization_10
Re: updating based on multiple condition [message #412003 is a reply to message #411978] Tue, 07 July 2009 07:21 Go to previous messageGo to next message
VIPINMADAAN
Messages: 7
Registered: July 2009
Junior Member
ashoka_bl wrote on Tue, 07 July 2009 06:36
Ofcourse it will end up in errors, and we cannot test it because we dont have DDL/DML for it.

UPDATE shipment 
SET    actual_image = CASE 
                        WHEN weight_utilization < 10 THEN 'utilization_0' 
                        WHEN (weight_utilization = 10 
                              AND weight_utilization < 20) THEN 'utilization_10' 
                        WHEN (weight_utilization = 20 
                              AND weight_utilization < 30) THEN 'utilization_20' 
                        WHEN (weight_utilization = 30 
                              AND weight_utilization < 40) THEN 'utilization_30' 
                        WHEN (weight_utilization = 40 
                              AND weight_utilization < 50) THEN 'utilization_40' 
                        WHEN (weight_utilization = 90 
                              AND weight_utilization < 100) THEN 'utilization_90' 
                        WHEN weight_utilization = 100 THEN 'utilization_100' 
                      END 
WHERE  shipment_gid = 'test.01367' 
/ 


Compare your query with the above..atleast it doesn't have Syntactical errors ( that believe how did u designed the table)


Hi ,

With the help of above query, it does not end up in any kind of error - however the column still does not get updated. I am trying to solve this.
Re: updating based on multiple condition [message #412010 is a reply to message #411911] Tue, 07 July 2009 07:34 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I cannot predict why the update is not working because i dont have DDL and DML for it, if you could post it, may be we can try and let u know
Re: updating based on multiple condition [message #412011 is a reply to message #412001] Tue, 07 July 2009 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
VIPINMADAAN wrote on Tue, 07 July 2009 13:19

if weight_utilization >=10 and weight_utilization <20
then throw the value as utilization_10



None of your previous posts say that. What I put was a direct cut and paste from your posts.
Consequently ashoka_bl's update doesn't say that either. Maybe if you rewrite it so that it does it'll work.
Re: updating based on multiple condition [message #412026 is a reply to message #412003] Tue, 07 July 2009 07:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Run this and see how many rows it returns:
SELECT CASE WHEN weight_utilization < 10 THEN 'utilization_0' 
            WHEN (weight_utilization >= 10 AND weight_utilization < 20) THEN 'utilization_10' 
            WHEN (weight_utilization >= 20 AND weight_utilization < 30) THEN 'utilization_20' 
            WHEN (weight_utilization >= 30 AND weight_utilization < 40) THEN 'utilization_30' 
            WHEN (weight_utilization >= 40 AND weight_utilization < 50) THEN 'utilization_40' 
            WHEN (weight_utilization >= 90 AND weight_utilization < 100) THEN 'utilization_90' 
            WHEN weight_utilization >= 100 THEN 'utilization_100' 
                      END result
       ,weight_utilization
FROM    shipment
WHERE  shipment_gid = 'test.01367'
Re: updating based on multiple condition [message #412029 is a reply to message #412011] Tue, 07 July 2009 08:09 Go to previous messageGo to next message
VIPINMADAAN
Messages: 7
Registered: July 2009
Junior Member
cookiemonster wrote on Tue, 07 July 2009 07:34
VIPINMADAAN wrote on Tue, 07 July 2009 13:19

if weight_utilization >=10 and weight_utilization <20
then throw the value as utilization_10



None of your previous posts say that. What I put was a direct cut and paste from your posts.
Consequently ashoka_bl's update doesn't say that either. Maybe if you rewrite it so that it does it'll work.


Ok, thanks..I understand what you mean. It was my mistake not to mention that.
Re: updating based on multiple condition [message #412030 is a reply to message #412026] Tue, 07 July 2009 08:14 Go to previous messageGo to next message
VIPINMADAAN
Messages: 7
Registered: July 2009
Junior Member
JRowbottom wrote on Tue, 07 July 2009 07:55
Run this and see how many rows it returns:
SELECT CASE WHEN weight_utilization < 10 THEN 'utilization_0' 
            WHEN (weight_utilization >= 10 AND weight_utilization < 20) THEN 'utilization_10' 
            WHEN (weight_utilization >= 20 AND weight_utilization < 30) THEN 'utilization_20' 
            WHEN (weight_utilization >= 30 AND weight_utilization < 40) THEN 'utilization_30' 
            WHEN (weight_utilization >= 40 AND weight_utilization < 50) THEN 'utilization_40' 
            WHEN (weight_utilization >= 90 AND weight_utilization < 100) THEN 'utilization_90' 
            WHEN weight_utilization >= 100 THEN 'utilization_100' 
                      END result
       ,weight_utilization
FROM    shipment
WHERE  shipment_gid = 'test.01367'



Hi,

I don;t get any rows returning out of it.

I understood one issue here that actual_image is actually an FK in shipment table. Thats why my system is not allowing to update.

the values mentioned by me like "utilization_0,utilization_10 are actually the values in another table " icon " column 'icon_gid' which I wanted to bring here directly. But seems now that my approach should be to reference the values from this table with something like icon.icon_gid = 'utilization_10' etc.

Can you guys suggest what I should be doing to get this?
Re: updating based on multiple condition [message #412041 is a reply to message #411911] Tue, 07 July 2009 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
select <whatever> from icon where icon_gid = <case statement>
Re: updating based on multiple condition [message #412163 is a reply to message #411911] Wed, 08 July 2009 01:28 Go to previous messageGo to next message
VIPINMADAAN
Messages: 7
Registered: July 2009
Junior Member
Can some one help on this : I framed my query as shown below:Note that I am able to use the query when I use it for a single record like where shipment_gid = TEST.01467'
but my requirement is to use it when ever a new record of shipment is created .

update shipment
set Actual_image = 
(SELECT CONCAT('UTILIZATION_', floor(weight_utilization*100)- mod(floor(weight_utilization*100),10)) from shipment ) 
WHERE SHIPMENT_GID =?;

Error	Exception	java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

.
Re: updating based on multiple condition [message #412177 is a reply to message #412163] Wed, 08 July 2009 02:06 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Can some one help on this :

your SELECT is returning more than one row. Shocked
Re: updating based on multiple condition [message #412181 is a reply to message #412163] Wed, 08 July 2009 02:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
VIPINMADAAN wrote on Wed, 08 July 2009 08:28
but my requirement is to use it when ever a new record of shipment is created .

update shipment
set Actual_image = 
(SELECT CONCAT('UTILIZATION_', floor(weight_utilization*100)-
mod(floor(weight_utilization*100),10)) from shipment ) 
WHERE SHIPMENT_GID =?;

Error	Exception	java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

.


The note about creating a new row seems strange as you issue UPDATE Statement. Now, you take value(s) from all row(s) of SHIPMENT table - this is logical nonsense, as one row may hold one set of values.

Just a guess: if you want to UPDATE the value based on other column values in the same row you are updating, you do not need subselect at all; you may simply use
update shipment
set Actual_image = CONCAT('UTILIZATION_',
  floor(weight_utilization*100)-mod(floor(weight_utilization*100),10))
WHERE SHIPMENT_GID =?
If you use Oracle 11g, you may consider using virtual column.

Otherwise, you shall realize, what exactly you need, and formulate it more clear than you did in this thread.
Previous Topic: Is there any alternative for LIST partition to capture the DEFAULT values? (merged)
Next Topic: Linkage problem when summing values in 2 tables.(merged)
Goto Forum:
  


Current Time: Sat Dec 10 10:55:06 CST 2016

Total time taken to generate the page: 0.08196 seconds