Home » SQL & PL/SQL » SQL & PL/SQL » maltiple condition in select statement
maltiple condition in select statement [message #270917] Fri, 28 September 2007 07:17 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all

Can Some one tell me, how can I put 4 diffrent conditions in a single select statement

for example i have 4 schemes & want to put all my client in any one of the scheme as per their trade

please suggest some thing

sonal

[Updated on: Fri, 28 September 2007 07:30]

Report message to a moderator

Re: maltiple condition in select statement [message #270918 is a reply to message #270917] Fri, 28 September 2007 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially section about IM speak.

Regards
Michel

Re: maltiple condition in select statement [message #270924 is a reply to message #270917] Fri, 28 September 2007 07:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you give us some sort of example of what you're trying to do?

Possibly you're looking for the DECODE or CASE statments - they let you do conditional logic in SQL.
Re: maltiple condition in select statement [message #270938 is a reply to message #270924] Fri, 28 September 2007 08:12 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Select dcm.dcn_ent_id

case when sum(dcd.dcd_qty*dcd.dcd_price) <= 50000000
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.0))/100)
end "flat_BRK1",

case when sum(dcd.dcd_qty*dcd.dcd_price) > 50000000 and sum(dcd.dcd_qty*dcd.dcd_price) < 100000000
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.005))/100)
end "flat_BRK2",

case when sum(dcd.dcd_qty*dcd.dcd_price) > 100000000 and sum(dcd.dcd_qty*dcd.dcd_price) < 200000000
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.0075))/100)
end "flat_BRK3",

case when  sum(dcd.dcd_qty*dcd.dcd_price) > 200000000 and sum(dcd.dcd_qty*dcd.dcd_price)< 99999999999999
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.01))/100)
end "flat_BRK4"

from dtm_Contract_Note_Details dcd, dtm_contract_note_master dcm
where dcm.dcn_no = dcd.dcd_dcn_no
and dcd.dcd_trade_squp_flag = 'Y'
and Dcd.Dcd_Dcm_Id like 'FUTSTK%'
and dcm.dcn_date  >= '10-jun-2007' and dcm.dcn_date <='30-jun-2007'
group by dcm.dcn_ent_id



And output of the above query is

DCN_ENT_ID  flat_BRK1  flat_BRK2  flat_BRK3   flat_BRK4
376174	      0        2666.22        0             0
548141        0        3644.49        0             0
90033	      0           0          12474.98       0



I want just a single Flat_BRK1 Column no matter if client fall in any condition used in query.

please help

sonal


[Updated on: Fri, 28 September 2007 23:52]

Report message to a moderator

Re: maltiple condition in select statement [message #270939 is a reply to message #270938] Fri, 28 September 2007 08:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very pretty.

Is the data you've provided a demonstration that you've worked out how to do it yourself, of is there some subtle flaw in it that we're expected to work out for ourselves?
Re: maltiple condition in select statement [message #270940 is a reply to message #270938] Fri, 28 September 2007 08:17 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
sonalshastry wrote on Fri, 28 September 2007 09:12
Select dcm.dcn_ent_id

case when sum(dcd.dcd_qty*dcd.dcd_price) <= '50000000'
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.0))/100)
end "flat_BRK1",

case when sum(dcd.dcd_qty*dcd.dcd_price) > '50000000' and sum(dcd.dcd_qty*dcd.dcd_price) < '100000000'
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.005))/100)
end "flat_BRK2",

case when sum(dcd.dcd_qty*dcd.dcd_price) > '100000000' and sum(dcd.dcd_qty*dcd.dcd_price) < '200000000'
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.0075))/100)
end "flat_BRK3",

case when  sum(dcd.dcd_qty*dcd.dcd_price) > '200000000' and sum(dcd.dcd_qty*dcd.dcd_price)< '99999999999999'
then ((sum(dcd.dcd_qty*dcd.dcd_price) * (0.01))/100)
end "flat_BRK4"

from dtm_Contract_Note_Details dcd, dtm_contract_note_master dcm
where dcm.dcn_no = dcd.dcd_dcn_no
and dcd.dcd_trade_squp_flag = 'Y'
and Dcd.Dcd_Dcm_Id like 'FUTSTK%'
and dcm.dcn_date  >= '10-jun-2007' and dcm.dcn_date <='30-jun-2007'
group by dcm.dcn_ent_id





1. You are comparing DATEs to character stings.
2. You are comparing NUMBERs to character strings.

I think it's time to familiarize yourself with the Oracle Concepts Manual at http://tahiti.oracle.com
Re: maltiple condition in select statement [message #271070 is a reply to message #270940] Sat, 29 September 2007 00:04 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member

Hi all

I am just using numbers only not date or not char
as i have 4 different condition in my query and which gives me 4 diffrent columns, I want to combined single column for all the 4 conditions results.

kindly suggest some way

Best Regards
Sonal
Re: maltiple condition in select statement [message #271079 is a reply to message #271070] Sat, 29 September 2007 01:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sonalshastry wrote on Sat, 29 September 2007 07:04
I am just using numbers only not date or not char


Here you compare a number (the result of the sum) to a string
sum(dcd.dcd_qty*dcd.dcd_price) > '200000000'


Here you compare what probably is a date (dcn_date) to a string.
dcm.dcn_date  >= '10-jun-2007'
Re: maltiple condition in select statement [message #271084 is a reply to message #271079] Sat, 29 September 2007 01:59 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member


Hi

i have kept a date condition to retrive data for that period
and sorry for string mistake but I did edit on it Please have a look on my previous reply

sonal
Re: maltiple condition in select statement [message #271364 is a reply to message #271084] Mon, 01 October 2007 07:52 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Where?
Please show us how you have rectified comparing a DATE column to a date? you really really need to go and read the Concepts manual on what a DATE is. Do you have to be so stubborn as to ignore the correct way to use a DATE?
Previous Topic: Error in inserting image in BLOB column
Next Topic: deleting a column
Goto Forum:
  


Current Time: Sun Dec 11 06:30:51 CST 2016

Total time taken to generate the page: 0.22398 seconds