db2look 导出的函数 function 无法执行，谁能看看是怎么回事
SET CURRENT SCHEMA = "F12SITST";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","F12SITST";
CREATE FUNCTION F12SITST.F_LOT_TYPE (IN_LOT_ID VARCHAR(64) ) RETURNS CHARACTER(16)
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA STATIC DISPATCH CALLED ON
NULL INPUT EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC return
with tmp(first,second,third) as ( select substr(IN_LOT_ID,locate('.',IN_LOT_ID)+1,1
), substr(IN_LOT_ID,locate('.',IN_LOT_ID)+2,1 ), substr(IN_LOT_ID,locate('.',IN_LOT_ID)+3,1
) from sysibm.sysdummy1 ) select (case when first='0' and second='0' and
third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and
third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1'
and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A'
and third<='Z' then 'REWORK' else 'UNKNOWN' end) as lot_type from tmp;
END;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","F12SITST";
CREATE FUNCTION F12SITST.F_LOT_TYPE (IN_LOT_ID VARCHAR(64) ) RETURNS CHARACTER(16)
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA STATIC DISPATCH CALLED ON
NULL INPUT EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC return
with tmp(first,second,third) as ( select substr(IN_LOT_ID,locate('.',IN_LOT_ID)+1,1
), substr(IN_LOT_ID,locate('.',IN_LOT_ID)+2,1 ), substr(IN_LOT_ID,locate('.',IN_LOT_ID)+3,1
) from sysibm.sysdummy1 ) select (case when first='0' and second='0' and
third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and
third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1'
and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A'
and third<='Z' then 'REWORK' else 'UNKNOWN' end) as lot_type from tmp;
END;
7同行回答
浏览1085
这是什么道理，为什么要加个--这呢收起
浏览1087
db2inpe@dmsrvr:~> db2 -tvf test.sql
with tmp(first,second,third) as ( select substr(ID,locate('.',ID)+1,1), substr(ID,locate('.',ID)+2,1 ), substr(ID,locate('.',ID)+3,1) from test ) select ( case when first='0' and second='0' and third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1' and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A' and third<='Z' then 'REWORK' else 'UNKNOWN' end ) as lot_type from tmp
LOT_TYPE
---------
0 record(s) selected.
db2inpe@dmsrvr:~> vi test.sql
db2inpe@dmsrvr:~> db2 -tvf test.sql
~ SET CURRENT SCHEMA = "F12SITST"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0007N The character "~" following "BEGIN-OF-STATEMENT" is not valid.
SQLSTATE=42601
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","F12SITST"
DB20000I The SQL command completed successfully.
CREATE FUNCTION F12SITST.F_LOT_TYPE (ID VARCHAR(64) ) RETURNS CHARACTER(16)
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA STATIC DISPATCH CALLED ON
NULL INPUT EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC return
with tmp(first,second,third) as ( select substr(ID,locate('.',ID)+1,1
), substr(ID,locate('.',ID)+2,1 ), substr(ID,locate('.',ID)+3,1
) from test ) select ( case when first='0' and second='0' and
third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and
third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1'
and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A'
and third<='Z' then 'REWORK' else 'UNKNOWN' end ) as xx from tmp
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "from tmp" was found following "UNKNOWN' end )
as xx". Expected tokens may include: "". LINE NUMBER=10.
SQLSTATE=42601
函数里边这么写有问题，貌似不支持吧。最好查infocenter确认下收起
with tmp(first,second,third) as ( select substr(ID,locate('.',ID)+1,1), substr(ID,locate('.',ID)+2,1 ), substr(ID,locate('.',ID)+3,1) from test ) select ( case when first='0' and second='0' and third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1' and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A' and third<='Z' then 'REWORK' else 'UNKNOWN' end ) as lot_type from tmp
LOT_TYPE
---------
0 record(s) selected.
db2inpe@dmsrvr:~> vi test.sql
db2inpe@dmsrvr:~> db2 -tvf test.sql
~ SET CURRENT SCHEMA = "F12SITST"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0007N The character "~" following "BEGIN-OF-STATEMENT" is not valid.
SQLSTATE=42601
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","F12SITST"
DB20000I The SQL command completed successfully.
CREATE FUNCTION F12SITST.F_LOT_TYPE (ID VARCHAR(64) ) RETURNS CHARACTER(16)
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA STATIC DISPATCH CALLED ON
NULL INPUT EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC return
with tmp(first,second,third) as ( select substr(ID,locate('.',ID)+1,1
), substr(ID,locate('.',ID)+2,1 ), substr(ID,locate('.',ID)+3,1
) from test ) select ( case when first='0' and second='0' and
third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and
third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1'
and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A'
and third<='Z' then 'REWORK' else 'UNKNOWN' end ) as xx from tmp
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "from tmp" was found following "UNKNOWN' end )
as xx". Expected tokens may include: "
SQLSTATE=42601
函数里边这么写有问题，貌似不支持吧。最好查infocenter确认下收起
浏览1065
补充一下db2的版本是db2v8收起
浏览1115
你一定是把“--”编辑掉了。
CREATE FUNCTION F12SITST.F_LOT_TYPE (IN_LOT_ID VARCHAR(64) ) RETURNS CHARACTER(1
6)
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA STATIC DISPATCH CALLED ON
NULL INPUT EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC return
with tmp(first,second,third) as ( select substr(IN_LOT_ID,locate('.',IN_LOT_ID)+
1,1
), substr(IN_LOT_ID,locate('.',IN_LOT_ID)+2,1 ), substr(IN_LOT_ID,locate('.',IN_
LOT_ID)+3,1
) from sysibm.sysdummy1 ) select (case when first='0' and second='0' and
third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and
third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1'
and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A'
and third<='Z' then 'REWORK' else 'UNKNOWN' end) as lot_type from tmp;--
END；
DB20000I The SQL command completed successfully.收起
CREATE FUNCTION F12SITST.F_LOT_TYPE (IN_LOT_ID VARCHAR(64) ) RETURNS CHARACTER(1
6)
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA STATIC DISPATCH CALLED ON
NULL INPUT EXTERNAL ACTION INHERIT SPECIAL REGISTERS BEGIN ATOMIC return
with tmp(first,second,third) as ( select substr(IN_LOT_ID,locate('.',IN_LOT_ID)+
1,1
), substr(IN_LOT_ID,locate('.',IN_LOT_ID)+2,1 ), substr(IN_LOT_ID,locate('.',IN_
LOT_ID)+3,1
) from sysibm.sysdummy1 ) select (case when first='0' and second='0' and
third='0' then 'PARENT' when first>='A' and first<='Z' and second='0' and
third='0' then 'SPLIT' when second>='A' and second<='Z' and third>='1'
and third<='9' then 'AUTOSPLIT' when second>='0' and second<='9' and third>='A'
and third<='Z' then 'REWORK' else 'UNKNOWN' end) as lot_type from tmp;--
END；
DB20000I The SQL command completed successfully.收起
浏览1075