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)'
midrange.com
