多个内联接SQL Server Compact Edition
多个内联接SQL Server Compact Edition
提问于 2012-02-22 00:33:59
我正在尝试在SQL Server CE中的单个查询中执行多个联接。我知道SQL Server CE不支持多个SELECT，但我找不到有关多个内联接的任何信息。在ProjectItemMaster (在FROM之后)和第一个内部连接上，我一直收到令牌错误。警告:这是一个用于生成报告的丑陋的大型查询。
GetCutSheetDataByCustPnumTagQuery定义为：
SELECT ReportCalculations.ItemNumber, ReportCalculations.PartNumber,
ReportCalculations.calcWidth, ReportCalculations.calcHeight,
ReportCalculations.calcQuantity, ReportCalculations.Description,
ReportCalculations.PrintonCutSheet, ProjectItemMaster.Quantity,
ProjectItemMaster.HingeDirection, ProjectItemMaster.ItemDescription,
ProjectItemMaster.MetalFinish, ProjectItemMaster.Width,
ProjectItemMaster.Height, ProjectItemMaster.CustomerID,
CustomerMaster.CustomerId AS Id_CM, ProjectItemMaster.GlassType,
ProjectItemMaster.Tag AS Expr1, ReportCalculations.Tag,
ProjectItemMaster.ItemNumber AS Expr2, ReportCalculations.CalcX,
ReportCalculations.CalcY, ProjectItemMaster.OpeningWidth,
ProjectItemMaster.ReturnDirection, ProjectItemMaster.PanelDirection,
ProjectItemMaster.ReturnWidth, ProjectItemMaster.ButtressHeight,
ProjectItemMaster.ButtressWidth, ProjectItemMaster.AvailThickness,
ProjectItemMaster.PanelThickness, ProjectItemMaster.Image,
ProjectItemMaster.SoftwareVersion, ProjectItemMaster.DatabaseVersion,
ProdlineMaster.Series, ProdlineMaster.Report, ProjectItemMaster.Addons,
ProjectItemMaster.PanelWidth, CustomerMaster.CustomerName,
ProjectMaster.WO, ProjectMaster.PO, ProdlineMaster.SeriesName,
ReportCalculations.Series AS Expr3
FROM ProjectItemMaster
INNER JOIN CustomerMaster
ON ProjectItemMaster.CustomerID = CustomerMaster.CustomerId
INNER JOIN ReportCalculations
ON ProjectItemMaster.Tag = ReportCalculations.Tag
AND ProjectItemMaster.CustomerID = ReportCalculations.CustomerID
AND ProjectItemMaster.ProjectNumber = ReportCalculations.ProjectNumber
INNER JOIN ProdlineMaster
ON ReportCalculations.Series = ProdlineMaster.Series
INNER JOIN ProjectMaster
ON CustomerMaster.CustomerId = ProjectMaster.CustomerId
AND ProjectItemMaster.ProjectNumber = ProjectMaster.ProjectNumber
WHERE (ReportCalculations.PrintonCutSheet = 'Y')
AND (ProjectItemMaster.ProjectNumber = @ProjectNumber)
AND (CustomerMaster.CustomerId = @CustomerID)
AND (ProjectItemMaster.CustomerID = @CustomerID)
AND (ProjectItemMaster.Tag = @TAG)这是在VB.NET中对SQL Compact数据库执行查询的函数。
Public Function getCutSheetInfobyCustProdTag(ByRef customerID As String,
ByRef projectNumber As Integer,
ByVal tag As String)
As System.Data.DataTable
Dim mydata As New DataTable
GetCutSheetDataByCustPnumTagQuery.Parameters.Clear()
GetCutSheetDataByCustPnumTagQuery.Parameters.AddWithValue("@ProjectNumber",
projectNumber)
GetCutSheetDataByCustPnumTagQuery.Parameters.AddWithValue("@CustomerID",
customerID)
GetCutSheetDataByCustPnumTagQuery.Parameters.AddWithValue("@TAG", tag)
Try
splConnection.Open()
Dim reader As SqlCeDataReader = GetCutSheetDataByCustPnumTagQuery.ExecuteReader
mydata.Load(reader)
Catch ex As Exception
MessageBox.Show("Problem with reportcalculations table", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
' Return False
Finally
splConnection.Close()
End Try
'Return True
Return mydata
End Function如果我不能使用多个内部连接，我可以将查询分解为许多不同的查询，但我不希望这样做。因此，维护SQL Server CE和SQL Server版本的程序更加容易。
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-02-22 23:52:45
显然，SQL Compact不喜欢您给出没有显式说明其类型和长度的查询参数。查询一直尝试将CustomerID "987654“转换为数字。我之所以弄明白这一点，是因为将值硬编码到查询中可以很好地运行，但是当我使用@CustomerID时，程序抛出了一个预期。因此，从parameters.addwithvalue(名称、值)到Parameters.add(名称、类型、长度)的转换解决了这些问题。
Public Function getCutSheetInfobyCustProdTag(ByRef customerID As String, ByRef projectNumber As Integer, ByVal tag As String) As System.Data.DataTable
Dim mydata As New DataTable
GetCutSheetDataByCustPnumTagQuery.Parameters.Clear()
GetCutSheetDataByCustPnumTagQuery.Parameters.Add("@ProjectNumber", System.Data.SqlDbType.Int)
GetCutSheetDataByCustPnumTagQuery.Parameters("@ProjectNumber").Value = projectNumber
GetCutSheetDataByCustPnumTagQuery.Parameters.Add("@CustomerID", System.Data.SqlDbType.NVarChar, 25)
GetCutSheetDataByCustPnumTagQuery.Parameters("@CustomerID").Value = customerID
GetCutSheetDataByCustPnumTagQuery.Parameters.Add("@TAG", System.Data.SqlDbType.NVarChar, 50)
GetCutSheetDataByCustPnumTagQuery.Parameters("@TAG").Value = tag
Try
splConnection.Open()
Dim reader As SqlCeDataReader = GetCutSheetDataByCustPnumTagQuery.ExecuteReader
mydata.Load(reader)
Catch ex As Exception
MessageBox.Show("Problem with reportcalculations table", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
splConnection.Close()
End Try
Return mydata
End Function感谢您@X-Zero的帮助
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9381316
复制相关文章
相似问题
腾讯云开发者
