天道酬勤,学无止境

iseries-navigator

使用 SQL for iSeries 将多行值连接为 1 行(Concatenate Multiple Row Values into 1 Row, with SQL for iSeries)

问题 首先,我要感谢 Kent Milligan 和他在 http://www.mcpressonline.com/sql/techtip-combining-multiple-row-values-into-a-single-row-with-sql-in-db2- 上的文章for-i.html 让我尽可能地解决这个问题。 但现在我需要扩展他在这里所做的事情。 为避免您不得不阅读他的文章,他解决的问题是将多行中的字符串数据连接到结果表中的单行中。 例如: 桌车: 制作模型福特融合雪佛兰太浩本田奥德赛福特金牛座福特福克斯雪佛兰迈锐宝 结果: 制作模型雪佛兰马里布,太浩福特福克斯、金牛座、融合本田奥德赛 这是通过 SQL 语句完成的: WITH numbered_sets(make, model, curr, prev) AS ( SELECT make, model, ROW_NUMBER() OVER (PARTITION BY make ORDER BY model) AS curr, ROW_NUMBER() OVER (PARTITION BY make ORDER BY model) -1 AS prev FROM inventory) SELECT make, MAX (TRIM(L ',' FROM CAST(SYS_CONNECT_BY_PATH(model, '

2021-06-25 16:41:34    分类:技术分享    sql   ibm-midrange   iseries-navigator

在 db2 中使用动态表名(Using dynamic table name in db2)

问题 目前在我的项目开发中需要根据某些标准生成记录数,其中表名存储在单独的表中。例如说xx表存储列名下的表名是tableInfo。 我以这样的方式编写了存储过程 DECLARE FGCURSOR CURSOR FOR SELECT tableInfo FROM xx WHERE col1='PO'; OPEN FGCURSOR; FETCH FROM FGCURSOR INTO FILEGROUPMEM; WHILE SQLCODE <> 100 DO SET COUNTVal = 'SELECT COUNT(*) FROM ' || FILEGROUPMEM || ' WHERE ICLS= ' || CLASS || ' AND IVEN= ' || VENDOR || ' AND ISTY= ' || STYLE || ' AND ICLR= ' || COLOR || ' AND ISIZ= ' || SIZE ; IF(COUNTVal >= 1) THEN RETURN 1; END IF; FETCH FROM FGCURSOR INTO FILEGROUPMEM; END WHILE; CLOSE FGCURSOR; 执行程序时出现异常,说 消息:[SQL0420] CAST 参数中的字符无效。 原因 。 . . . . : CAST 函数的参数中的字符不正确。

2021-06-24 00:09:18    分类:技术分享    stored-procedures   db2   ibm-midrange   iseries-navigator

DB2 - How to run an ad hoc select query with a parameter in IBM System i Access for Windows GUI Tool

I would like to run some ad hoc select statements in the IBM System I Navigator tool for DB2 using a variable that I declare. For example, in the SQL Server world I would easily do this in the SQL Server Management Studio query window like so: DECLARE @VariableName varchar(50); SET @VariableName = 'blah blah'; select * from TableName where Column = @VariableName; How can I do something similar in the IBM System I Navigator tool?

2021-06-21 13:01:42    分类:问答    sql   db2   ibm-midrange   iseries-navigator

Concatenate Multiple Row Values into 1 Row, with SQL for iSeries

First, I need to thank Kent Milligan and his article at http://www.mcpressonline.com/sql/techtip-combining-multiple-row-values-into-a-single-row-with-sql-in-db2-for-i.html for getting me as far in this problem as I have. But now I need to expand on what he has done here. To avoid you having to go to his article, the problem he addressed was concatenating string data from multiple rows into a single row in the resulting table. For example: Table Cars: Make Model Ford Fusion Chevy Tahoe Honda Odyssey Ford Taurus Ford Focus Chevy Malibu Results: Make Model Chevy Malibu, Tahoe Ford Focus, Taurus

2021-05-03 08:28:17    分类:问答    sql   ibm-midrange   iseries-navigator

SQL0469 IN,OUT或INOUT对过程中的参数2无效(SQL0469 IN, OUT, or INOUT not valid for parameter 2 in procedure)

问题 我正在尝试对运行AS400的IBM iSeries运行存储过程,并在标题中遇到上述错误。 当我键入以下内容以从System iNavigator工具执行存储过程时,它运行良好: CALL QS36F.HH189P('1','1','') 在存储过程中,第一个参数方向定义为输入,第二个输出定义,第三个方向作为输出。 问题是当我尝试通过.Net代码设置参数来运行存储过程时。 有人可以帮帮我吗? 我的参数列表设置如下: DB2Command.Parameters.Add("P_STRRRN", iDB2DbType.iDB2Char, 10); DB2Command.Parameters["P_STRRRN"].Direction = System.Data.ParameterDirection.Input; DB2Command.Parameters["P_STRRRN"].Value = strRRN; DB2Command.Parameters.Add("P_LSTRRN", iDB2DbType.iDB2Char, 10); DB2Command.Parameters["P_LSTRRN"].Value = string.Empty; DB2Command.Parameters["P_LSTRRN"].Direction = System.Data

2021-04-30 18:29:25    分类:技术分享    .net   stored-procedures   parameters   db2-400   iseries-navigator

SQL0469 IN, OUT, or INOUT not valid for parameter 2 in procedure

I'm trying to run a stored procedure against an IBM iSeries running AS400 and getting the above error in my title. When I type in the following to execute the stored procedure from the System iNavigator tool, it runs fine: CALL QS36F.HH189P('1','1','') The first parameter direction is defined in the stored procedure as input, the second output, and the third as output. Problem is when I try to run the stored procedure from .Net code setting up the parameters. Can someone please help me? My parameter list is set up as follows: DB2Command.Parameters.Add("P_STRRRN", iDB2DbType.iDB2Char, 10)

2021-04-11 16:35:08    分类:问答    .net   stored-procedures   parameters   db2-400   iseries-navigator