我正在使用VB .NET编写一个ASP.NET Web窗体页。我正在编写用于折线图的代码,但是我似乎无法使查询按周分组。下面是我的查询:
SELECT F42119LA.SDMCU || '-' || F42119LA.SDLNTY AS BranchCode,
AVG(F42119la.SDIVD-F42119LA.SDDRQJ) AS Days,
WEEK(SDTRDJ) AS Day
FROM KAI400.KAIPRDDTA.EXCHBYDATE EXCHBYDATE,
KAI400.KAIPRDDTA.F42119L14 F42119LA
WHERE F42119LA.SDBCRC = EXCHBYDATE.CXCRCD
AND EXCHBYDATE.EXCHDATE = F42119LA.SDTRDJ
AND F42119LA.SDTRDJ>='118006'
AND F42119LA.SDTRDJ<='118096'
AND F42119LA.SDNXTR<>'999'
AND SDIVD <> 0
AND SDDRQJ <> 0
AND F42119LA.SDAEXP <> 0
AND EXCHBYDATE.CXCRDC='USD'
AND F42119LA.SDLNTY IN ('S','W')
AND (SDMCU LIKE '%100' OR SDMCU LIKE '%150')
GROUP BY SDMCU,
SDLNTY,
SDIVD,
F42119LA.SDMCU || '-' || F42119LA.SDLNTY,
WEEK(SDTRDJ)
ORDER BY SDIVD,
SDMCU,
SDLNTY
这是SQL字符串运行的代码:
Public Shared Function GetMyDataTableString(SqlString As String, Optional IncDb As Integer = 0) As DataTable
Dim MyConn As OleDbConnection = GetMyConn(IncDb)
Dim DbCmd As New OleDbCommand(SqlString, MyConn)
Dim ReturnDataTable As New DataTable
Try
If Not MyConn.State = ConnectionState.Open Then
MyConn.Open()
End If
Dim Reader As OleDbDataReader = DbCmd.ExecuteReader(CommandBehavior.CloseConnection)
Using Reader
ReturnDataTable.Load(Reader)
End Using
Catch ex As Exception
LogSqlErrors(SqlString, "GetMyDataTableString " & ex.Message.ToString(), IncDb)
If HttpContext.Current.Session("SITEADMIN") = "True" Then
HttpContext.Current.Response.Write("<b>OleFun.GetMyDataTableString, datatable failed</b>---<br />" & ex.ToString)
End If
Finally 'Happens regardless of failing or succeeding
MyConn.Close()
End Try
Return ReturnDataTable
End Function
每当我使用WEEK()时,它都会给出以下错误:
Value in date, time, or timestamp string not valid
ONDATE是一个日期字段,格式为MM/DD/YYYY。有人知道按周分组的其他方法吗?或者是什么原因导致了这个错误?提前感谢您的回复。
3条答案
按热度按时间waxmsbnn1#
zour9fqk2#
DATEPART()
可能是SQL Server中的函数,但不是Db2中的函数。您可以使用WEEK()
或WEEK_ISO()
,它们都是Db2函数。https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0005481.html如果您使用的是最新版本的Db2 LUW https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0053629.html,也可以使用
EXTRACT
顺便说一句,您不需要按
F42119LA.SDMCU || '-' || F42119LA.SDLNTY
的连接进行分组,您可以按这些列单独分组,并且只在SELECT中进行连接。neekobn83#
在DB2中,可以使用以下方法对同一周中的几天进行分组:
备注: