Re: Odd Error: Month must be between 1 and 12

[Andrew Rivers <andrew.rivers@CWCOM.NET>]

Hi Denise
Thanks for the comments that you make.
I have confirmed the error to be due to that for invoicenum = 1052  by
putting conditions around show statement.

The show statement is executed left to right and falls over when the show
attempts to parse &invoicedate with the null value.
(The default log file shows all arguments up to and including the one prior
to &invoicedate.)
If the show argument &invoicedate is commented out then there is no compiler
error.

With null selected into &invoicedate, SQR parses
    if isnull (&invoicedate)
without a compiler error.
However it parses it incorrectly as 0 (FALSE).

______________________
As an aside, if null is selected into &invoicedate then
    if isblank( &invoicedate )
is also passed as 0 (FALSE).

However, if null has been selected into &invoiceDate compiler errors occur
if the following statements are executed:
    let #invoiceDateLength = length (&invoiceDate )
    move &invoiceDate to $invoiceDate                        !$invoiceDate
was not declared as a date - see comment below.

I suspect that the issue is with SQR.

______________________
A colleague commented:
    If $invoicedate is declared as a date type you can't move a null value
into it because you can only place a date in it I
    think.  SQR bug?
I explore this comment on Monday.

______________________
There are two work arounds.
1. Convert to the date to a char in which case I suspect that the date
formatting chars are stripped out.

2. As suggested by Betty Thompson:
    select invoicenum, NVL(invoicedate,TO_DATE('01-01-1900','MM-DD-YYYY'))
from invoice
    then:
          if  &invoiceDate = TO_DATE('01-01-1900','MM-DD-YYYY')
             show 'invoicenum: ' &invoicenum ' is NULL'
          else
             show 'invoicenum: ' &invoicenum ' - Invoicedate: '
&invoiceDate
          end-if

Andrew

______________________________________
______________________________________
----- Original Message -----
From: Denise White <dewhite@VICR.COM>
To: <SQR-USERS@list.iex.net>
Sent: Friday, January 25, 2002 4:17 PM
Subject: Re: Odd Error: Month must be between 1 and 12


> Hi Andrew,
>
> Is it possible that it is actually the row after 1052 that is causing the
> problem?  If the program were detecting an error in the show statement for
1052,
> I would have thought that it would not have performed any part of that
show
> statement, and it is clearly doing the show.  I might be wrong, though.
Or it
> could possibly be a statement after the show - I have found that the line
> numbers in the error messages are not always 100% accurate.  I don't
really
> agree with the other responses you have gotten that the date is not null,
as the
> first select you are indicating only selects it if the date is null, so
that
> seems to me to prove that it is indeed null.
>
> Denise White
> Sr. Programmer/Analyst
> Vicor
> ------------------------------
>
> Date:    Thu, 24 Jan 2002 15:21:52 +0000
> From:    Andrew Rivers <andrew.rivers@CWCOM.NET>
> Subject: Odd Error:  Month must be between 1 and 12
>
> Hi
> I get the error:
> Error on line 1377:
>    (SQR 1925) Month must be between 1 and 12.
>
> Line 1377 is as follows (all on one line):
>     show 'invoicenum: ' &invoicenum ' - Invoicedate: '  &invoiceDate
>
> The problem is a NULL value for &invoiceDate in the database.
>      select invoicenum, invoicedate from invoice
>             where invoicenum = '1052' and invoicedate is null;
> This returns:
>      INVOICENUM     INVOICEDATE
>      ========== ====================
>      1052
>
>      1 ROW SELECTED
> showing that invoicenum = '1052' is NULL.
>
> However, if the following code is run:
>       if  isnull( &invoiceDate )
>          show 'invoicenum: ' &invoicenum ' is NULL'
>       else
>          show 'invoicenum: ' &invoicenum ' - Invoicedate: '  &invoiceDate
>       end-if
>
> the else statement is executed for invoicenum = '1052' and the program
> terminates at:
>       invoicenum: 1052 - Invoicedate:
>
> Other fields including date fields do not give problems.
>
> If anyone has any suggestion as to the problem, I would be most grateful.
>
> Andrew