Oracle PL/SQL case statements not executing as expected-CodePudding

I'm completely new to PL/SQL but am trying to use it for a solution for an assignment at my job. Essentially, I'm wanting to run through case statements to output a value for the previous accounting period. Here is what I put together and I'll explain what is occurring below it:

select
case
     when substr(sysdate, 1, 6) >= '01-JAN' and substr(sysdate, 1, 6) <= '31-JAN' then '03'
     when substr(sysdate, 1, 6) >= '01-FEB' and substr(sysdate, 1, 6) <= '29-FEB' then '04'
     when substr(sysdate, 1, 6) >= '01-MAR' and substr(sysdate, 1, 6) <= '31-MAR' then '05'
     when substr(sysdate, 1, 6) >= '01-APR' and substr(sysdate, 1, 6) <= '30-APR' then '06'
     when substr(sysdate, 1, 6) >= '01-MAY' and substr(sysdate, 1, 6) <= '31-MAY' then '07'
     when substr(sysdate, 1, 6) >= '01-JUN' and substr(sysdate, 1, 6) <= '30-JUN' then '08'
     when substr(sysdate, 1, 6) >= '01-JUL' and substr(sysdate, 1, 6) <= '31-JUL' then '09'
     when substr(sysdate, 1, 6) >= '01-AUG' and substr(sysdate, 1, 6) <= '31-AUG' then '10'
     when substr(sysdate, 1, 6) >= '01-SEP' and substr(sysdate, 1, 6) <= '30-SEP' then '11'
     when substr(sysdate, 1, 6) >= '01-NOV' and substr(sysdate, 1, 6) <= '30-NOV' then '01'
     when substr(sysdate, 1, 6) >= '01-DEC' and substr(sysdate, 1, 6) <= '31-DEC' then '02'
end actg_period
from table_name

I excluded October as that is a separate condition. What's happening with the above is that today the sysdate value(I've verified this in our db) is '01-JUN' and should net an output value of '08'. However, what it's doing is netting a value of '03'. I ran this same code yesterday(May 31st) and it netted a value or '07' as expected. I cannot determine what the issue is, given my little experience with case statements.

Any advice is appreciated.

CodePudding user response：

Converting and comparing dates as strings often leads to problems.

You say that today (1-Jun-2022) you get '01-JUN' for SUBSTR(SYSDATE, 1, 6). This is absolutely correct (if we assume that your system date format is something like 'DD-MON-YYYY').

You then say that you're surprised you're getting 03 from your CASE expression. The question to ask is, "Is the character string '01-JUN' between the character strings '01-JAN' and '31-JAN`"? And the answer is "Yes, it is". So, why the confusion?

Well, we understand why. What you REALLY wanted to say is:

Is the day-of-month portion ('01') of SYSDATE between '01' and '31' and is the month portion of SYSDATE ('JUN') = 'JAN'? If asked that way, the answer is No, it's not, but that's not what your code does.

And this is why we don't convert dates to strings to compare them - because the results we get are always correct but are often surprising.

There are better ways to do this, and every one of them does NOT involve converting the date to a string.

Others have made excellent suggestions.

But I've got one more, which just uses a little math and a character conversion at the end:

ACCTG_PERIOD := TO_CHAR(MOD(EXTRACT(MONTH FROM SYSDATE)   2, 12), '09');

CodePudding user response：

The issue is that you are comparing strings, not dates. Yesterday, substr(sysdate, 1, 6) gave you - assuming your session NLS settings are still the default, which is not a safe assumption for you to make, particularly about anyone else running this code - the string '31-MAY'.

When compared as a string that happens to not fit into any of the earlier brackets.

As a demonstration, if you order the first three days each each month, formatted as DD-MON-YY, in alphabetical order you get:

01-APR-22
01-AUG-22
01-DEC-22
01-FEB-22
01-JAN-22
01-JUL-22
01-JUN-22
01-MAR-22
01-MAY-22
01-NOV-22
01-OCT-22
01-SEP-22
02-APR-22
02-AUG-22
02-DEC-22
...
02-SEP-22
03-APR-22
03-AUG-22
...
03-SEP-22

Using just the first day of each month, your first bracket '01-JAN' to '31-JAN' includes the strings (not dates):

01-JAN-22
01-JUL-22
01-JUN-22
01-MAR-22
01-MAY-22
01-NOV-22
01-OCT-22
01-SEP-22

