[Stackoverflow] [Progress OpenEdge ABL] How to get a Postcode to show depending on if an AdhocID is NULL/NOT NULL

Status
Not open for further replies.
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...
 
Status
Not open for further replies.
Top