gpt4 book ai didi

sql - 如何将项目添加到联合 SQL View ?

转载 作者:搜寻专家 更新时间:2023-10-30 20:58:39 25 4
gpt4 key购买 nike

我正在努力在 Crystal Reports 中制作一份自动报告,这需要我将表格添加到现有的、预先编写的 View 中。该 View 包含一个 Union 操作,这使我无法正常使用该 View 。我所做的是手动添加数据(大约一半)。

报告所需的项目是 tblSOPartsUsed.Memo。我已将其添加到两个选择查询中。在 tblCustomerInventory 和 tblSOPartsUsed 之间的连接中,.Memo 表仅出现在 PartsUsed 表中,而不出现在 CustomerInventory 中。

  1. 首先选择查询位置

                  tblCustomerInventory.SerialNumber AS ExchangeSerialNumber, **tblSOPartsUsed.Memo AS SOPartsNotes**, tblInvoiceDetail.Taxable AS DetailIsTaxable, 
  2. 首先从查询位置

                  tblCustomerInventory FULL OUTER JOIN
    tblSOPartsUsed INNER JOIN

我以完全相同的方式概述了第二个 Select 和 From。

有人可以解释一下吗,我已经尝试了一切!

完整代码如下...

SELECT InvoiceAssemblyPriceBook.Features AS AssemblyFeatures,
tblInvoiceAssemblyDetail.EachQuantity AS AssemblyEachQuantity,
tblInvoiceAssemblyDetail.FKInvoiceDetail AS AssemblyFKInvoiceDetail,
tblInvoiceAssemblyDetail.InvoiceAssemblyDetailKeyID,
tblInvoiceAssemblyDetail.ItemID AS AssemblyItemID,
tblInvoiceAssemblyDetail.ItemDescription AS AssemblyItemDescription,
tblInvoiceAssemblyDetail.PrintOnInvoice AS AssemblyPrintOnInvoice,
tblInvoiceAssemblyDetail.Quantity AS AssemblyQuantity,
tblInvoiceAssemblyDetail.SellingPrice AS AssemblySellingPrice,
tblInvoiceAssemblyDetail.TotalSellingPrice AS AssemblyTotalSellingPrice,
tblInvoiceAssemblyDetail.Type AS AssemblyType,
tblInvoiceAssemblyDetail.UnitOfMeasure AS AssemblyUnitOfMeasure,
tblInvoiceDetail.AssemblyType AS DetailAssemblyType,
tblInvoiceDetail.InvoiceDetailKeyID,
tblInvoiceDetail.ItemDescription AS DetailItemDescription,
tblInvoiceDetail.ItemID AS DetailItemID,
tblInvoiceDetail.PrintOnInvoice AS DetailPrintOnInvoice,
tblInvoiceDetail.Quantity AS DetailQuantity,
tblInvoiceDetail.SellingPrice AS DetailSellingPrice,
tblInvoiceDetail.TotalSellingPrice AS DetailTotalSellingPrice,
tblInvoiceDetail.Type AS DetailType,
tblInvoices.AccountNumber,
tblInvoices.Comments,
tblInvoices.ContractNumber,
tblInvoices.Deposit,
tblInvoices.Freight,
tblInvoices.GSTax,
tblInvoices.InvoiceDate,
tblInvoices.InvoiceNumber,
tblInvoices.PaidDate,
tblInvoices.QuoteNumber,
tblInvoices.SalesTaxPercent,
CASE
WHEN tblInvoiceDetail.SoNumber IS NULL
THEN tblInvoices.SONumber
ELSE tblInvoiceDetail.SoNumber
END AS SONumber,
tblInvoices.STATUS,
tblInvoices.StatusDate,
tblInvoices.Tax,
tblInvoices.TotalAmountDue,
tblInvoices.TotalComment,
tblInvoices.TotalDollarsDiscounted,
tblInvoices.TotalGrossSell,
tblInvoices.TotalNetSell,
tblInvoices.TradeIn,
tblInvoices.WorkOrderNumber,
tblPriceLevels.IsRepairLevel,
tblServiceOrders.ContractNumber AS ServiceOrderContractNumber,
tblSysCompanySettings.HideGSTaxRelatedInformation,
tblSysCompanySettings.ItemsServicedPrintOnInvoice,
tblSysDisclaimerSettings.SalesInvoiceDisclaimer,
tblSysDisclaimerSettings.ServiceInvoiceDisclaimer,
tblSysPBSettings.PrintItemorPartNum,
VoidedByReps.RepName AS VoidedByRepName,
tblInvoices.TotalNetInvoice,
InvoiceDetailPriceBook.PartNumber AS DetailPartNumber,
InvoiceDetailPriceBook.UnitOfMeasure AS DetailUnitOfMeasure,
InvoiceDetailPriceBook.Features AS DetailFeatures,
tblSysDisclaimerSettings.ContractInvoiceDisclaimer,
tblInvoices.ProviderTax,
tblServiceOrders.BriefDescription AS SOBriefDescription,
tblInvoices.GSTaxComputedBeforeTradeIn,
tblInvoices.TaxComputedBeforeTradeIn,
tblInvoices.ProviderTaxRate,
tblInvoices.FreightTaxable,
tblInvoices.GSTIsTaxable,
tblInvoices.ProjectKeyID,
InvoicesReps.RepName,
tblAccounts.AccountNumber AS Expr1,
tblAccounts.AccountID,
tblInvoices.Terms,
tblInvoices.Reference,
tblInvoices.ARCustomerNumber,
tblInvoices.PONumber,
tblInvoices.ShipVia,
tblServiceOrders.DateRequested,
tblServiceOrders.DateOpened,
tblServiceOrders.SONumber AS ServiceOrderSONumber,
tblSysReportSettings.InvoiceCommentsAtEnd,
tblInvoices.SourceDocument,
tblTaxCodes.HasTieredDistrict,
tblExchange.ExchangeKeyID,
tblCustomerInventory.ItemID AS ExchangeItemID,
tblCustomerInventory.ItemDescription AS ExchangeItemDescription,
tblCustomerInventory.SerialNumber AS ExchangeSerialNumber,
tblSOPartsUsed.Memo AS SOPartsNotes,
tblInvoiceDetail.Taxable AS DetailIsTaxable,
tblInvoiceAssemblyDetail.Taxable AS AssemblyIsTaxable,
tblInvoices.IsFinalProgressiveInvoice,
tblInvoiceDetail.IsProgressiveInvoiceItem,
tblInvoices.IsProgressiveInvoice,
tblInvoices.TotalPriceCredited,
tblInvoices.TotalTaxCredited,
tblInvoices.TotalGSTaxCredited,
tblInvoices.TotalProviderTaxCredited,
tblInvoices.TotalFreightCredited,
tblInvoices.DiscountAllowed,
tblInvoices.AmountPaid,
(
SELECT MAX(SOItemsServicedKeyID) AS Expr1
FROM tblSOItemsServiced
WHERE (SONumber = tblServiceOrders.SONumber)
) AS SOItemsServicedKeyID,
tblInvoiceDetail.CommentOnly,
ServiceOrderContacts.ContactName AS SOContactName,
tblServiceOrders.ContactPhone AS SOContactPhone,
tblServiceOrders.ContactPhoneLocation AS SOContactPhoneLocation,
(
SELECT TOP (1) FormattedPhoneNumber
FROM tblPhoneNumbers
WHERE (ContactNumber = 0)
AND (PrimaryIndicator = 1)
AND (COALESCE(PhoneLocation, '') <> 'Fax')
AND (AccountNumber = tblAccounts.AccountNumber)
) AS AccountPhone,
(
SELECT TOP (1) PhoneLocation
FROM tblPhoneNumbers AS tblPhoneNumbers_1
WHERE (ContactNumber = 0)
AND (PrimaryIndicator = 1)
AND (COALESCE(PhoneLocation, '') <> 'Fax')
AND (AccountNumber = tblAccounts.AccountNumber)
) AS AccountPhoneLocation,
COALESCE(tvwr_TotalAmountDuePerAccount.AmountDue, 0.00) AS AmountDue,
COALESCE(tvwr_TotalAmountDuePerAccount.Unappliedpayments, 0.00) AS Unappliedpayments,
tblTaxCodes.IsHarmonizedTaxCode,
tblInvoices.GSTax + tblInvoices.Tax AS HSTax
FROM tblPriceBook AS InvoiceAssemblyPriceBook
RIGHT JOIN tblPriceBook AS InvoiceDetailPriceBook
RIGHT JOIN tblInvoiceAssemblyDetail
RIGHT JOIN tblCustomerInventory
FULL JOIN tblSOPartsUsed
INNER JOIN tblExchange
ON tblSOPartsUsed.SOPartsUsedKeyID = tblExchange.FKSOPartsUsed
ON tblCustomerInventory.CustomerInventoryKeyID = tblExchange.FKCustomerInventory RIGHT JOIN tblInvoiceDetail
ON tblSOPartsUsed.FKInvoiceDetail = tblInvoiceDetail.InvoiceDetailKeyID
ON tblInvoiceAssemblyDetail.FKInvoiceDetail = tblInvoiceDetail.InvoiceDetailKeyID
ON InvoiceDetailPriceBook.ItemID = tblInvoiceDetail.ItemID
ON InvoiceAssemblyPriceBook.ItemID = tblInvoiceAssemblyDetail.ItemID LEFT JOIN tblPriceLevels
ON tblInvoiceDetail.PriceLevel = tblPriceLevels.PriceLevelsKeyID RIGHT JOIN tvwr_TotalAmountDuePerAccount INNER JOIN tblAccounts
ON tvwr_TotalAmountDuePerAccount.AccountNumber = tblAccounts.AccountNumber RIGHT JOIN tblReps AS VoidedByReps RIGHT JOIN tblInvoices AS tblInvoices LEFT JOIN tblTaxCodes
ON tblInvoices.SalesTaxCode = tblTaxCodes.SalesTaxCode LEFT JOIN tblReps AS InvoicesReps
ON tblInvoices.SalesRep = InvoicesReps.RepNumber
ON VoidedByReps.RepNumber = tblInvoices.StatusBy LEFT JOIN tblServiceOrders LEFT JOIN tblContacts AS ServiceOrderContacts
ON tblServiceOrders.ContactNumber = ServiceOrderContacts.ContactNumber
ON tblInvoices.SONumber = tblServiceOrders.SONumber
ON tblAccounts.AccountNumber = tblInvoices.AccountNumber
ON tblInvoiceDetail.InvoiceNumber = tblInvoices.InvoiceNumber LEFT JOIN tblContacts AS tblContacts
ON tblAccounts.PrimaryContactNumber = tblContacts.ContactNumber CROSS JOIN tblSysPBSettings CROSS JOIN tblSysCompanySettings CROSS JOIN tblSysReportSettings CROSS JOIN tblSysDisclaimerSettings
WHERE (tblInvoices.MSPAgreementNumber = 0)