and all other dates in those months.

The second bracket '01-FEB' to '29-FEB' includes:

01-FEB-22
01-JAN-22
01-JUL-22
01-JUN-22
01-MAR-22
01-MAY-22
01-NOV-22
01-OCT-22

and almost all other dates in those months - everything up to the 29th of each month, so 30th and 31st are excluded.

The third '01-MAR' to '31-MAR' contains:

01-MAR-22
01-MAY-22
01-NOV-22
01-OCT-22

and all other dates in those months.

The fourth '01-APR' to '30-APR' contains all of them:

01-APR-22
01-AUG-22
01-DEC-22
01-FEB-22
01-JAN-22
01-JUL-22
01-JUN-22
01-MAR-22
01-MAY-22
01-NOV-22
01-OCT-22

and all other dates in those months except the 31st.

And the fifth '01-MAY' to '31-MAY' contains:

01-MAY-22
01-NOV-22
01-OCT-22

and all other dates in those months.

And so on.

When you compared the string version of yesterday's date, '31-MAY', that didn't fit into the first bracket because, alphabetically, '31-M' isn't between '01-J' and '31-J', since 'M' comes after 'J' in the alphabet. For the second bracket it didn't even need to look at the month, it could stop comparing after the first character - '3' comes after '2', so it doesn't fit in that bracket. The third bracket is slightly more exciting as it had to check the whole string - but still, '31-MAY' isn't between '01-MAR' and '31-MAR', because 'Y' comes after 'R'. The fourth bracket could be rejected after two characters because '1' comes after '0'. It finally can match on the fifth bracket, which is what you expected.

But when you compared the string version of today's date, '01-JUN', that was matched against your first bracket, as '01-JUN' is included in that first list.

You haven't said how you want to handle October, but for the rest you don't need a case expression for the conversion; you can do:

TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 2), 'MM')

which gives '08' today. You can modify that to make October a special case - quite how depends on how you want to treat it; either a different number of months added, or a case expression with a specific value.

CodePudding user response：

You can just use EXTRACT to check the month:

SELECT CASE EXTRACT(MONTH FROM sysdate) 
WHEN 1 THEN '03'
WHEN 2 THEN '04'
...
WHEN 12 THEN '02'
END AS actg_period
FROM table_name;

This makes your query much shorter and easier to read.

CodePudding user response：

Why would you substring a DATE? Just compare as dates:

SELECT
CASE
     WHEN SYSDATE >= TO_DATE('01-JAN','DD-MON') AND SYSDATE <= TO_DATE('31-JAN','DD-MON') THEN '03'
     WHEN SYSDATE >= TO_DATE('01-FEB','DD-MON') AND SYSDATE <= ADD_MONTHS(TO_DATE('01-FEB','DD-MON'),1) -1 THEN '04'
     WHEN SYSDATE >= TO_DATE('01-MAR','DD-MON') AND SYSDATE <= TO_DATE('31-MAR','DD-MON') THEN '05'
     WHEN SYSDATE >= TO_DATE('01-APR','DD-MON') AND SYSDATE <= TO_DATE('30-APR','DD-MON') THEN '06'
     WHEN SYSDATE >= TO_DATE('01-MAY','DD-MON') AND SYSDATE <= TO_DATE('31-MAY','DD-MON') THEN '07'
     WHEN SYSDATE >= TO_DATE('01-JUN','DD-MON') AND SYSDATE <= TO_DATE('30-JUN','DD-MON') THEN '08'
     WHEN SYSDATE >= TO_DATE('01-JUL','DD-MON') AND SYSDATE <= TO_DATE('31-JUL','DD-MON') THEN '09'
     WHEN SYSDATE >= TO_DATE('01-AUG','DD-MON') AND SYSDATE <= TO_DATE('31-AUG','DD-MON') THEN '10'
     WHEN SYSDATE >= TO_DATE('01-SEP','DD-MON') AND SYSDATE <= TO_DATE('30-SEP','DD-MON') THEN '11'
     WHEN SYSDATE >= TO_DATE('01-NOV','DD-MON') AND SYSDATE <= TO_DATE('30-NOV','DD-MON') THEN '01'
     WHEN SYSDATE >= TO_DATE('01-DEC','DD-MON') AND SYSDATE <= TO_DATE('31-DEC','DD-MON') THEN '02'
 END actg_period
FROM table_name ;