Home » SQL & PL/SQL » SQL & PL/SQL » fatch column value on basis condition (PL Sql)
fatch column value on basis condition [message #445145] 
Fri, 26 February 2010 03:58 
sonalshastry
Messages: 52 Registered: September 2007

Member 


Hi all
i need some help to write SQL query foe a scenario
explained below
Table and data storage format
table structure:
App_no, bid_1, bid2, bid3, price1, price2, price3
data in table
App_no, bid_1, bid2, bid3, price1, price2, price3
001 10 15 14 60 62 65
002 12 14 18 60 68 62
i need to fetch the bid, price, (bid*price)value columns
for the condition where in (bid*price)is max
in given data structure expected result should be
App_no bid price value
001 15 62 930 bid2 ans price2 columns
002 18 62 1116 bid3 ans price2 columns
please revert
Thanks
sonal





Re: fatch column value on basis condition [message #445152 is a reply to message #445145] 
Fri, 26 February 2010 04:12 
sonalshastry
Messages: 52 Registered: September 2007

Member 


i need to query the calculation first & then find max among all the 3 value & then bring the bid and price used to derive that value
like
App b1 b2 b3 p1 p2 p3 v1 v2 v3
1 10 15 14 60 62 65 600 [b]930[/b] 910
2 12 14 18 60 68 62 720 952 [b]1116[/b]
on basis of above data my column get change for 1 st row it were bid2 , price2 and for 2nd row it become bid3 ,price3
same thing to have to do on table contains more than million of rows
thank
sonal




Re: fatch column value on basis condition [message #445161 is a reply to message #445152] 
Fri, 26 February 2010 04:53 

ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...

Senior Member 


SQL> select app_no, bid_1*price_1,bid_1*price_2,bid_1*price_3,
2 bid_2*price_1,bid_2*price_2,bid_2*price_3,
3 bid_3*price_1,bid_3*price_2,bid_3*price_3
4 from data
5 /
APP_NO BID_1*PRICE_1 BID_1*PRICE_2 BID_1*PRICE_3 BID_2*PRICE_1 BID_2*PRICE_2
     
BID_2*PRICE_3 BID_3*PRICE_1 BID_3*PRICE_2 BID_3*PRICE_3
   
1 600 620 650 900 930
975 840 868 910
2 720 816 744 840 952
868 1080 1224 1116
SQL>
why BID_2*PRICE_2, bid_3*price_3... what about BID_2*PRICE_3, BID_3*PRICE_2 which is 1224
sriram



Re: fatch column value on basis condition [message #445172 is a reply to message #445145] 
Fri, 26 February 2010 05:38 
sonalshastry
Messages: 52 Registered: September 2007

Member 


Dear sriram
bid & Price column is co related to each other
thats why i need to multiple them in Sets of Bid1*price1
Bid2*price2 & Bid3*price3
& my issue is related to finding max of set wise value column that is
Bid1*price1 = value1
Bid2*price2 = value2
Bid3*price3 = value3
and the corresponding bid & price column to derive that value column
Thanks
Sonal





Re: fatch column value on basis condition [message #445175 is a reply to message #445174] 
Fri, 26 February 2010 05:43 
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...

Senior Member 


A CREATE TABLE statement and a set of INSERT statements that together create the test data you are using.
We can do it ourselves, and sometimes do, but in general, if you want our help, it would be nice if you did some of the work....




Re: fatch column value on basis condition [message #445182 is a reply to message #445145] 
Fri, 26 February 2010 05:58 
ora_baby
Messages: 89 Registered: May 2008

Member 


try
with ora_test as
(
select 1 App,10 b1, 15 b2, 14 b3, 60 p1, 62 p2, 65 p3 from dual
union all
select 2 App, 12 b1, 14 b, 18 b3, 60 p1, 68 p2,62 p3 from dual
)
select tmp.app, tmp.b, tmp.p, tmp.val
from
(
select t.*, t.b1*t.p1 pr1, t.b2*t.p2 pr2, t.b3*t.p3 pr3
, case when (t.b1*t.p1 > t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then b1
when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then b2
when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 < t.b3*t.p3) then b3
end as b
, case when (t.b1*t.p1 > t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then p1
when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then p2
when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 < t.b3*t.p3) then p3
end as p
, case when (t.b1*t.p1 > t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then t.b1*t.p1
when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then t.b2*t.p2
when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 < t.b3*t.p3) then t.b3*t.p3
end as val
from ora_test t
) tmp



Goto Forum:
Current Time: Mon Apr 24 18:07:27 CDT 2017
Total time taken to generate the page: 0.19322 seconds
