February 1, 2020 at 12:04 am
I am very new to SQL. I have completed a Udemy introduction course and have some exposure via MS Access to SQL but not a lot. I am really interested in any directions to a good learning resource as I think that is missing in my understanding somewhere.
Anyway, the issue I am stuck with is:
SELECT dbo.StockDelivered.DeliveryDate, dbo.StockDelivered.Barcode, dbo.StockDelivered.Quantity, dbo.StockDelivered.WaterBodyID, dbo.StockDelivered.RegistrantEmail, ShtStock_1.PartNumber, ShtStock_1.Description,
dbo.Site.SiteName, dbo.WaterBody.WBName, dbo.NZ(dbo.StockLink.AssociatedID, ShtStock_1.ShtStockId) AS RID,
(SELECT PartNumber
FROM dbo.ShtStock
WHERE (ShtStockId = dbo.NZ(dbo.StockLink.AssociatedID, ShtStock_1.ShtStockId))) AS Expr1
FROM dbo.StockDelivered INNER JOIN
dbo.WaterBody ON dbo.StockDelivered.WaterBodyID = dbo.WaterBody.WaterBodyID INNER JOIN
dbo.Site ON dbo.WaterBody.SiteID = dbo.Site.SiteID LEFT OUTER JOIN
dbo.StockLink ON dbo.StockDelivered.ShtStockId = dbo.StockLink.StockID LEFT OUTER JOIN
dbo.ShtStock AS ShtStock_1 ON dbo.StockDelivered.ShtStockId = ShtStock_1.ShtStockId
WHERE (dbo.StockDelivered.DeliveryDate >=
(SELECT MAX(ChangeDate) AS ActChangeDate
FROM dbo.MonthChange
WHERE (MonthChangeID =
(SELECT MAX(MonthChangeID - 1) AS Expr1
FROM dbo.MonthChange AS MonthChange_1)))) AND (dbo.StockDelivered.DeliveryDate <
(SELECT MAX(ChangeDate) AS ChangeDate
FROM dbo.MonthChange AS MonthChange_2))
The problem here is specifically
(SELECT PartNumber
FROM dbo.ShtStock
WHERE (ShtStockId = dbo.NZ(dbo.StockLink.AssociatedID, ShtStock_1.ShtStockId))) AS Expr1
In practical terms I am saying if there is an associatedID then use that to find the part number otherwise use the ShtStockID.
No matter how I configure this it is returning a null value when there is an associatedID.
Independently I know the sub query works, I can also see in RID the corresponding ID being returned correctly in the main query, but nested as I have it...
I am at a loss on what to do next?
February 2, 2020 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 2, 2020 at 12:49 pm
In the FROM clause of the "main query" the table dbo.ShtStock had been LEFT JOINED to based on the ShtStockId. I changed that to be the dbo.NZ(...) function. The "main query" now selects the PartNumber from dbo.ShtStock without the subquery. Maybe this helps? Also, using shorter table aliases makes the code more readable imo.
SELECT
sd.DeliveryDate,
sd.Barcode,
sd.Quantity,
sd.WaterBodyID,
sd.RegistrantEmail,
ss.PartNumber,
ss.[Description],
s.SiteName,
wb.WBName,
dbo.NZ(sl.AssociatedID, ss.ShtStockId) AS RID,
ss.PartNumber
FROM
dbo.StockDelivered sd
INNER JOIN
dbo.WaterBody wb ON sd.WaterBodyID = wb.WaterBodyID
INNER JOIN
dbo.Site s ON wb.SiteID = s.SiteID
LEFT OUTER JOIN
dbo.StockLink sl ON sd.ShtStockId = sl.StockID
LEFT OUTER JOIN
dbo.ShtStock ss ON sd.ShtStockId = dbo.NZ(sl.AssociatedID, ss.ShtStockId)
WHERE
(sd.DeliveryDate >= (SELECT MAX(ChangeDate) AS ActChangeDate
FROM dbo.MonthChange
WHERE (MonthChangeID = (SELECT MAX(MonthChangeID - 1) AS Expr1
FROM dbo.MonthChange AS MonthChange_1))))
AND (sd.DeliveryDate <(SELECT MAX(ChangeDate) AS ChangeDate
FROM dbo.MonthChange AS MonthChange_2));
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply