J
Jack Williams
Guest
I am currently trying to return postcodes for delivery addresses. The issue i am currently experiencing is some of these are Ad hoc addresses so would not refer to the companies standard SiteID. Every record has a CompanySiteID and this is the only point i can JOIN ON between the Ordering Table and the Address Table.
What I want the data to do or if it can be done in the from clause is to say if the DeliveryCompanySiteID is blank then return the postcode for the AdHocDeliveryAddressID if not find the AddressID for the site and then find the postcode for that AddressID
Below is a sample of the tables and what i currently get:
Table 1 - order
CompanySiteID DeliveryCompanySiteID InvoiceCompanySiteID AdHocDeliveryAddressID
8613552 8613552 663401931
94823142 326963198 326963198
633057107 634312178 565578092
Table 2 - Address
AddressID AddPostCode
663401931 NG1 1PQ
123 S12 1TP
456 S1 9PU
789 S11 1TY
Table 3 - company site
CompanySiteID AddressID
8613552 123
94823142 456
633057107 789
Based on the code i am using below this is the result i am getting:
Site ID Site PostCode
8613552 S12 1TP
94823142 S1 9PU
633057107 S11 1TY
The reason i believe this is happening is because of my JOIN which is:
Reason why I get the current result is because my join asks: JOIN SBS.PUB.Address Address_0 ON CompanySite_0.AddressID = Address_0.AddressID
Unfortunately there is no other way to join the tables, So i feel i need something in place that effectively says if the DeliveryCompanySiteID is blank then return the postcode for the AdHocDeliveryAddressID if not find the AddressID for the site and then find the postcode for that AddressID
I have supplied my full code even though i know the samples are focused down to just the SiteId and PostCode:
SELECT
Company_0.CoaCompanyName AS 'Company'
, CompanySite_0.CompanySiteID
, Address_0.AddPostCode AS 'Delivery Post Code'
, Job_0.JobJobNumber AS 'Job'
, SopOrderItem_0.SoiQuantity AS 'Qty'
, StockItem_0.StiQuantityPerUnit AS 'UOM'
, StockItem_0.StiCustomerStockCode AS 'Customer Code'
, SopOrder_0.SooOrderNumber AS 'SOP'
, SopOrder_0.SooOrderDate AS 'Order Date'
, SopProduct_0.SopDescription AS 'Product'
, SopProductType_0.SpyDescription AS 'Product Type'
, StockType_0.SotDescription AS 'Process Method'
, Delivery_0.DelActualQuantity AS 'Delivered'
, Delivery_0.DelWholeQuantity
, Delivery_0.DelProposedDate AS 'Proposed Delivery'
, TemplateRunObject_0.TroValue AS 'Manufactured By'
, (CASE WHEN SopOrder_0.AdHocDeliveryAddressID IS NOT NULL AND SopOrder_0.AdHocDeliveryAddressID = Address_0.AddressID THEN Address_0.AddPostCode ELSE Address_0.AddPostCode END) AS 'TEST'
FROM
SBS.PUB.Job Job_0
JOIN SBS.PUB.Company Company_0 ON Job_0.CompanyID = Company_0.CompanyID
JOIN SBS.PUB.Delivery Delivery_0 ON Job_0.JobID = Delivery_0.JobID
JOIN SBS.PUB.TemplateRunObject TemplateRunObject_0 ON Job_0.TemplateRunID = TemplateRunObject_0.TemplateRunID
JOIN SBS.PUB.SopOrder SopOrder_0 ON Delivery_0.SopOrderID = SopOrder_0.SopOrderID
JOIN SBS.PUB.SopOrderItem SopOrderItem_0 ON Delivery_0.SopOrderItemID = SopOrderItem_0.SopOrderItemID
JOIN SBS.PUB.SopProduct SopProduct_0 ON SopOrderItem_0.SopProductID = SopProduct_0.SopProductID
JOIN SBS.PUB.SopProductType SopProductType_0 ON SopProduct_0.SopProductTypeID = SopProductType_0.SopProductTypeID
JOIN SBS.PUB.StockItem StockItem_0 ON SopProduct_0.SopProductID = StockItem_0.SopProductID
JOIN SBS.PUB.StockType StockType_0 ON StockItem_0.StockTypeID = StockType_0.StockTypeID
JOIN SBS.PUB.CompanySite CompanySite_0 ON SopOrderItem_0.CompanySiteID = CompanySite_0.CompanySiteID
JOIN SBS.PUB.Address Address_0 ON CompanySite_0.AddressID = Address_0.AddressID
WHERE
Job_0.JobCompletedJob = '0'
AND Job_0.JobJob = '1'
AND SopOrder_0.SooStatus = '2'
AND TemplateRunObject_0.ObjectId = '8036818'
AND SopProduct_0.SopProcessMethod<>'2'
ORDER BY
Delivery_0.DelProposedDate DESC
,Company_0.CoaCompanyName ASC
, Address_0.AddPostCode ASC
In the end i hope to be able to get to the position where based on the sample i showed above that the result should return as:
Site ID Site PostCode
8613552 NG1 1PQ
94823142 S1 9PU
633057107 S11 1TY
And not like:
Site ID Site PostCode
8613552 S12 1TP
94823142 S1 9PU
633057107 S11 1TY
I hope i have explained this well enough as i am struggling to explain it to myself at the moment.
Continue reading...
What I want the data to do or if it can be done in the from clause is to say if the DeliveryCompanySiteID is blank then return the postcode for the AdHocDeliveryAddressID if not find the AddressID for the site and then find the postcode for that AddressID
Below is a sample of the tables and what i currently get:
Table 1 - order
CompanySiteID DeliveryCompanySiteID InvoiceCompanySiteID AdHocDeliveryAddressID
8613552 8613552 663401931
94823142 326963198 326963198
633057107 634312178 565578092
Table 2 - Address
AddressID AddPostCode
663401931 NG1 1PQ
123 S12 1TP
456 S1 9PU
789 S11 1TY
Table 3 - company site
CompanySiteID AddressID
8613552 123
94823142 456
633057107 789
Based on the code i am using below this is the result i am getting:
Site ID Site PostCode
8613552 S12 1TP
94823142 S1 9PU
633057107 S11 1TY
The reason i believe this is happening is because of my JOIN which is:
Reason why I get the current result is because my join asks: JOIN SBS.PUB.Address Address_0 ON CompanySite_0.AddressID = Address_0.AddressID
Unfortunately there is no other way to join the tables, So i feel i need something in place that effectively says if the DeliveryCompanySiteID is blank then return the postcode for the AdHocDeliveryAddressID if not find the AddressID for the site and then find the postcode for that AddressID
I have supplied my full code even though i know the samples are focused down to just the SiteId and PostCode:
SELECT
Company_0.CoaCompanyName AS 'Company'
, CompanySite_0.CompanySiteID
, Address_0.AddPostCode AS 'Delivery Post Code'
, Job_0.JobJobNumber AS 'Job'
, SopOrderItem_0.SoiQuantity AS 'Qty'
, StockItem_0.StiQuantityPerUnit AS 'UOM'
, StockItem_0.StiCustomerStockCode AS 'Customer Code'
, SopOrder_0.SooOrderNumber AS 'SOP'
, SopOrder_0.SooOrderDate AS 'Order Date'
, SopProduct_0.SopDescription AS 'Product'
, SopProductType_0.SpyDescription AS 'Product Type'
, StockType_0.SotDescription AS 'Process Method'
, Delivery_0.DelActualQuantity AS 'Delivered'
, Delivery_0.DelWholeQuantity
, Delivery_0.DelProposedDate AS 'Proposed Delivery'
, TemplateRunObject_0.TroValue AS 'Manufactured By'
, (CASE WHEN SopOrder_0.AdHocDeliveryAddressID IS NOT NULL AND SopOrder_0.AdHocDeliveryAddressID = Address_0.AddressID THEN Address_0.AddPostCode ELSE Address_0.AddPostCode END) AS 'TEST'
FROM
SBS.PUB.Job Job_0
JOIN SBS.PUB.Company Company_0 ON Job_0.CompanyID = Company_0.CompanyID
JOIN SBS.PUB.Delivery Delivery_0 ON Job_0.JobID = Delivery_0.JobID
JOIN SBS.PUB.TemplateRunObject TemplateRunObject_0 ON Job_0.TemplateRunID = TemplateRunObject_0.TemplateRunID
JOIN SBS.PUB.SopOrder SopOrder_0 ON Delivery_0.SopOrderID = SopOrder_0.SopOrderID
JOIN SBS.PUB.SopOrderItem SopOrderItem_0 ON Delivery_0.SopOrderItemID = SopOrderItem_0.SopOrderItemID
JOIN SBS.PUB.SopProduct SopProduct_0 ON SopOrderItem_0.SopProductID = SopProduct_0.SopProductID
JOIN SBS.PUB.SopProductType SopProductType_0 ON SopProduct_0.SopProductTypeID = SopProductType_0.SopProductTypeID
JOIN SBS.PUB.StockItem StockItem_0 ON SopProduct_0.SopProductID = StockItem_0.SopProductID
JOIN SBS.PUB.StockType StockType_0 ON StockItem_0.StockTypeID = StockType_0.StockTypeID
JOIN SBS.PUB.CompanySite CompanySite_0 ON SopOrderItem_0.CompanySiteID = CompanySite_0.CompanySiteID
JOIN SBS.PUB.Address Address_0 ON CompanySite_0.AddressID = Address_0.AddressID
WHERE
Job_0.JobCompletedJob = '0'
AND Job_0.JobJob = '1'
AND SopOrder_0.SooStatus = '2'
AND TemplateRunObject_0.ObjectId = '8036818'
AND SopProduct_0.SopProcessMethod<>'2'
ORDER BY
Delivery_0.DelProposedDate DESC
,Company_0.CoaCompanyName ASC
, Address_0.AddPostCode ASC
In the end i hope to be able to get to the position where based on the sample i showed above that the result should return as:
Site ID Site PostCode
8613552 NG1 1PQ
94823142 S1 9PU
633057107 S11 1TY
And not like:
Site ID Site PostCode
8613552 S12 1TP
94823142 S1 9PU
633057107 S11 1TY
I hope i have explained this well enough as i am struggling to explain it to myself at the moment.
Continue reading...