[Stackoverflow] [Progress OpenEdge ABL] Better way to write a WHERE clause with multiple <> conditions from same object

Status
Not open for further replies.
J

Jack Williams

Guest
I have built a statement to return the sizes for an component attached to the order. The issue i have is the query returns all components on the order such as delivery and packing which are irrelevant to me. All together there is over 45 components that i am not interested in seeing. To exclude these i have put into there WHERE clause everything i don't want to see. My problem is i feel this isn't the best way for me to approach with multiple AND's listing each and every one.

To save the hassle i have put the main query below and only a portion of the where clause

SELECT

Job_0.JobJobNumber AS 'Job Number'
,Job_0.JobEstimateNumber AS 'Estimate Number'
,Component_0.CooComponentDescription AS 'Component'
,Component_0.CooMfsDescription AS 'Finished Size'
,Component_0.CooDownSize AS 'Down Size'
,Component_0.CooAcrossSize AS 'Across Size'
,Component_0.CooDownFactor AS 'Down Gap'
,Component_0.CooAcrossFactor AS 'Across Gap'
,MasterWorkingSize_0.MwsDescription AS 'Working Size'
,MasterWorkingSize_0.MwsMinimumWidth AS 'Width'
,MasterWorkingSize_0.MwsMinimumLength AS 'Length'

FROM
SBS.PUB.Job Job_0
JOIN SBS.PUB.Component Component_0 ON Job_0.JobID = Component_0.JobID
JOIN SBS.PUB.WorkingSize Workingsize_0 ON Job_0.JobID = WorkingSize_0.JobID
JOIN SBS.PUB.MasterWorkingSize MasterWorkingSize_0 ON WorkingSize_0.MasterworkingsizeID = MasterWorkingSize_0.MasterworkingsizeID

WHERE

Job_0.JobID > 254677392
AND Component_0.CooComponentDescription <> 'Packing'
AND Component_0.CooComponentDescription <> 'Delivery'
AND Component_0.CooComponentDescription <> Packing in 1,000s
AND Component_0.CooComponentDescription <> Finish Pad of Sets
AND Component_0.CooComponentDescription <> Part Outsourced
AND Component_0.CooComponentDescription <> Finishing Booklets
AND Component_0.CooComponentDescription <> Packing - band in 50s
AND Component_0.CooComponentDescription <> Small Job Uplift £10
AND Component_0.CooComponentDescription <> Packing band in 50s


I was wondering other than what i have done above is there a better way in which i could nest these? Much like you can do in a CASE WHEN statement. what i hope to see is something like AND Component_0.CooComponentDescription <>('Packing', 'Delivery', Packing in 1,000s,etc,etc)

Continue reading...
 
Status
Not open for further replies.
Top