Some time back I had posted a message looking for a function to convert a
date column into a decimal(8,0) value. There didn't seem to be a built in
function, so I've created the following UDF:
CREATE FUNCTION FB_FUNC.DATE_TO_DEC ( date_in DATE )
RETURNS DECIMAL(8,0)
SPECIFIC FB_FUNC.date_to_dec
F1:
BEGIN ATOMIC
RETURN DECIMAL(TRANSLATE('EFGHABCD',CHAR(date_in,USA),'AB/CD/EFGH'));
END
I can now do something like
SELECT POST_DATE,
FB_FUNC.DATE_TO_DEC(POST_DATE) AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS WHERE BRCH_NBR = 123 AND ACCT_NBR = 1234567
and get back
POST_DATE POST_DATE_D
---------- -----------
08/23/2006 20060823.
08/24/2006 20060824.
08/11/2006 20060811.
This works fine and gives me what I want. I just want to make sure I'm not
missing something that would make it even simpler. Thoughts?
(Now to write DEC_TO_DATE...)
Oh, one other question... Unless the user's name is FB_FUNC, it appears the
UDF has to be qualified to call it. Is there any way around this?
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
10  1469 
Frank Swarbrick wrote:
Oh, one other question... Unless the user's name is FB_FUNC, it appears the
UDF has to be qualified to call it. Is there any way around this?
SET PATH = CURRENT PATH, FB_FUNC
Many client interfaces allow you to initialize the PATH during connect
set up.
E.g. the cli.ini file
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference http://www.iiug.org/waiug/present/Fo...Forum2006.html
Frank Swarbrick wrote:
Some time back I had posted a message looking for a function to convert a
date column into a decimal(8,0) value. There didn't seem to be a built in
function, so I've created the following UDF:
CREATE FUNCTION FB_FUNC.DATE_TO_DEC ( date_in DATE )
RETURNS DECIMAL(8,0)
SPECIFIC FB_FUNC.date_to_dec
F1:
BEGIN ATOMIC
RETURN DECIMAL(TRANSLATE('EFGHABCD',CHAR(date_in,USA),'AB/CD/EFGH'));
END
I can now do something like
SELECT POST_DATE,
FB_FUNC.DATE_TO_DEC(POST_DATE) AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS WHERE BRCH_NBR = 123 AND ACCT_NBR = 1234567
and get back
POST_DATE POST_DATE_D
---------- -----------
08/23/2006 20060823.
08/24/2006 20060824.
08/11/2006 20060811.
This works fine and gives me what I want. I just want to make sure I'm not
missing something that would make it even simpler. Thoughts?
(Now to write DEC_TO_DATE...)
Oh, one other question... Unless the user's name is FB_FUNC, it appears the
UDF has to be qualified to call it. Is there any way around this?
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
A few comments.
SPECIFIC FB_FUNC.date_to_dec
Listing the SCHEMA name inside SPECIFIC is redundant, since it by
default is the same as the one listed earlier, and cannot be different:
<<<<
If no qualifier is specified, the qualifier that was used for
function-name is used. If a qualifier is specified, it must be the same
as the explicit or implicit qualifier of function-name or an error is
raised (SQLSTATE 42882).
<<<<
F1:
BEGIN ATOMIC
The BEGIN ATOMIC is redundant. It is a single statement that is alos a
RETURN. IIRC, Knut or Serge mentioned some time back that not starting
as block is better because of some inlining something can do.
It may also be good to specify DETERMINISTIC, since you are providing
the date, and every date always has the same RETURN.
NO EXTERNAL ACTION
<<<<
This optional clause specifies whether or not the function takes some
action that changes the state of an object not managed by the database
manager. By specifying NO EXTERNAL ACTION, the system can use certain
optimizations that assume functions have no external impacts.
<<<<
CONTAINS SQL
If you do not specify CONTAINS SQL, it defaults to READS SQL DATA,
which can sometimes be a problem.
Finally, and this is completely stylistic, i usually use IN_ as a
prefix to PROCEDURE and FUNCTION arguments, as opposed to a suffix of
_IN. I think the former is more common.
So, i'd rewrite the FUNCTION as:
CREATE FUNCTION FB_FUNC.DATE_TO_DEC (IN_Date DATE)
RETURNS DECIMAL(8,0)
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
SPECIFIC Date_To_Dec
RETURN DECIMAL(TRANSLATE('EFGHABCD',CHAR(IN_Date, USA),'AB/CD/EFGH'))
B.
Serge Rielau<sr*****@ca.ibm.com12/13/06 12:21 PM >>>
>Frank Swarbrick wrote:
>Oh, one other question... Unless the user's name is FB_FUNC, it appears
the
>UDF has to be qualified to call it. Is there any way around this?
SET PATH = CURRENT PATH, FB_FUNC
Many client interfaces allow you to initialize the PATH during connect
set up.
E.g. the cli.ini file
Thanks!
Unfortunately DB2/VSE does not appear to support the SET PATH statement,
even when I'm connected to an LUW database. Hopefully I can find a way
around this...
Oh, BTW, a co-worker pointed out that DECIMAL(POST_DATE) (where POST_DATE is
a DATE column) works for what I was trying to do. Maybe it was the reverse
(DECIMAL to DATE) that I was not able to find? Hmm, I so confused. :-)
Thanks,
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Brian Tkatch<Ma***********@ThePentagon.com12/13/06 12:39 PM >>>
>Frank Swarbrick wrote:
A few comments.
> SPECIFIC FB_FUNC.date_to_dec
Listing the SCHEMA name inside SPECIFIC is redundant, since it by
default is the same as the one listed earlier, and cannot be different:
<<<<
If no qualifier is specified, the qualifier that was used for
function-name is used. If a qualifier is specified, it must be the same
as the explicit or implicit qualifier of function-name or an error is
raised (SQLSTATE 42882).
<<<<
>F1:
BEGIN ATOMIC
The BEGIN ATOMIC is redundant. It is a single statement that is alos a
RETURN. IIRC, Knut or Serge mentioned some time back that not starting
as block is better because of some inlining something can do.
It may also be good to specify DETERMINISTIC, since you are providing
the date, and every date always has the same RETURN.
NO EXTERNAL ACTION
<<<<
This optional clause specifies whether or not the function takes some
action that changes the state of an object not managed by the database
manager. By specifying NO EXTERNAL ACTION, the system can use certain
optimizations that assume functions have no external impacts.
<<<<
CONTAINS SQL
If you do not specify CONTAINS SQL, it defaults to READS SQL DATA,
which can sometimes be a problem.
Finally, and this is completely stylistic, i usually use IN_ as a
prefix to PROCEDURE and FUNCTION arguments, as opposed to a suffix of
_IN. I think the former is more common.
So, i'd rewrite the FUNCTION as:
CREATE FUNCTION FB_FUNC.DATE_TO_DEC (IN_Date DATE)
RETURNS DECIMAL(8,0)
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
SPECIFIC Date_To_Dec
RETURN DECIMAL(TRANSLATE('EFGHABCD',CHAR(IN_Date, USA),'AB/CD/EFGH'))
Thanks. I used the UDF wizard in the Control Center, and it generated the
schema on the specific name as well as the ATOMIC clause.
Anyway, I will make the changes you recommend.
Thanks!
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Just forget all the bullshit discussed above.
All you need to do is
SELECT POST_DATE,
DEC( HEX(POST_DATE), 8, 0 ) AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 123
AND ACCT_NBR = 1234567
That's it.
Never try to make things 100 times more complicated than they really
are.
Good luck.
"""Frank Swarbrick ΠΙΣΑΜ(Α):
"""
Some time back I had posted a message looking for a function to convert a
date column into a decimal(8,0) value. There didn't seem to be a built in
function, so I've created the following UDF:
CREATE FUNCTION FB_FUNC.DATE_TO_DEC ( date_in DATE )
RETURNS DECIMAL(8,0)
SPECIFIC FB_FUNC.date_to_dec
F1:
BEGIN ATOMIC
RETURN DECIMAL(TRANSLATE('EFGHABCD',CHAR(date_in,USA),'AB/CD/EFGH'));
END
I can now do something like
SELECT POST_DATE,
FB_FUNC.DATE_TO_DEC(POST_DATE) AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS WHERE BRCH_NBR = 123 AND ACCT_NBR = 1234567
and get back
POST_DATE POST_DATE_D
---------- -----------
08/23/2006 20060823.
08/24/2006 20060824.
08/11/2006 20060811.
This works fine and gives me what I want. I just want to make sure I'm not
missing something that would make it even simpler. Thoughts?
(Now to write DEC_TO_DATE...)
Oh, one other question... Unless the user's name is FB_FUNC, it appears the
UDF has to be qualified to call it. Is there any way around this?
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Frank Swarbrick wrote:
Some time back I had posted a message looking for a function to convert a
date column into a decimal(8,0) value. There didn't seem to be a built in
function, so I've created the following UDF:
[...]
This works fine and gives me what I want. I just want to make sure I'm not
missing something that would make it even simpler. Thoughts?
Why not just, DECIMAL(INT(POST_DATE),8,0) ? This looks simpler to me,
but I'm not sure if DB2 would agree.
$ db2 "values DECIMAL(INT(CURRENT DATE),8,0)"
1
----------
20061213.
As for the reverse (decimal to date), try:
date(translate('abcd-ef-gh', char(post_date_dec),'abcdefgh'))
-Chris
Frank Swarbrick wrote:
Oh, one other question... Unless the user's name is FB_FUNC, it appears
the
UDF has to be qualified to call it. Is there any way around this?
Personally, I prefer to _always_ qualify your functions that way. The
advantage is that you can be sure (as developer) which function is called
and a different function path setting does not start to interfere.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Actually, I found out it's even easier:
SELECT POST_DATE,
DECIMAL(POST_DATE) AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 123
AND ACCT_NBR = 1234567
No there is the issue of 'going the other direction. Here's what I came up
with:
CREATE FUNCTION FB_FUNC.TO_DATE ( date_in DECIMAL(9,0) )
RETURNS DATE
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
SPECIFIC dec_to_date
RETURN CASE
-- Gregorian date?
WHEN date_in 19000000 AND date_in < 29000000 THEN
DATE(TRANSLATE('AB/CD/EFGH',CHAR(CAST(date_in AS INTEGER)),'EFGHABCD'))
-- Julian date?
WHEN date_in < 10000000 AND date_in >= 1000000 THEN
DATE(RIGHT(DIGITS(date_in),7))
-- Bad date!
ELSE NULL
END
Then:
EXEC SQL
INSERT INTO FILM.FILM_TRANSACTIONS (
BRCH_NBR, ACCT_NBR, LAST_STATEMENT_DATE,
POST_DATE, AMOUNT, SERIAL_NBR,
SEQUENCE_NBR, POST_FLAG
)
VALUES (
:FMST-BRANCH-NBR, :FMST-ACCT-NBR,
FB_FUNC.TO_DATE(:FSTM-LAST-DATE-JUL),
FB_FUNC.TO_DATE(:FTRN-POST-DATE-JUL),
:FTRN-AMOUNT, :FTRN-SERIAL-NBR,
:FTRN-SEQ-NBR, :FTRN-POST-FLAG
)
END-EXEC
Yes, strangely enough the program that inserts the date has it in 'Julian'
format (YYYYDDD), but the program retrieving it wants it in Gregorian. My
TO_DATE function handles both.
Thanks for the input,
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>SVK<se************@gmail.com12/13/06 2:45 PM >>>
Just forget all the bullshit discussed above.
All you need to do is
SELECT POST_DATE,
DEC( HEX(POST_DATE), 8, 0 ) AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 123
AND ACCT_NBR = 1234567
That's it.
Never try to make things 100 times more complicated than they really
are.
Good luck.
Knut Stolze<st****@de.ibm.com12/14/06 1:48 AM >>>
>Frank Swarbrick wrote:
>Oh, one other question... Unless the user's name is FB_FUNC, it appears
the
UDF has to be qualified to call it. Is there any way around this?
>Personally, I prefer to _always_ qualify your functions that way. The
advantage is that you can be sure (as developer) which function is called
and a different function path setting does not start to interfere.
Good reasoning. I will take it under advisement. :-)
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA This thread has been closed and replies have been disabled. Please start a new discussion. |
