RE: DB2 SQL and MAPICS Dates (100+YY)MMDD

Yes but you can actually use the function in the SQL.  Here's an excerpt
from the IBM DB2 manual (DB2 UDB for iSeries SQL Programming Concepts V5R2):

A user-defined function (UDF) is a subroutine that performs a distinct
function. This program can be called like any other system function. 

Suppose that you wanted a function that returns the square of a number. The
query statement is: 

SELECT SQUARE(myint) FROM mytable

The following example shows how to define the UDF via SQL. 

Using an SQL function 
CREATE FUNCTION SQUARE( inval INT) RETURNS INT
LANGUAGE SQL
BEGIN
RETURN(inval*inval);
END

-----Original Message-----
From: mandis-inc@xxxxxxx [mailto:mandis-inc@xxxxxxx] 
Sent: Thursday, September 22, 2005 3:10 PM
To: MAPICS ERP System Discussion
Subject: RE: [MAPICS-L] DB2 SQL and MAPICS Dates (100+YY)MMDD

Would that be like a stored procedure that would be called?
--
Best regards, 

Mike Dreimiller 
Mandis Inc. 
410-526-5996 
Mandis-Inc@xxxxxxx


-------------- Original message from Lisa.Thomas@xxxxxxxxxxx: --------------



> You could create an SQL User defined function that masks the complexity. 
> 
> -----Original Message----- 
> From: mandis-inc@xxxxxxx [mailto:mandis-inc@xxxxxxx] 
> Sent: Thursday, September 22, 2005 2:55 PM 
> To: MAPICS-L@xxxxxxxxxxxx 
> Subject: [MAPICS-L] DB2 SQL and MAPICS Dates (100+YY)MMDD 
> 
> MAPICS holds dates in the (100+yy)MMDD format. To convert the MAPICS date 
> format to an an sql date format I am using this statement: SELECT 
> DATE(concat(concat(concat(substr(char((C6ALDT-1000000)+20000000),1,4),
'-'), 
> concat(substr(char((C6ALDT-1000000)+20000000),5,2), '-')), 
> substr(char((C6ALDT-1000000)+20000000),7,2))) FROM MBC6REP. Lengthly, but 
> it works. 
> 
> To select records by comparing dates, I am using a statement such as:
SELECT 
> DATE(concat(concat(concat(substr(char((C6ALDT-1000000)+20000000),1,4),
'-'), 
> concat(substr(char((C6ALDT-1000000)+20000000),5,2), '-')), 
> substr(char((C6ALDT-1000000)+20000000),7,2))) FROM MBC6REP WHERE 
> DATE(concat(concat(concat(substr(char((C6ALDT-1000000)+20000000),1,4),
'-'), 
> concat(substr(char((C6ALDT-1000000)+20000000),5,2), '-')), 
> substr(char((C6ALDT-1000000)+20000000),7,2))) > 
> While this sql select statement works, there must be a simpler way. I
would 
> appreciate any and all comments/suggestions. 
> 
> In advance, thanks 
> -- 
> Best regards, 
> 
> Mike Dreimiller 
> Mandis Inc. 
> 410-526-5996 
> Mandis-Inc@xxxxxxx 
> _______________________________________________ 
> This is the MAPICS ERP System Discussion (MAPICS-L) mailing list 
> To post a message email: MAPICS-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/mapics-l 
> or email: MAPICS-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/mapics-l. 
> 
> _______________________________________________ 
> This is the MAPICS ERP System Discussion (MAPICS-L) mailing list 
> To post a message email: MAPICS-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/mapics-l 
> or email: MAPICS-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/mapics-l. 
> 
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.