Calcualtion in View 2

[Cpreston]

Hi

I have a view which as a calculation for one field

dbrderHeader.TotalVolume * dbo.Delivery.LineCost

However, I have just noticed we have times where something may be collected so there would not be amounts in the fields. It is therefore leaving these lines out of the the query result.

I have tried something like

Select isnull (dbrderHeader.TotalVolume * dbo.Delivery.LineCost,0)

But it still not returning the ones missing. Could anyone advise how I get them to appear please.

Many thanks

 

[Olaf Doschke]

There is no mecahnuusm suppressing results which are 0 or NULL, so the exclusion must result from the WHERE clause or you need an outer insted of an inner join. So in short, only the whole query would give us an insight about what's to be mended.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Cpreston]

HI

Here is the code as it is, for whatever reason it excludes VehicleName called own collection. All the rest come in. My assumption was that the line that calculates (dbrderHeader.TotalVolume * dbo.Delivery.LineCost) would be null or 0 for own collection. Thanks

SELECT         dbo.JourneyHeader.JourneyDate, dbo.Vehicle.Name, dbo.JourneyHeader.DriverName, dbo.JourneyHeader.VehicleName, 
                      dbo.JourneyHeader.JourneyNumber, dbo.JourneyHeader.TotalVolume, dbo.JourneyLine.DropNumber, dbo.OrderHeader.DeliveryAddress, 
                      dbo.Customer.Name AS [Customer Name], dbo.Customer.County, dbo.Customer.PostCode, dbo.JourneyHeader.NoOfStops, dbo.OrderHeader.udfSalesOrderOTIF, 
                      dbo.JourneyHeader.NoOfPacks, dbo.JourneyHeader.udfLoader1, dbo.JourneyHeader.udfLoader2, dbo.JourneyHeader.TransportCost, dbo.JourneyHeader.Reference, 
                      dbo.DeliveryArea.Name AS DeliveryArea, dbo.OrderHeader.TotalVolume * dbo.Delivery.LineCost AS BistrackTotalCost
FROM         dbo.JourneyHeader INNER JOIN
                      dbo.JourneyLine ON dbo.JourneyHeader.JourneyID = dbo.JourneyLine.JourneyID INNER JOIN
                      dbo.Vehicle ON dbo.JourneyHeader.VehicleID = dbo.Vehicle.VehicleID INNER JOIN
                      dbo.OrderHeader ON dbo.JourneyLine.OrderID = dbo.OrderHeader.OrderID INNER JOIN
                      dbo.Customer ON dbo.OrderHeader.CustomerID = dbo.Customer.CustomerID INNER JOIN
                      dbo.DeliveryArea ON dbo.OrderHeader.DeliveryAreaID = dbo.DeliveryArea.DeliveryAreaID INNER JOIN
                      dbo.Delivery ON dbo.DeliveryArea.DeliveryAreaID = dbo.Delivery.DeliveryAreaID AND dbo.Vehicle.VehicleTypeID = dbo.Delivery.VehicleTypeID
ORDER BY dbo.JourneyHeader.JourneyNumber

 

[dhookom]

Are you missing the entire row or just the calculated value? If the entire row, it suggests there is not a matching record in the "child" table (possibly Delivery). In this case you would need to use a LEFT JOIN rather than INNER JOIN.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Cpreston]

HI

I think the Left Join as worked. Just testing it a bit more but is bringing them through.

Thanks

 

[Olaf Doschke]

Yes, as there is no where caluse the only eason you get no value is becaue INNER JOIN not matching something.

Just notice when you would have records with 0 in them, INNER JOIN would also work, the join conditions are only about IDs, not asking anything about TotalVolume or LineCost.
Both from the way the query is written and from the terms OrderHEADER and Delivery I would conclude Delivery records are missing for some Orderheaders and not the other way around, it's also logic not any order will have a delivery, that only happens as last step.

Whenever you have such a hierarchy and on top of that a chronology meaning some data will only be added later, but you still want to calculate some cost or income summary, you LEFT JOIN table, in which you expect data to be inserted at a löater stage.

The join will not put in 0 for Linecost, but NULL, because a missing match can't give a result. In an overall SUM a NULL will be handled as 0, though more strictly speaking you would also get a NULL if any single summand or factor or concatgenated value of any expression is NULL.

So very strictly programming you would do a LEFT JOIN of the Delivery table and use ISNULL(dbo.Delivery.LineCost,0) instead of dbo.Delivery.LineCost as a factor of dbrderHeader.TotalVolume * ISNULL(dbo.Delivery.LineCost,0)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[dhookom]

Great explanation Olaf.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016