WEEK_ISO in SQL

[B.Hauser]

Hallo Leute,

in News400 Januar waren Beispiele, wie man Datums-Funktionen aus SQL in RPG nutzen kann.
Hat jemand das mal ausprobiert?
Beim Einsatz der Skalaren Funktion WEEK_ISO()bekomme ich einen Umwandlungs-Fehler:
SQL1105 : Benutzerdefinierte Art oder Funktion WEEK_ISO in *LIBL nicht gefunden.
Wir arbeiten mit Release V5R1M0. Laut SQL-Referenz gibt es die Funktion WEEK_ISO() ab Release V5R1M0.

Übrigens müssen auch alle Host-Variablen, die in Skalaren Funktionen benutzt werden, durch : gekennzeichnet werden!
(Dies ist sowohl in News400 als auch im Download falsch!)
Der korrekte Befehl zur Ermittlung des Wochentages muss wie folgt lauten:
C/Exec SQL
C+ Set http://www.as400-forum.de/ubb/biggrin.gifayOfWeekNbr = DayOfWeek( http://www.as400-forum.de/ubb/biggrin.gifateIn )
C/End-Exec
Birgitta

[Oberelch]

Hallo Birgitta,

habe bei IBM folgendes gefunden:

APAR#: SE02777
Component: 5722SS1DB - OS/400 DATABASE
Release(s): R510

Abstract
SQL-CMPL-CURR-MSGSQL1105 NEW JULIAN_DAY, DAYOFWEEK_ISO, WEEK_ISO SCALAR FUNCTIONS DO NOT WORK IN EMBEDDED SQL PROGRAMS

Error Description
******* (Do NOT alter/erase this or next 3 lines) *******
* EQUIVALENT ABSTRACT:
SQL-CMPL-CURR-MSGSQL1105 NEW JULIAN_DAY_ DAYOFWEEK_ISO_ WEEK_ISO SCALAR FUNCTIONS DO NOT WORK IN EMBEDDED SQL PROGRAMS

When compiling embedded SQL C and embedded SQL RPGIV programs with the new SQL scalar functions julian_day, dayofweek_iso and
week_iso program is created. But when called programs fail with MSGSQL0204 - <scalar function> in *LIBL type *N not found.

In the spool file created from the CRTSQLCI command you can see the MSGSQL1105 - User defined type or function <scalar function>
in *LIBL not found warning message.

Problem Summary
When compiling embedded SQL C and embedded SQL RPGIV programs with the new SQL scalar functions julian_day, dayofweek_iso and
week_iso program is created. But when called programs fail with MSGSQL0204 - <scalar function> in *LIBL type *N not found.
In the spool file created from the CRTSQLCI command you can see the MSGSQL1105 - User defined type or function <scalar function>
in *LIBL not found warning message.

Problem Conclusion
MSGSQL1105 and MSGSQL0204 are issued for the functions DAYOFWEEK_ISO, WEEK_ISO, JULIAN_DAY, MIDNIGHT_SECONDS, PI, TIMESTAMPDIFF and SPACE either at precompile time or when a program is called. With the PTF, the functions will be found as long as valid arguments are specified.

Temporary Fix

Comments

Circumvention
None.

PTFs Available
R510 SI02174 2036

Affected Modules
ROCSMGR

Affected Publications

Summary Information
Status.... CLOSED PER
HIPER..... No
Component. 5722SS1DB
Failing Module. ROCSMGR
Reported Release.R510
Duplicate Of
IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright (c) 1994, 1995, 1996,1997,1998, 1999, 2000, 2001, 2002 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult your product manuals for complete trademark information.