updating based on multiple condition [message #411911] |
Tue, 07 July 2009 05:17  |
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 #411923 is a reply to message #411911] |
Tue, 07 July 2009 05:30   |
cookiemonster
Messages: 13963 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   |
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 #411978 is a reply to message #411911] |
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)
|
|
|
Re: updating based on multiple condition [message #411996 is a reply to message #411923] |
Tue, 07 July 2009 07:11   |
cookiemonster
Messages: 13963 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   |
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   |
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 #412011 is a reply to message #412001] |
Tue, 07 July 2009 07:34   |
cookiemonster
Messages: 13963 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   |
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   |
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   |
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 #412163 is a reply to message #411911] |
Wed, 08 July 2009 01:28   |
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 #412181 is a reply to message #412163] |
Wed, 08 July 2009 02:31  |
flyboy
Messages: 1903 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.
|
|
|