Home » SQL & PL/SQL » SQL & PL/SQL » Multiple where conditions for a column in a single query
Multiple where conditions for a column in a single query [message #347848] Sun, 14 September 2008 09:53 Go to next message
apaul
Messages: 1
Registered: September 2008
Junior Member
Hi,

I have a table (Expense) which returns month-wise expenditure amounts.

Period Expense
Jan-08 10
Feb-08 20
Mar-08 25
Apr-08 35


Now, based on a 'From date' (format: MON-YYYY) and 'To date' (format: MON-YYYY), I would have to return in a single row:
1. Selected period expense amount: Expense amount between From and To date
2. Previous period expense amount: Expense amount before From Date
3. Future period expense amount: Expense amount after To date

So, for ex. if the From date: FEB-2008 and To Date: MAR-2008, I would get:

From To Selected Period Prev Period Future Period
FEB-2008 MAR-008 45 10 35

This needs to be done by a single query and using the input parameters.

Could someone please help.

Thanks in advance!

Re: Multiple where conditions for a column in a single query [message #347849 is a reply to message #347848] Sun, 14 September 2008 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: Multiple where conditions for a column in a single query [message #347851 is a reply to message #347848] Sun, 14 September 2008 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64136
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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you tried.

Regards
Michel
Re: Multiple where conditions for a column in a single query [message #347922 is a reply to message #347848] Mon, 15 September 2008 01:42 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

You can take a hint from query something like this.
Use union.

SQL> SELECT 1,
2 SUM(Expense)
3 FROM ExpenseTab
4 WHERE Period BETWEEN ('01/FEB/2008')
5 AND ('01/MAR/2008')
6 UNION
7 SELECT 2,
8 Expense
9 FROM ExpenseTab
10 WHERE Period NOT BETWEEN ('01/FEB/2008')
11 AND ('01/MAR/2008')
12 AND Period < '01/FEB/2008'
13 UNION
14 SELECT 3,
15 Expense
16 FROM ExpenseTab
17 WHERE Period NOT BETWEEN ('01/FEB/2008')
18 AND ('01/MAR/2008')
19 AND Period > '01/MAR/2008'
20 ;

1 SUM(EXPENSE)
---------- ------------
1 45
2 10
3 35
Re: Multiple where conditions for a column in a single query [message #347926 is a reply to message #347922] Mon, 15 September 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@harshadsp

From one of your previous posts:
Michel Cadot wrote on Fri, 12 September 2008 15:15
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 the "Preview Message" button to verify.

You will also find in the guidelines:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.

I don't say that what you posted is a solution, there are errors in it. The first one is that a string is NOT a date.


Regards
Michel


Re: Multiple where conditions for a column in a single query [message #347930 is a reply to message #347926] Mon, 15 September 2008 01:59 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

@Michel,

Actually, I have copeid the formatted code from Sql*plus session and even checked through Preview message button.Ii was looking fine but I will once again go through the posting guidelines.

yes,I know its a string and not a date.But I am giving only just a hint like use Union for such queries.

Thanks & regards
Harshad
Re: Multiple where conditions for a column in a single query [message #347948 is a reply to message #347930] Mon, 15 September 2008 02:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Some remarks about your code:
- how many dates do you know that are < date_x, yet they fall between date_x and date_y (considering that in Oracle the lower date always is mentioned first)
- the original poster requested to have all the data in a single record
- you use union, where you should use union all. Maybe not a big point in this particular query, but make it a habit to use the appropriate one.
- make it a habit to not rely on implicit conversion. Also in 'example' queries. It looks sloppy and we want to help people who might not recognize it as something they need to fix
Re: Multiple where conditions for a column in a single query [message #347950 is a reply to message #347930] Mon, 15 September 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I am giving only just a hint

Giving a known wrong hint is not really wise. Even if it is an example, post it as correct as possible.

To format the query use [code]your query[/code] tags (as explained in guidelines).

Regards
Michel
Re: Multiple where conditions for a column in a single query [message #347959 is a reply to message #347950] Mon, 15 September 2008 03:29 Go to previous message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Thanks Michel, Frank for your useful info.
will keep these things in mind before posting.


regards
Harshad
Previous Topic: Mutating Triggers
Next Topic: Check uniqueness of data.
Goto Forum:
  


Current Time: Thu Dec 08 06:13:41 CST 2016

Total time taken to generate the page: 0.09741 seconds