I can run the query - ie UniQuery.Open, and I can read the records in and I can process them. Everything with this query is fine. I want to add a single column to this query from a local Firebird database, which is why I am looking at VirtualQuery. Simply to merge in this local data.
I add a VirtualQuery and setup a sourcedatasource to point to the UniQuery previously mentioned. At designtime I make the UniQuery active. I open VirtualQuery Editor, set up the query
Code: Select all
SELECT * FROM iConnect
I can setup a Firebird UniQuery and sourcedataset, and that works ok. But this one to the MSSQL database I cannot.
This is in Delphi XE7, with the latest UniDac (7.1?) installed.
Code: Select all
SELECT
dbo.icTransactionIndex.AccountId AS "Cust ID",
dbo.icTransaction.TxTradingDate AS "Trading Day",
dbo.icTransaction.TxEnd AS "Transaction Time",
dbo.dbVenues.Name as Venue,
'unknown' as "Location class",
dbo.icTransaction.TxLocationName AS "Location Name",
dbo.icTransaction.TxTillName AS "Terminal Name",
dbo.icTransaction.TxId AS "Transaction No",
ROW_NUMBER() OVER (PARTITION BY dbo.icTransaction.TxId Order by dbo.icTransaction.TxId) AS "Transaction line",
dbo.icTransactionDetailProduct.ProductId AS "Item Number",
dbo.dbProducts.FullName AS "Item Desc",
dbo.dbProducts.Size,
(dbo.icTransactionDetail.DetailQty *-1) AS Qty,
dbo.icTransactionDetailProduct.ProductCost AS Cost,
dbo.icTransactionDetailProduct.ProductSell AS "Selling Price",
(dbo.icTransactionDetail.DetailValue *-1) AS "Sale Value",
dbo.icTransactionDetailProduct.ProductAPNs AS "Item Barcode",
dbo.icTransactionDetailProduct.Barcode AS "Barcode Scanned",
dbo.icTransactionDetailGroups.GroupValue AS "Sub Group",
icTransactionDetailGroups1.GroupValue AS Dept,
dbo.icTransactionDetailPrice.PriceName AS "Promo Name",
dbo.icTransactionDetailPrice.PriceFormula AS "Price Formula",
dbo.icTransactionDetailPrice.PriceSource AS "Price Source",
dbo.icTransactionDetailPrice.PriceSender AS "Method of Activation"
FROM
dbo.icTransaction
LEFT OUTER JOIN dbo.icTransactionIndex ON (dbo.icTransaction.TxID = dbo.icTransactionIndex.TxID)
INNER JOIN dbo.dbCustomers ON (dbo.icTransactionIndex.AccountId = dbo.dbCustomers.Id)
INNER JOIN dbo.icTransactionDetail ON (dbo.icTransactionDetail.TxId = dbo.icTransaction.TxId)
INNER JOIN dbo.icTransactionDetailProduct ON (dbo.icTransactionDetail.TxId = dbo.icTransactionDetailProduct.TxId)
AND (dbo.icTransactionDetailProduct.PartId = dbo.icTransactionDetail.PartId)
AND (dbo.icTransactionDetailProduct.DetailId = dbo.icTransactionDetail.DetailId)
INNER JOIN dbo.icTransactionDetailGroups ON (dbo.icTransactionDetailProduct.TxId = dbo.icTransactionDetailGroups.TxId)
AND (dbo.icTransactionDetailProduct.PartId = dbo.icTransactionDetailGroups.PartId)
AND (dbo.icTransactionDetailProduct.DetailId = dbo.icTransactionDetailGroups.DetailId)
INNER JOIN dbo.icTransactionDetailGroups icTransactionDetailGroups1 ON (dbo.icTransactionDetailProduct.TxId = icTransactionDetailGroups1.TxId)
AND (dbo.icTransactionDetailProduct.PartId = icTransactionDetailGroups1.PartId)
AND (dbo.icTransactionDetailProduct.DetailId = icTransactionDetailGroups1.DetailId)
LEFT OUTER JOIN dbo.icTransactionDetailPrice ON (dbo.icTransactionDetailProduct.DetailId = dbo.icTransactionDetailPrice.DetailId)
AND (dbo.icTransactionDetailProduct.TxId = dbo.icTransactionDetailPrice.TxId)
AND (dbo.icTransactionDetailProduct.PartId = dbo.icTransactionDetailPrice.PartId)
INNER JOIN dbo.dbProducts ON (dbo.icTransactionDetailProduct.ProductId = dbo.dbProducts.Id)
AND (dbo.icTransactionDetailProduct.ProductId = dbo.dbProducts.Id)
LEFT OUTER JOIN dbo.dbLocations ON (dbo.icTransaction.TxLocation = dbo.dbLocations.Id)
LEFT OUTER JOIN dbo.dbVenues ON (dbo.dbLocations.VenueId = dbo.dbVenues.Id)
WHERE
dbo.icTransaction.TxTradingDate BETWEEN '2015-07-16' AND '2017-12-14'
AND (dbo.icTransactionDetail.DetailType = 'ttSale') OR (dbo.icTransactionDetail.DetailType = 'ttReturn')
AND dbo.icTransactionDetailGroups.GroupName = 'SUBDEPT' AND icTransactionDetailGroups1.GroupName = 'DEPT'
ORDER BY
[Trading Day],
[Transaction Time],
[Cust ID],
[Transaction No]