UNION ALL

SELECT InvoiceAssemblyPriceBook.Features AS AssemblyFeatures,
tblInvoiceAssemblyDetail.EachQuantity AS AssemblyEachQuantity,
tblInvoiceAssemblyDetail.FKInvoiceDetail AS AssemblyFKInvoiceDetail,
tblInvoiceAssemblyDetail.InvoiceAssemblyDetailKeyID,
tblInvoiceAssemblyDetail.ItemID AS AssemblyItemID,
tblInvoiceAssemblyDetail.ItemDescription AS AssemblyItemDescription,
tblInvoiceAssemblyDetail.PrintOnInvoice AS AssemblyPrintOnInvoice,
tblInvoiceAssemblyDetail.Quantity AS AssemblyQuantity,
tblInvoiceAssemblyDetail.SellingPrice AS AssemblySellingPrice,
tblInvoiceAssemblyDetail.TotalSellingPrice AS AssemblyTotalSellingPrice,
tblInvoiceAssemblyDetail.Type AS AssemblyType,
tblInvoiceAssemblyDetail.UnitOfMeasure AS AssemblyUnitOfMeasure,
tblInvoiceDetail.AssemblyType AS DetailAssemblyType,
tblInvoiceDetail.InvoiceDetailKeyID,
tblInvoiceDetail.ItemDescription AS DetailItemDescription,
tblInvoiceDetail.ItemID AS DetailItemID,
tblInvoiceDetail.PrintOnInvoice AS DetailPrintOnInvoice,
tblInvoiceDetail.Quantity AS DetailQuantity,
tblInvoiceDetail.SellingPrice AS DetailSellingPrice,
tblInvoiceDetail.TotalSellingPrice AS DetailTotalSellingPrice,
tblInvoiceDetail.Type AS DetailType,
tblInvoices.AccountNumber,
tblInvoices.Comments,
tblInvoices.ContractNumber,
tblInvoices.Deposit,
tblInvoices.Freight,
tblInvoices.GSTax,
tblInvoices.InvoiceDate,
tblInvoices.InvoiceNumber,
tblInvoices.PaidDate,
tblInvoices.QuoteNumber,
tblInvoices.SalesTaxPercent,
CASE
WHEN tblInvoiceDetail.SoNumber IS NULL
THEN tblInvoices.SONumber
ELSE tblInvoiceDetail.SoNumber
END AS SONumber,
tblInvoices.STATUS,
tblInvoices.StatusDate,
tblInvoices.Tax,
tblInvoices.TotalAmountDue,
tblInvoices.TotalComment,
tblInvoices.TotalDollarsDiscounted,
tblInvoices.TotalGrossSell,
tblInvoices.TotalNetSell,
tblInvoices.TradeIn,
tblInvoices.WorkOrderNumber,
tblPriceLevels.IsRepairLevel,
tblServiceOrders.ContractNumber AS ServiceOrderContractNumber,
tblSysCompanySettings.HideGSTaxRelatedInformation,
tblSysCompanySettings.ItemsServicedPrintOnInvoice,
tblSysDisclaimerSettings.SalesInvoiceDisclaimer,
tblSysDisclaimerSettings.MSPAgreementInvoiceDisclaimer AS ServiceInvoiceDisclaimer,
tblSysPBSettings.PrintItemorPartNum,
VoidedByReps.RepName AS VoidedByRepName,
tblInvoices.TotalNetInvoice,
InvoiceDetailPriceBook.PartNumber AS DetailPartNumber,
InvoiceDetailPriceBook.UnitOfMeasure AS DetailUnitOfMeasure,
InvoiceDetailPriceBook.Features AS DetailFeatures,
tblSysDisclaimerSettings.ContractInvoiceDisclaimer,
tblInvoices.ProviderTax,
tblServiceOrders.BriefDescription AS SOBriefDescription,
tblInvoices.GSTaxComputedBeforeTradeIn,
tblInvoices.TaxComputedBeforeTradeIn,
tblInvoices.ProviderTaxRate,
tblInvoices.FreightTaxable,
tblInvoices.GSTIsTaxable,
tblInvoices.ProjectKeyID,
InvoicesReps.RepName,
tblAccounts.AccountNumber AS Expr1,
tblAccounts.AccountID,
tblInvoices.Terms,
tblInvoices.Reference,
tblInvoices.ARCustomerNumber,
tblInvoices.PONumber,
tblInvoices.ShipVia,
tblServiceOrders.DateRequested,
tblServiceOrders.DateOpened,
tblServiceOrders.SONumber AS ServiceOrderSONumber,
tblSysReportSettings.InvoiceCommentsAtEnd,
tblInvoices.SourceDocument,
tblTaxCodes.HasTieredDistrict,
tblExchange.ExchangeKeyID,
tblCustomerInventory.ItemID AS ExchangeItemID,
tblCustomerInventory.ItemDescription AS ExchangeItemDescription,
tblCustomerInventory.SerialNumber AS ExchangeSerialNumber,
tblSOPartsUsed.Memo AS SOPartsNotes,
tblInvoiceDetail.Taxable AS DetailIsTaxable,
tblInvoiceAssemblyDetail.Taxable AS AssemblyIsTaxable,
tblInvoices.IsFinalProgressiveInvoice,
tblInvoiceDetail.IsProgressiveInvoiceItem,
tblInvoices.IsProgressiveInvoice,
tblInvoices.TotalPriceCredited,
tblInvoices.TotalTaxCredited,
tblInvoices.TotalGSTaxCredited,
tblInvoices.TotalProviderTaxCredited,
tblInvoices.TotalFreightCredited,
tblInvoices.DiscountAllowed,
tblInvoices.AmountPaid,
(
SELECT MAX(SOItemsServicedKeyID) AS Expr1
FROM tblSOItemsServiced
WHERE (SONumber = tblServiceOrders.SONumber)
) AS SOItemsServicedKeyID,
tblInvoiceDetail.CommentOnly,
ServiceOrderContacts.ContactName AS SOContactName,
tblServiceOrders.ContactPhone AS SOContactPhone,
tblServiceOrders.ContactPhoneLocation AS SOContactPhoneLocation,
(
SELECT TOP (1) FormattedPhoneNumber
FROM tblPhoneNumbers
WHERE (ContactNumber = 0)
AND (PrimaryIndicator = 1)
AND (COALESCE(PhoneLocation, '') <> 'Fax')
AND (AccountNumber = tblAccounts.AccountNumber)
) AS AccountPhone,
(
SELECT TOP (1) PhoneLocation
FROM tblPhoneNumbers AS tblPhoneNumbers_1
WHERE (ContactNumber = 0)
AND (PrimaryIndicator = 1)
AND (COALESCE(PhoneLocation, '') <> 'Fax')
AND (AccountNumber = tblAccounts.AccountNumber)
) AS AccountPhoneLocation,
COALESCE(tvwr_TotalAmountDuePerAccount.AmountDue, 0.00) AS AmountDue,
COALESCE(tvwr_TotalAmountDuePerAccount.Unappliedpayments, 0.00) AS Unappliedpayments,
COALESCE(tblTaxCodes.IsHarmonizedTaxCode, 0) AS IsHarmonizedTaxCode,
tblInvoices.GSTax + tblInvoices.Tax AS HSTax
FROM tblPriceBook AS InvoiceAssemblyPriceBook
RIGHT JOIN tblPriceBook AS InvoiceDetailPriceBook
RIGHT JOIN tblInvoiceAssemblyDetail
RIGHT JOIN tblCustomerInventory
FULL JOIN tblSOPartsUsed
INNER JOIN tblExchange
ON tblSOPartsUsed.SOPartsUsedKeyID = tblExchange.FKSOPartsUsed
ON tblCustomerInventory.CustomerInventoryKeyID = tblExchange.FKCustomerInventory RIGHT JOIN tblInvoiceDetail
ON tblSOPartsUsed.FKInvoiceDetail = tblInvoiceDetail.InvoiceDetailKeyID
ON tblInvoiceAssemblyDetail.FKInvoiceDetail = tblInvoiceDetail.InvoiceDetailKeyID
ON InvoiceDetailPriceBook.ItemID = tblInvoiceDetail.ItemID
ON InvoiceAssemblyPriceBook.ItemID = tblInvoiceAssemblyDetail.ItemID LEFT JOIN tblPriceLevels
ON tblInvoiceDetail.PriceLevel = tblPriceLevels.PriceLevelsKeyID RIGHT JOIN tvwr_TotalAmountDuePerAccount INNER JOIN tblAccounts
ON tvwr_TotalAmountDuePerAccount.AccountNumber = tblAccounts.AccountNumber RIGHT JOIN tblReps AS VoidedByReps RIGHT JOIN tblInvoices AS tblInvoices LEFT JOIN tblTaxCodes
ON tblInvoices.SalesTaxCode = tblTaxCodes.SalesTaxCode LEFT JOIN tblReps AS InvoicesReps
ON tblInvoices.SalesRep = InvoicesReps.RepNumber
ON VoidedByReps.RepNumber = tblInvoices.StatusBy LEFT JOIN tblServiceOrders LEFT JOIN tblContacts AS ServiceOrderContacts
ON tblServiceOrders.ContactNumber = ServiceOrderContacts.ContactNumber
ON tblInvoices.SONumber = tblServiceOrders.SONumber
ON tblAccounts.AccountNumber = tblInvoices.AccountNumber
ON tblInvoiceDetail.InvoiceNumber = tblInvoices.InvoiceNumber LEFT JOIN tblContacts AS tblContacts
ON tblAccounts.PrimaryContactNumber = tblContacts.ContactNumber CROSS JOIN tblSysPBSettings CROSS JOIN tblSysCompanySettings CROSS JOIN tblSysReportSettings CROSS JOIN tblSysDisclaimerSettings
WHERE (tblInvoices.MSPAgreementNumber <> 0)

最佳答案

我设法将 View 的每一半复制到一个新 View 中,从 GUI 中手动勾选 tblSOPartsUsed.memo 并将两者复制在一起,这使得 View 工作得很好

关于sql - 如何将项目添加到联合 SQL View ?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12361466/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com