Optional ByVal StartTime As Date = defDateTime, Optional ByVal EndTime As Date = defDateTime, Optional ByVal AdditionalXml As String = "") As String
Dim cnnTemp As OleDb.OleDbConnection
Dim daTemp As OleDb.OleDbDataAdapter
Dim dsTemp As DataSet
Dim drTemp As OleDb.OleDbDataReader
Dim strSQL As String
Dim StartEndTime As Double
Dim Rows() As DataRow
Try
'//Create connection
cnnTemp = objLic.CreateConnection(strConnectionString)
strSQL = "Select * From tblEQPStateBasis Order By EquipmentState"
cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp)
drTemp = cmmTemp.ExecuteReader()
strSQL = " Select (Select ContainareaNo From tblSMDAreaRelation Where ObjectNo = Z.EquipmentNo And ObjectType = 2) AreaNo," & _
" (Select EquipmentType From tblEQPEquipmentBasis Where EquipmentNo = Z.EquipmentNo) EquipmentType," & _
" Z.EquipmentNo," & _
" 0 Moves"
StartEndTime = DateDiff(DateInterval.Second, StartTime, EndTime)
Do While drTemp.Read
If strDataBaseType = "oracle" Then
strSQL = strSQL & " ,Nvl(Round(( Select Sum(Least(A.EndTime,To_Date('" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')) -" & _
" Greatest(A.StartTime,To_Date('" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')))*24*60*60" & _
" From (Select EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime From tblEMSEquipmentStateLog" & _
" UNION All" & _
" Select EquipmentType, EquipmentNo, EquipmentState, StartTime, SysDate EndTime From tblEMSEquipmentState" & _
" ) A" & _
" Where A.StartTime <= to_date('" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')" & _
" And A.EndTime > to_date('" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS')" & _
" And A.EquipmentState = " & drTemp("EquipmentState") & _
" And A.EquipmentNo = Z.EquipmentNo" & _
" Group By A.EquipmentNo, A.EquipmentState)/" & StartEndTime & "*100,2),0) """ & drTemp("StateName") & """"
ElseIf DataBaseType = "mssql" Or DataBaseType = "msaccess" Then
strSQL = strSQL & " ,ISNull(Round((CAST((Select Sum(DateDiff(Second," & _
" (Case" & _
" When A.StartTime > '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "' Then A.StartTime" & _
" Else '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
" End)," & _
" (Case" & _
" When A.EndTime < '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "' Then A.EndTime" & _
" Else '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
" End)))" & _
" From (Select EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime From tblEMSEquipmentStateLog" & _
" UNION All" & _
" Select EquipmentType, EquipmentNo, EquipmentState, StartTime, GetDate() EndTime From tblEMSEquipmentState" & _
" ) A" & _
" Where A.StartTime <= '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
" And A.EndTime > '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
" And A.EquipmentState = " & drTemp("EquipmentState") & _
" And A.EquipmentNo = Z.EquipmentNo" & _
" Group By A.EquipmentNo, A.EquipmentState) as float) /CAST(" & StartEndTime & " AS float))*100,2),0) " & drTemp("StateName")
End If
Loop
drTemp.Close()
cmmTemp.Dispose()
strSQL = strSQL & " From tblEQPEquipmentBasis Z" & _
" Where Z.EquipmentNo Is Not Null"
If AreaNo <> defString Then
strSQL = strSQL & " And (Select ContainareaNo From tblSMDAreaRelation Where ObjectNo = Z.EquipmentNo And ObjectType = 2) = '" & AreaNo & "'"
End If
If EquipmentNo <> defString Then
strSQL = strSQL & " And EquipmentNo In ('" & Replace(EquipmentNo, ",", "','") & "')"
End If
If AdditionalXml <> "" Then
'//additionalcondition
strSQL = strSQL & SeparateAddXML_Condition(AdditionalXml)
End If
strSQL = strSQL & " Order By EquipmentNo"
'//Select Data
cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp)
daTemp = New OleDb.OleDbDataAdapter(cmmTemp)
dsTemp = New DataSet
daTemp.Fill(dsTemp, "EQPDailyReport")
If strDataBaseType = "oracle" Then
strSQL = " Select EquipmentNo, Sum(B.GoodQty) Moves" & _
" From tblWIPCont_Equipment A, tblWIPLotLog_Report B" & _
" Where A.LogGroupSerial = B.LogGroupSerial" & _
" And A.EndTime Is Not Null " & _
" And A.EndTime >= To_Date('" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS') " & _
" And A.EndTime < To_Date('" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "','YYYY/MM/DD HH24:MI:SS') " & _
"Group By EquipmentNo"
ElseIf DataBaseType = "mssql" Or DataBaseType = "msaccess" Then
strSQL = " Select EquipmentNo, Sum(B.GoodQty) Moves" & _
" From tblWIPCont_Equipment A, tblWIPLotLog_Report B" & _
" Where A.LogGroupSerial = B.LogGroupSerial" & _
" And A.EndTime Is Not Null " & _
" And A.EndTime >= '" & Format(CDate(StartTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
" And A.EndTime < '" & Format(CDate(EndTime), "yyyy/MM/dd HH:mm:ss") & "'" & _
"Group By EquipmentNo"
End If
cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp)
drTemp = cmmTemp.ExecuteReader()
Do While drTemp.Read
Rows = dsTemp.Tables("EQPDailyReport").Select("EquipmentNo = '" & drTemp("EquipmentNo") & "'")
If Rows.Length <> 0 Then
Rows(0).Item("Moves") = drTemp("Moves")
End If
Loop
drTemp.Close()
cmmTemp.Dispose()
'//Combine Return Value
LoadEQPDailyReport = CombineXMLReturnValue("loadeqpdailyreport", "EQPDailyReport", _
"DataSet", FormatXMLSchema(dsTemp.GetXmlSchema), dsTemp.GetXml, "")
Catch e1 As Exception
LoadEQPDailyReport = "fail"
Throw 'New Exception("clsReport.LoadEQPDailyReport: " & e1.Message)
Finally
Call objLic.CloseConnection(cnnTemp)
If Not daTemp Is Nothing Then
daTemp.Dispose()
End If
If Not dsTemp Is Nothing Then
dsTemp.Dispose()
End If
End Try
End Function
谁能帮我从里面抠出最终的SQL来，用的是ORACLE数据库
7 个解决方案
#1
你自己在程序的适当位置打印一下 strSQL 不就ok了。
#2
可以上dbfaq.net试试。。
#3
跟踪监视SQL。
#4
头晕~
#5
抠出SQL是一个力气活！我试了一下，问题很大，老兄对SQL的非过程性质没有概念，这样的程序没有使用价值。
我理出的大致逻辑是：
Select :AreaNo,
Z.EquipmentType
Z.EquipmentNo,
0 Moves,
Nvl(Round(SUM(计算时间SELECT）* 24*60*60 / :StartEndTime * 100 ,2),0) StateName
From tblEQPEquipmentBasis Z
Where Z.EquipmentNo Is Not Null
AND EXISTS (SELECT 1
FROM tblSMDAreaRelation
Where ObjectNo = Z.EquipmentNo
And ObjectType = 2
AND ContainareaNo = :AreaNo )
And EquipmentNo In (:EquipmentNo) -- :EquipmentNo 的数据类似于:'aa','bb','cc'
Order By EquipmentNo
---- 计算时间SELECT
Select Sum(Least(A.EndTime,:EndTime) - Greatest(A.StartTime,:StartTime))
From (Select EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime
From tblEMSEquipmentStateLog
UNION All
Select EquipmentType, EquipmentNo, EquipmentState, StartTime, SysDate EndTime
From tblEMSEquipmentState ) A
Where A.StartTime <= :EndTime
And A.EndTime > :StartTime)
And A.EquipmentState = :EquipmentState
And A.EquipmentNo = Z.EquipmentNo)
我理出的大致逻辑是：
Select :AreaNo,
Z.EquipmentType
Z.EquipmentNo,
0 Moves,
Nvl(Round(SUM(计算时间SELECT）* 24*60*60 / :StartEndTime * 100 ,2),0) StateName
From tblEQPEquipmentBasis Z
Where Z.EquipmentNo Is Not Null
AND EXISTS (SELECT 1
FROM tblSMDAreaRelation
Where ObjectNo = Z.EquipmentNo
And ObjectType = 2
AND ContainareaNo = :AreaNo )
And EquipmentNo In (:EquipmentNo) -- :EquipmentNo 的数据类似于:'aa','bb','cc'
Order By EquipmentNo
---- 计算时间SELECT
Select Sum(Least(A.EndTime,:EndTime) - Greatest(A.StartTime,:StartTime))
From (Select EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime
From tblEMSEquipmentStateLog
UNION All
Select EquipmentType, EquipmentNo, EquipmentState, StartTime, SysDate EndTime
From tblEMSEquipmentState ) A
Where A.StartTime <= :EndTime
And A.EndTime > :StartTime)
And A.EquipmentState = :EquipmentState
And A.EquipmentNo = Z.EquipmentNo)
#6
顶楼上 将SQL抠出来了
#7
这可真是个体力活
#1
你自己在程序的适当位置打印一下 strSQL 不就ok了。
#2
可以上dbfaq.net试试。。
#3
跟踪监视SQL。
#4
头晕~
#5
抠出SQL是一个力气活！我试了一下，问题很大，老兄对SQL的非过程性质没有概念，这样的程序没有使用价值。
我理出的大致逻辑是：
Select :AreaNo,
Z.EquipmentType
Z.EquipmentNo,
0 Moves,
Nvl(Round(SUM(计算时间SELECT）* 24*60*60 / :StartEndTime * 100 ,2),0) StateName
From tblEQPEquipmentBasis Z
Where Z.EquipmentNo Is Not Null
AND EXISTS (SELECT 1
FROM tblSMDAreaRelation
Where ObjectNo = Z.EquipmentNo
And ObjectType = 2
AND ContainareaNo = :AreaNo )
And EquipmentNo In (:EquipmentNo) -- :EquipmentNo 的数据类似于:'aa','bb','cc'
Order By EquipmentNo
---- 计算时间SELECT
Select Sum(Least(A.EndTime,:EndTime) - Greatest(A.StartTime,:StartTime))
From (Select EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime
From tblEMSEquipmentStateLog
UNION All
Select EquipmentType, EquipmentNo, EquipmentState, StartTime, SysDate EndTime
From tblEMSEquipmentState ) A
Where A.StartTime <= :EndTime
And A.EndTime > :StartTime)
And A.EquipmentState = :EquipmentState
And A.EquipmentNo = Z.EquipmentNo)
我理出的大致逻辑是：
Select :AreaNo,
Z.EquipmentType
Z.EquipmentNo,
0 Moves,
Nvl(Round(SUM(计算时间SELECT）* 24*60*60 / :StartEndTime * 100 ,2),0) StateName
From tblEQPEquipmentBasis Z
Where Z.EquipmentNo Is Not Null
AND EXISTS (SELECT 1
FROM tblSMDAreaRelation
Where ObjectNo = Z.EquipmentNo
And ObjectType = 2
AND ContainareaNo = :AreaNo )
And EquipmentNo In (:EquipmentNo) -- :EquipmentNo 的数据类似于:'aa','bb','cc'
Order By EquipmentNo
---- 计算时间SELECT
Select Sum(Least(A.EndTime,:EndTime) - Greatest(A.StartTime,:StartTime))
From (Select EquipmentType, EquipmentNo, EquipmentState, StartTime, EndTime
From tblEMSEquipmentStateLog
UNION All
Select EquipmentType, EquipmentNo, EquipmentState, StartTime, SysDate EndTime
From tblEMSEquipmentState ) A
Where A.StartTime <= :EndTime
And A.EndTime > :StartTime)
And A.EquipmentState = :EquipmentState
And A.EquipmentNo = Z.EquipmentNo)
#6
顶楼上 将SQL抠出来了
#7
这可真是个体力活