Re: Date failing issue in SQL

On 13-Oct-2016 08:53 -0500, Hoteltravelfundotcom wrote:

On 13-Oct-2016 08:20 -0500, Hoteltravelfundotcom wrote:

I have this SQL query that has been halting since 9/28/16 during the
crystal report run, it was working fine for over 2 years. The error
getting is this:

'SQL0181 Value in Date, Time, or Timestamp not valid Database
vendor code -181'

sqlcode=-181 msg SQL0181

I checked against this date IATRND, via a query/400 and see nothing
funky.

And that column name was derived from wheree already? If mentioned in
the above message, that was omitted. And now having been mentioned, what
are the attributes; i.e. what was the DDL to define that column? When
describing an issue with an expression involving a column, the actual
data-type and precision [and scale and for date/time representations, then
also the /format/ of the data the column represents] are important to
reveal to avoid a reader having to guess or infer -- thus likely prolonging
the agony of extracting the details from the OP that easily could have been
there initially.

Is there a way to show me which particular date and order # is the
problem?

Yes. Ask for a revised version of that expression [shown in the WHERE
clause on an equal predicate shown just below in the quoted text that was
not snipped] in the select-list, by having removed the DATE casting scalar
*plus* either removing that predicate comparing with current_date or
changing the expression to remove the DATE casting scalar in conjunction
with replacing the current_date with a string literal such as
'2016-10-13'. Then a report for the query will show the date-strings
instead of trying to generate DATE data-typed values.

However depending on the amount of data, that may not be very helpful,
because visual inspection can be tedious. So better to wrap the expression
in a scalar with a User Defined Function (UDF) with a function implied by
the name, of IS_DATE or ISDATE; a UDF providing the ability to both present
an indication that the data is not valid for producing a DATE and the
ability to add a predicate on the WHERE clause to select *only* those
values which can not produce a valid DATE. The logic for testing valid
dates is not as simple as with using a UDF, within an actual query; I have
posted past examples however, of how to exhaustively validate all of the
components of a date-like value.

[…]
and date(varchar( integer(T01.IATRND/10000)) || '-' ||
varchar( integer(T01.IATRND/100)
- integer(T01.IATRND/10000) * 100 ) || '-' ||
varchar(mod(T01.IATRND, 100))
) = current date

Why such a complicated expression for what appears to be such a simple
task? The order of the date-components [i.e. YYYY, MM, DD] that is implied
by the arithmetic expression shown, is YYYY, MM, and DD; i.e. from the
expression can be inferred, that the column IATRND is [of at least] an
8-digit number in the format YYYYMMDD.

As Birgitta showed, there is a much simpler expression to cast such a
column to a TIMESTAMP [which can compare to a DATE such as the CURRENT DATE
special register]; revised here, to be /safer/ per ensuring exactly an
8-digit value cast into character using the DIGITS casting scalar, and done
so, per lack of the DDL offered in the OP:

Date( Digits( DEC(T01.IATRND, 8) ) concat '000000'))

And as Rob suggests, although performing such a casting on the column
can be functional, that does not necessarily imply also that the request is
practical or favorable. As he alludes, the better option is to cast the
CURRENT_DATE into whatever matches the DDL [and date-like formatting] for
the column IATRND; e.g. assuming the DDL was `IATRND INTEGER`, then the
predicate in the WHERE-clause could be better expressed with one of the
following [though NB, according to the comments]:

T01.IATRND = INT( current date ) /* for use on release and maintenance
levels for which date casting to numeric [implicitly, in the format
YYYYMMDD] is supported */

T01.IATRND = INT( REPLACE( CHAR(current_date,ISO), '-', '' ) )

Some of the rows are this:

TRANSACTION DATE: IATRND = 0

is this the issue?

Easy. Anywhere in an expression where IATRND is referenced, just
replace any *one occurrence* with: NULLIF(IATRND, 0)

For example, code `integer(NULLIF(T01.IATRND, 0)/100)` in place of what
is shown coded currently as `integer(T01.IATRND/100)'

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.