Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Applying constraints to WHERE statement Expand / Collapse
Author
Message
Posted Friday, January 24, 2014 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:48 AM
Points: 4, Visits: 18
Hi,

I have to write code to produce data along the lines of SIZE, COLOUR and PATTERN. I'm coming unstuck because I have to apply specific constraints on PATTERN given a specific SIZE and COLOUR. For example, SIZE=10; COLOUR=R; PATTERNID must not be in (001,009): SIZE=10; COLOUR=G; PATTERNID must not be in (001,002,008) and so on.

I'm not new to programming, but am to SQL and I can't seem to get the syntax right. Any help would be much appreciated!
Post #1534428
Posted Friday, January 24, 2014 4:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:39 AM
Points: 5,408, Visits: 10,054
Here's one way, but I'm not sure that it's the best. Create a table called ForbiddenPatterns with columns Size, Colour, Pattern. Insert one row for each forbidden combination. Write a function that takes Colour, Size and Pattern as parameters and returns Forbidden if the combination supplied appears in ForbiddenPatterns. Add a check constraint to your original table that checks that the result of the function is not Forbidden.

John

Edit - corrected typo
Post #1534433
Posted Friday, January 24, 2014 5:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:48 AM
Points: 4, Visits: 18
Hi John,

Yes - that sounds as though it would work I'm on another task now for the rest of the day, but I'll be sure to give an update when I can. Many thanks!!
Post #1534441
Posted Friday, January 24, 2014 6:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 6,778, Visits: 13,973
CROSS APPLY is an excellent tool for building complex WHERE clauses and would also help you to figure out if it's worthwhile building up an exclusion table as suggested. Once you've constructed your exclusions list in the CROSS APPLY block, you can drop it down into the WHERE clause.
SELECT x.Exclusion, m.* 
FROM MyTable m
CROSS APPLY (
SELECT Exclusion = CASE
WHEN m.SIZE = 10 AND m.COLOUR = 'R' AND m.PATTERNID IN (001,009) THEN 1
WHEN m.SIZE = 10 AND m.COLOUR = 'G' AND m.PATTERNID IN (001,002,008) THEN 2
ELSE NULL END
) x
WHERE x.Exclusion IS NULL



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1534455
Posted Friday, January 24, 2014 6:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:48 AM
Points: 4, Visits: 18
Thanks Chris! That looks like it would be useful for other tasks that I have to perform as well
Post #1534459
Posted Friday, January 24, 2014 6:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 6,778, Visits: 13,973
Anytime.
Third row up in my sig block has links to a couple of outstanding articles on APPLY. Well worth a read.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1534469
Posted Tuesday, January 28, 2014 2:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:48 AM
Points: 4, Visits: 18
Update: using adaptations of the code/ideas you both supplied, I have been able to correct a troublesome report at work. My bosses were very pleased! Thanks again!
Post #1535338
Posted Tuesday, January 28, 2014 2:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:39 AM
Points: 5,408, Visits: 10,054
Happy to help, and I'm glad it turned out so well. Please will you share your solution, in case anyone else has a similar issue and wonders how you resolved it?

Thanks
John
Post #1535339
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse