1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

Better way to write a WHERE clause with multiple <> conditions from same object

Discussão em 'StackOverflow' iniciado por fdantas, Julho 17, 2019.

  1. fdantas

    fdantas Administrator Moderador

    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...

Compartilhe esta Página