INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help on Where clause

Need help on Where clause

(OP)
Friends
I want to write a where clause in such a way that

I am getting all data from July(Current year) to July(next year) suppose by the end of year I have amount >0 then I need to get data
July(Previous year) to July(Current year).
DO i need to write a IF else statement in Where statement?. Please advice me.

Regards,
Lori

RE: Need help on Where clause

Doubtful..

I would write a union to solve this one, like:

CODE

SELECT ............. FROM ........
INNER JOIN
<< SOME EXPRESSION FOR THE FIRST SCENARIO >>
ON
.........
UNION
SELECT ............. FROM ........
INNER JOIN
<< SOME EXPRESSION FOR THE SECOND SCENARIO >>
ON
............

The rows that come from the 2 scenario expressions then control the output of the main data queries.

If you have more exact example , then it is easier to give example code..  

Ties Blom
 
 

RE: Need help on Where clause

(OP)
Blom thanks for the quick reply. Below is sample data

I get data for fiscal year July 08 to june 09

then again next fiscal year start from july 09, now when i query i should get data july 09 to August 09.

Suppose if the total amount > 0 in june 09 then i need to display data in reverse
july09 to july08.
So basically I want to show data in incremental manner july09
august 09 sept 09 so on, but in other case when fiscl year eneded in june 09 and amount >0 then i want to show data in deceremental manner of months julu09 june 09 april 09 till july 08.

Can this be done?

RE: Need help on Where clause

When you mean 'I get data', is this some single amount for the period that you query? Or do you fetch dimensions in the query?

'total amount > 0 in june 09'

Do you mean that you want to change the query conditions once any amount is registred in the new june month?

Example:

CODE


SELECT 1 AS CHECK1,SUM(AMOUNT) FROM TABLE WHERE .........
INNER JOIN
(SELECT
CASE WHEN
(SELECT SUM(AMOUNT) FROM TABLE WHERE YEAR(SOMEDATE)*100+MONTH(SOMEDATE) = 200906) > 0 THEN 1 ELSE 0 END AS CHECK2) TEMP
ON CHECK1 = TEMP.CHECK2
UNION
SELECT 1 AS CHECK1,SUM(AMOUNT) FROM TABLE WHERE .........
INNER JOIN
(SELECT
CASE WHEN
(SELECT SUM(AMOUNT) FROM TABLE WHERE YEAR(SOMEDATE)*100+MONTH(SOMEDATE) = 200906) = 0 THEN 1 ELSE 0 END AS CHECK2) TEMP
ON CHECK1 = TEMP.CHECK2

Unfortunately I have no DB2 instance available to check if this is entirely properly coded.
The idea is to perform the amount check and use it in a join.. (so either the first OR the second part of the union brings back any data)

 

Ties Blom
 
 

RE: Need help on Where clause

(OP)
Hi Blom,
How can I write where condtion that my query always fetches data from July of present year? no matter when i run the report if I run today, report needs to fetch the data for July, and when i run report next month in Sept I need to get data for July and August, so on. ANy ideas?
. Thank you so much for taking your time and helping me in this
Lori

 

RE: Need help on Where clause

(OP)
And my date format is 8/14/2009, I want to write a condtion like Datefield between Date(year(Current date),07,01) and
Current date. How to format the start date like 07/01/2009?

RE: Need help on Where clause

(OP)
Ok i tried I got it date( '07' || '-' || '01' || '-' || CHAR(YEAR(current date ))) as date_converted .
I will try your query logic now and will let you know

RE: Need help on Where clause

(OP)
Blom thank you I have got the where clause sucessfully. I will let you know once i get to the logic, you been a great help. Thank you
Lori

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close