Add two business days to a date

[Vectorspace]

I also did not know about limit and offset. Bookmarking this page for future reference!

Another alternative method, based somewhat on jtalor's SQL - you can use row_number() to add row numbering to the subselect, enabling you to choose the nth row

  with TwoDays as (
select DATE,
       row_number() over(order by DATE) as ROWNUM
  from TABLE
 where DATE > current_date
   and WEEKEND = 'N'
   and HOLIDAY = 'N'
)
select DATE
  from TwoDays
 where ROWNUM = 2;

[gwilburn]

The row_number() is what I was attempting to do prior to my post. I could not get that to work (your example works great).

Since I have this in a procedure, your example may be best. I can make the row number a variable - this would make my procedure more useful (table is DATE_CONV from DB2 WebQuery)

  with BusinessDays as (
select dc_date, row_number() over(order by dc_date) as ROWNUM
  from library.date_conv
 where dc_date > current_date
   and DC_WEEKEND = 'N'
   and DC_HOLIDAY = 'N'
)
select dc_date
  from BusinessDays
 where ROWNUM = :NbrOfDays;

Sweet!

[gwilburn]

Anyone else using the DATE_CONV with Web Query... I used these statements to set my company's holidays. There are fields for "day before holiday" and "day after holiday" - I did not update those. I also didn't know how to update "Good Friday".

This also does not account for holidays that fall on the weekend. In those case, we take Friday when the holiday is on Saturday and Monday when the holiday falls on Sunday.

update mylib.date_conv
set dc_holiday='Y'
where
dc_mm_char = '01' and dc_dd_char = '01' or  // New Years
dc_mm_char = '07' and dc_dd_char = '04' or // 4th July
dc_mm_char = '12' and dc_dd_char = '24' or // Christmas Eve
dc_mm_char = '12' and dc_dd_char = '25' or // Christmas Day
dc_mm_char = '05' and dc_day_name = 'Monday' and dc_dd_char > '24' or  // Memorial Day
dc_mm_char = '09' and dc_day_name = 'Monday' and dc_dd_char < '08' or // Labor Day
dc_mm_char = '11' and dc_day_name = 'Thursday' and dc_dd_char > '23';  // Thanksgiving

[kitvb1]

I've opened a PMR with IBM. SF99701 is listed below. The RDi v9.6 syntax checker also reports that same error (inline in when coding).

Please let us know the outcome of this.

TIA

[jtaylor___]

OFFSET and LIMIT are super useful, thanks!

I was just playing with them and see that they work well for me when I run them in ACS Run SQL Scripts, and also in Surveyor/400 (Linoma). They do not seem to work on the green screen with STRSQL. I have not tried within an RPG program yet or in my RDi v9.5. I'm on i7.2.

What do you get in 5250 STRSQL? I just tried and it works for me (i7.3).

[Viking]

What do you get in 5250 STRSQL? I just tried and it works for me (i7.3).

Really? I'm on i7.2 and I get error SQL0084 "SQL statement not allowed". I get this error with both LIMIT and OFFSET.

[B.Hauser]

Really? I'm on i7.2 and I get error SQL0084 "SQL statement not allowed". I get this error with both LIMIT and OFFSET.

It seems you are not on the current PTF Level (or LIMIT/OFFSET is not installed).
I run an SQL statement with Limit/Offset in STRSQL on a 7.3 Machine without any problems.

Birgitta

[Viking]

It seems you are not on the current PTF Level (or LIMIT/OFFSET is not installed).
I run an SQL statement with Limit/Offset in STRSQL on a 7.3 Machine without any problems.

Again, both LIMIT and OFFSET work fine for me if I run them in ACS Run SQL Scripts or Surveyor/400 (Linoma). They just don't seem to be recognized from the green-screen STRSQL interface... that's when I get the SQL0084 error.

[B.Hauser]

What do you expect? STRSQL is an outdated tool that was stabilized with Release V5!

Birgitta

[Viking]

What do you expect? STRSQL is an outdated tool that was stabilized with Release V5!

What did I expect...? Well, I guess I expected it to work. Lots of SQL functions have become available since V5 that work in STRSQL. For example, the LISTAGG function works, and my understanding is that it only became available for 7.3 TR2 and 7.2 TR6. So yes, I expected LIMIT/OFFSET to work too. But it's not a big deal for me since I usually use Linoma Surveyor to run my SQL statements, but just thought I'd point it out.

[jtaylor___]

What did I expect...? Well, I guess I expected it to work. Lots of SQL functions have become available since V5 that work in STRSQL. For example, the LISTAGG function works, and my understanding is that it only became available for 7.3 TR2 and 7.2 TR6. So yes, I expected LIMIT/OFFSET to work too. But it's not a big deal for me since I usually use Linoma Surveyor to run my SQL statements, but just thought I'd point it out.

It does work. Both Birgitta and I tested it on 7.3. Have you checked PTF's like she suggested?

[gwilburn]

It does work. Both Birgitta and I tested it on 7.3. Have you checked PTF's like she suggested?

I get the same error on v7r1 with the current PTFs. What's troubling is that RDi v9.6.1 doesn't recognize them either. My PMR has been pushed to development.

[jtaylor___]

I get the same error on v7r1 with the current PTFs. What's troubling is that RDi v9.6.1 doesn't recognize them either. My PMR has been pushed to development.

Maybe they added those functions in 7.3.

[Viking]

Ok, I see on IBM's DeveloperWorks page that it says i7.2 needs SF99702 Level 19 for LIMIT/OFFSET, and I see that I'm on Level 18. So it seems that even though my machine can do it with only level 18 (via ACS Run SQL Scripts, Linoma Surveyor, etc.), that the green screen STRSQL must need the higher PTF level. So I'll get that Group PTF updated and try it again.

[gwilburn]

Here is what IBM Support received back from "development".

I have had development review this issue. The problem is the fact that you are using the OFFSET syntax (the 1,1 format uses the Implicit OFFSET) which has limited support in V7R1. The offset-clause is only allowed as part of the outer fullselect of a DECLARE CURSOR statement or a prepared select-statement. It is not supported in interactive SQL. This restriction was lifted for V7R2 and V7R3 with the latest DB Group PTFs.
So if you were to change it to LIMIT 1
That should work, or if you upgrade to V7R2 or V7R3 with the latest Group PTF for database.

My response was rather direct... I told them I did not accept that answer. It works fine in Run SQL Scripts. The compiler (and RDi parser) doesn't recognize it no matter what LIMIT is set to.