Hi,
Who can help me?
For a BI solution i have a dataset with many tabels (Orders, Orderdetail, etc etc)
In one visual I have to show all the records that is the result of the next query
SELECT DISTINCT o.oKey, o.OrderNumber, o.OrderType, o.AckDate, o.Date, ISNULL(os.TargetShipDate, '') AS TargetShipDate, o.ReqDate
FROM FVMASTER.dbo.Orders o
INNER JOIN FVMASTER.dbo.OrderDetail od ON o.okey=od.okey
INNER JOIN FVMASTER.dbo.OrderDetailParts odp ON od.odKey=odp.odKey
INNER JOIN FVMASTER.dbo.OrderDetailPurchaseReqs odpr ON odpr.odKey=odp.odKey AND odpr.MasterKey = odp.MasterKey AND odpr.ParentKey = odp.ParentKey
LEFT JOIN (
--Retrieve the earliest target ship date for any shipments (includes transfer shipments)
SELECT oKey, MIN(TargetShipDate) AS TargetShipDate
FROM FVMASTER.dbo.OrderShipments
GROUP BY oKey
) os ON o.oKey = os.oKey
WHERE NOT EXISTS(
SELECT *
FROM FVMASTER.dbo.OrderDetail od2
WHERE o.oKey=od2.oKey
AND od2.PurchasingReqsEvaluated=0
AND od2.ItemType<>5 AND od2.SubLineItem >= 0
)
AND o.AckDate<>''
AND o.OrderType IN (1,4,5)
AND o.ClosedDate=''
AND o.Cancelled=0
AND od.PurchasingReqsEvaluated=1
AND odpr.OrderDate=''
GROUP BY o.oKey, o.OrderNumber, o.OrderType, o.AckDate, o.Date, os.TargetShipDate, o.ReqDate
I realy don't know how to realise this.
At this moment I have a seperate SQL view that i load in PowerBi. But this is not the way to go, because this subdataset is not a part of mij datamodel.
