SQL Syntax with Conditional Logic 2

[ks01]

Hello ...

I am trying to obtain some assistance for using conditional logic with DB2.

My task is to select a record in a table based on date fields. Each record has a date field. Users add records to this table with a specific calendar date (e.g. 2007-10-21, 2007-12-31, etc...).

The basic query is:

SELECT RECORD,
       DATE
  FROM TABLE
 WHERE DATE = '2007-10-21';

The logic I am trying to employ needs to look at the date entered (e.g. 2007-10-21). If the date is equal to a Sunday, then include a second date in the WHERE clause. If the date is equal to Monday, then include a second date in the WHERE clause. Etc...

SELECT RECORD,
       DATE
  FROM TABLE
 WHERE DATE = '2007-10-21'
       IF DAYOFWEEK(DATE) = 0 THEN ALSO SEARCH '2100-09-09'
       IF DAYOFWEEK(DATE) = 1 THEN ALSO SEARCH '2100-03-03'
       IF DAYOFWEEK(DATE) = 2 THEN ALSO SEARCH '2100-04-04'
       IF DAYOFWEEK(DATE) = 3 THEN ALSO SEARCH '2100-05-05'
       IF DAYOFWEEK(DATE) = 4 THEN ALSO SEARCH '2100-06-06'
       IF DAYOFWEEK(DATE) = 5 THEN ALSO SEARCH '2100-07-07'
       IF DAYOFWEEK(DATE) = 6 THEN ALSO SEARCH '2100-08-08'
       IF DAYOFWEEK(DATE) = 7 THEN ALSO SEARCH '2100-10-10'
       ELSE ONLY SEARCH DATE;

I've looked at the CASE statement, but am not sure how to apply this.

Thanks in advance ...

 

[Truusvlugindewind]

This will work for V8 or higher

where DATUM = date ('2007-10-12')
   or DATUM = case dayofweek (date('2007-10-12'))
                         when 0 then     date('2100-09-09')
                         when 1 then     date('2100-03-03')
                                else     date('2100-10-10')
                         end

 

[Truusvlugindewind]

I a re-reading your initial question..... how do you imagine the ELSE??? You test 8 days in the week and expect an ELSE??
If monday or tuesday .... or sunday
then bla
else halleki-day?

 

[MarcLodge]

I think we need a bit more info here. KS01 said that he needs to examine a date to see if it's equal to a Sunday, all well and good, but then shows SQL that tests the date to see if it's equal to '2007-10-21' and goes on further to try to test that date to see if it's equal to another date.

If it's equal '2007-10-21' then it's not going to be anything other date!

I suspect that you want to look at the date column on the table, and if it's a Sunday do something to that date field, but I'm only guessing here.

Please get back to us.

Marc

 

[ks01]

Hi Truusvlugindewind and Marc ...

Thank you for the feedback. Apologies if my original post was not clear. Let me attempt to explain in a different way.

The business rule states that a user can add a record for a specific calendar date up to two days prior to that date (e.g. a user can add a record for 2007-10-21 up until 00:00 GMT on 2007-10-19). If the user misses the cutoff time for this calendar date (2007-10-21), the application will display a message to the user advising them to chose another future date.

The user has an opportunity to add a record after the cutoff date if the user uses what we refer to as a "LATE DATE". These late dates are fictious dates, assigned to calendar days. Those fictious dates are:

[tt]Sunday 2100-09-09
Monday 2100-03-03
Tuesday 2100-04-04
Wednesday 2100-05-05
Thursday 2100-06-06
Friday 2100-07-07[/tt]

I would like for my query to show me:

SELECT RECORD,
       DATE
  FROM TABLE
 WHERE DATE = '2007-10-21'  --- REAL DATE
   AND DATE = '2100-09-09'; --- LATE DATE

My attempt in my original post was to show if the date entered for the REAL DATE is equal to a Sunday, then in addition to searching on the date the user enters, also search on the Sunday equivalent of 2100-09-09. If the date entered in the REAL DATE is equal to a Monday, then in addition to searching on the date the user enters, also seach on 2100-03-03.

I hope that helps in clarifying a bit more.

Thank you again for your feedback!

 

[MarcLodge]

KS01,
I'm still not 100% certain, so please allow me to summarise what I think you are after.

The user enters various details plus a date. This date can be in the past, present, or up to two days in the future. If it is over two days in the future, then the date entered must be 2100-09-09, 2100-03-03, etc depending on the day of the week of today's date.

Please let me know if I have understood this correctly.

Marc

 

[ks01]

Hi Marc ...

That is sort of correct. All records entered are for future dates. Users can never enter a date in the past. Here is a sample list from the application's calendar:

LOAD DATE CUTOFF
2007-10-21 2007-10-19 SUNDAY
2007-10-22 2007-10-20 MONDAY
2007-10-23 2007-10-21 TUESDAY
2007-10-24 2007-10-22 WEDNESDAY
2007-10-25 2007-10-23 THURSDAY
2007-10-26 2007-10-24 FRIDAY
2007-10-28 2007-10-26 SUNDAY
2007-10-29 2007-10-27 MONDAY
2007-10-30 2007-10-28 TUESDAY
2007-10-31 2007-10-29 WEDNESDAY
2007-11-01 2007-10-30 THURSDAY

If the user wants to enter a change in the application for example for Tuesday, October 23, they have up until the October 21 to enter it. After the 21st, the application will no longer allow them to enter any records for this date.

However, some changes can still be made after the cutoff. Since the application will not allow the user to enter it for the October 23 date (this day is a Tuesday), we have special dates assigned to each day of the week minus Saturday, that users can enter limited type of changes which the application will eventually merge in to the October 23 date. The special date associated with Tuesday is 2100-04-04.

When the application begins background processing for Tuesday, October 23, it will go out and pull all records which are ready from 2007-10-23 and 2100-04-04.

The same thing will happen on Wednesday, except the dates will change, it will be become 2007-10-24 and 2100-05-05.

I am trying to write my query so that all a user has to do is enter a calendar date, and based on that calendar date, the query will not only search the date they enter, but also the associated special date assigned to that calendar day. In this example, if the user searches on 2007-10-23 the query should also search on 2100-04-04.

Hope that helps clarify.

Kent

 

[MarcLodge]

OK, Kent, got what it is you are after. I believe that Truus' case statement from earlier in the thread is exactly what you are after.

Marc

 

[Truusvlugindewind]

Thanks Marc

Kent, please notice my "where .. or .." instead of your "where .. and ..".

succes

 

[ks01]

Hi guys ...

I've tested this a few different ways and it works great.

Thanks for your input, much appreciated!

Kent