Re: Another SQL newbie question

On 12-May-2011 20:35 , James Lampert wrote:

CRPence wrote:

<<SNIPped was a comment on 100-year window rules and
"if the UDF might have needed to use some date strings,
then best to also, just before the "begin", add the line:
SET OPTION DATFMT=*ISO
">>

Note that the CHAR scalar\cast function allows a second parameter to
map a date to character in the ISO form [for example] similarly to allow
the smaller or larger dates to avoid the date underflow and date
overflow conditions. So in the interface used to test the UDF, instead
of finding the session attribute to set date formats to ISO, use instead
the following UDF invocation:
CHAR( DISPLAYDAT( xxx ) , ISO )
instead of:
DISPLAYDAT( xxx )

Sometimes my preference would be to return CAST(NULL AS DATE) for an
input of zero, or in response to an error for any bad inputs like
20010229 or 99999999. In that case adding a HANDLER to the SQL function
to directly RETURN that, or return the DATE variable when that can be
set without an error.

No, that's not it at all: it's failing IN the UDF, which is being
called in the SELECT that defines a VIEW, NOT as a SELECT pouring data
into a TABLE. In every single case in which it doesn't blow up, the
date is displayed as an ISO date, whether the view is being opened as
a SELECT * on the STRSQL command line, or it's being opened through
native RLA (specifically, from QuestView).

Not sure what "that" is referring to. The text of mine that remained quoted in the above quoted reply was merely additional information, a "note" not directly part of any of the recommendation given for what might prevent the condition; just ways either to produce a date string from a date and my preference on how best to deal with invalid dates or date underflow and overflow conditions. Anyhow...

If the problem is that "it's failing IN the UDF", then perhaps the omitted text which suggested "SET OPTION DATFMT=*ISO has relevance even though I had alluded that should have been unnecessary? I see no reason for the UDF to need that option enabled, because there is only TIMESTAMP cast to DATE in the UDF, but perhaps something funky about the generated SQL which actually requires that option to be specified. Though for lack of knowing what input values will cause the failure and what the failing exceptions and\or sqlstate and sqlcode values are, the date format may not even be relevant. Perhaps debug is in order; then adding a desirable DBGVIEW on that SET OPTION might also have value.?

Regards, Chuck