date_to_decimal [英] date_to_decimal
问题描述
回过头来，我发布了一条消息，寻找将
日期列转换为小数（8,0）值的函数。似乎没有内置的
函数，所以我创建了以下UDF：
CREATE FUNCTION FB_FUNC.DATE_TO_DEC（date_in日期
返回DECIMAL（8,0）
SPECIFIC FB_FUNC.date_to_dec
F1：
BEGIN ATOMIC
RETURN DECIMAL（翻译（'''EFGHABCD''，CHAR（date_in，USA），''AB / CD / EFGH''））;
END
我现在可以做类似
SELECT POST_DATE，
FB_FUNC.DATE_TO_DEC（POST_DATE）AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS，其中BRCH_NBR = 123 AND ACCT_NBR = 1234567
并取回
POST_DATE POST_DATE_D
- -------- -----------
08/23/2006 20060823.
08/24/2006 20060824.
08/11/2006 20060811.
这很好用，给了我想要的东西。我只是想确保我不会丢失一些能让它变得更简单的东西。想法？
（现在写DEC_TO_DATE ......）
哦，另外一个问题......除非用户的名字是FB_FUNC，看来
UDF必须有资格调用它。有没有办法解决这个问题？
Frank
---
Frank Swarbrick
Senior开发人员/分析师 - 大型机应用程序
FirstBank数据公司 - 美国科罗拉多州莱克伍德
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写道：
Frank Swarbrick wrote:
哦，另外一个问题......除非用户的名字是FB_FUNC，否则看起来
UDF必须有资格调用它。有没有办法解决？
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
许多客户端接口允许你在连接期间初始化PATH
设置。
例如cli.ini文件
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
WAIUG会议
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Frank Swarbrick写道：
Frank Swarbrick wrote:
一段时间后我发布了一条消息，寻找一个函数将
日期列转换为小数（8,0）值。似乎没有内置的
函数，所以我创建了以下UDF：
CREATE FUNCTION FB_FUNC.DATE_TO_DEC（date_in日期
返回DECIMAL（8,0）
SPECIFIC FB_FUNC.date_to_dec
F1：
BEGIN ATOMIC
RETURN DECIMAL（翻译（'''EFGHABCD''，CHAR（date_in，USA），''AB / CD / EFGH''））;
END
我现在可以做类似
SELECT POST_DATE，
FB_FUNC.DATE_TO_DEC（POST_DATE）AS POST_DATE_D
FROM FILM.FILM_TRANSACTIONS，其中BRCH_NBR = 123 AND ACCT_NBR = 1234567
并取回
POST_DATE POST_DATE_D
- -------- -----------
08/23/2006 20060823.
08/24/2006 20060824.
08/11/2006 20060811.
这很好用，给了我想要的东西。我只是想确保我不会丢失一些能让它变得更简单的东西。想法？
（现在写DEC_TO_DATE ......）
哦，另外一个问题......除非用户的名字是FB_FUNC，看来
UDF必须有资格调用它。有没有办法解决这个问题？
Frank
---
Frank Swarbrick
高级开发人员/分析师 - 大型机应用程序
FirstBank数据公司 - 美国科罗拉多州莱克伍德
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
SPECIFIC FB_FUNC.date_to_dec
在SPECIFIC中列出SCHEMA名称是多余的，因为它由
默认值与前面列出的相同，并且不能相同：
<<<<
如果未指定限定符，使用用于
函数名的限定符。如果指定了限定符，则它必须与函数名的显式或隐式限定符相同
或者错误是
引发（SQLSTATE 42882）。
<<<<
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
F1:
BEGIN ATOMIC
BEGIN ATOMIC是多余的。这是一个单一的声明，即
RETURN。 IIRC，Knut或Serge提到一段时间后没有开始
因为一些内联可以做什么更好块。
它也可能是好的指定DETERMINISTIC，因为你提供了
的日期，并且每个日期总是有相同的返回。
没有外部行动
<<<<
此可选子句指定函数是否采取一些
操作来更改未由其管理的对象的状态数据库
经理。通过指定NO EXTERNAL ACTION，系统可以使用某些
优化，假设函数没有外部影响。
<<<<
包含SQL
如果你没有指定CONTAINS SQL，它默认为READS SQL DATA，
这有时可能是个问题。
最后，这是完全风格的，我通常使用IN_作为PROCEDURE和FUNCTION参数的
前缀，而不是后缀
_IN。我认为前者更常见。
所以，我将把函数重写为：
CREATE FUNCTION FB_FUNC.DATE_TO_DEC（IN_Date日期
退货十分（8,0）
确定性
无外部行动
包含SQL
SPECIFIC Date_To_Dec
RETURN DECIMAL（翻译（''EFGHABCD''，CHAR（IN_Date，USA），''AB / CD / EFGH''））
B.
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 >>>
Serge Rielau<sr*****@ca.ibm.com12/13/06 12:21 PM >>>
> Frank Swarbrick写道：
>Frank Swarbrick wrote:
>哦，另外一个问题......除非用户的名字是FB_FUNC，看起来是
>Oh, one other question... Unless the user''s name is FB_FUNC, it appears
the
> UDF必须有资格调用它。有没有办法解决？
>UDF has to be qualified to call it. Is there any way around this?
SET PATH = CURRENT PATH，FB_FUNC
许多客户端界面允许您在连接设置期间初始化PATH。
例如cli.ini文件
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
谢谢！
不幸的是DB2 / VSE似乎不支持SET PATH语句，
即使我连接到LUW数据库。希望我能找到一种方式
围绕这个...
哦，顺便说一下，同事指出DECIMAL（POST_DATE）（POST_DATE是
a DATE专栏）适用于我想要做的事情。也许是我无法找到的反向
（DECIMAL到DATE）？嗯，我很困惑。 :-)
谢谢，
弗兰克
---
弗兰克Swarbrick
高级开发人员/分析师 - 大型机应用程序
FirstBank数据公司 - 美国科罗拉多州莱克伍德
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
这篇关于date_to_decimal的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
