Select with AND OR clause

[Stang02GT]

I am trying to get the right syntax around a select statement. Here is what I am trying to do.


I am selecting rows from a table based on date ranges that works fine. Where i am getting stuck is there is a third field i need to include that will either have a value or if it's left blank i need to return all the rows for the date range or i need to return all the rows for the date range and with the selected PAY_CYCLE

I was trying to do it with an AND OR

This is the specific syntax I am having issues with is the and or correct to use in this case? What should i use in my OR clause?

AND (A.PAY_CYCLE = :1 OR A.PAY_CYCLE = ?)

Any help is appreciated!

[Anas Mosaad]

Let me try to rephrase the problem:
Third field (say: f3) can either have a value or NULL.
If f3 is null, either:
- return all rows for the date range.
- OR, return all rows for the date range and with the selected PAY_CYCLE.

I'm trying to help but I see the question not clear. In the case when you return all rows for the date range, they include the ones with the selected PAY_CYCLE.

Please try to rephrase the problem to be able to help you.

Thanks

[Stang02GT]

Sorry for the confusion. Sometimes the hardest part is explain the issue. Your rephrase is correct.

[Anas Mosaad]

No need to be sorry. I'm not a native English speaker and I cause lots of confusions many times. May be the I'm the only one who gets confused.
Anyway, back to your issue: when should it return all the rows? and when should it return only those with a matching PAY_CYCLE?
If either of them can be returned, you can always try to return all!

[Stang02GT]

So right now there are three prompts that the user has they are…

From Date
To Date
Cycle

Here are the different scenarios.
1. Users what to run the query for a specific date range and want to include all of the cycles.
2. The users want to run the query for a specific date range AND for one specific cycle.

My issue is that my query currently works if the users enter both a date range and a cycle. However I cannot get the query to work if the I use just the date ranges and exclude a specific cycle.

Right now I think that my issue is around how I am joining the tables in my query. I am joining them on cycle because it is in all three of the tables I am querying from. I think because that is what I am joining on that if I exclude it from the query it is not working correctly.