按周分组的DB2 SQL查询

l2osamch  于 2022-11-07  发布在  DB2
关注(0)|答案(3)|浏览(382)

我正在使用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。有人知道按周分组的其他方法吗?或者是什么原因导致了这个错误?提前感谢您的回复。

waxmsbnn

waxmsbnn1#

-- date part takes the part of the date as first paramater:
-- you have:
DATEPART(SDTRDJ, wk)

-- needs to be:
DATEPART(wk, SDTRDJ)
zour9fqk

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

db2 "values extract(WEEK FROM  current date)"

1          
-----------
         14

1 record(s) selected.

顺便说一句,您不需要按F42119LA.SDMCU || '-' || F42119LA.SDLNTY的连接进行分组,您可以按这些列单独分组,并且只在SELECT中进行连接。

neekobn8

neekobn83#

在DB2中,可以使用以下方法对同一周中的几天进行分组:

  1. DATE_PART('WEEK',dateColumn)-〉一周从星期六开始
  2. WEEK(dateColumn)-〉一周从星期日开始
  3. WEEK_ISO(dateColumn)-〉一周从星期一开始
  4. DAYS(dateColumn)/ 7 -〉一周从星期日开始
    备注:
  • 我相信它们可以像处理TIMESTAMP一样处理DATE类型的列。
  • “DAYS(dateColumn)/ 7”不能获得周数,但它有助于按周分组。
  • 请检查您的周开始日,因为它们的结果不同,如下所示:

相关问题