Need help with Boolean in Where

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with Boolean in Where

Author

Topic

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 10:44:14

I'm building a SQL report so our users can quickly search for a specific "work center" with a "order minimum" above or below a given amount. The purpose is to identify items that might possibly need a work center changed based on that order minimum.

I let the user enter the work center, the threshold, the operator (<,>,=) and choose whether to search for additional work centers. The activator must be set to 1 for it to return results. This is my Where clause:


WHERE
((@wc1op = '>'  AND @wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN >  @wc1val) OR
 (@wc1op = '>='	AND @wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN >= @wc1val) OR
 (@wc1op = '='	AND @wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN =  @wc1val) OR
 (@wc1op = '<='	AND @wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN <= @wc1val) OR
 (@wc1op = '<'	AND @wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN <  @wc1val))
OR
((@wc2op = '>'  AND @wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN >  @wc2val) OR
 (@wc2op = '>='	AND @wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN >= @wc2val) OR
 (@wc2op = '='	AND @wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN =  @wc2val) OR
 (@wc2op = '<='	AND @wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN <= @wc2val) OR
 (@wc2op = '<'	AND @wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN <  @wc2val))

The code repeats this up to wc8. The problem is it only returns anything for wc1. After that it does not seem to work.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 10:53:10

1. How are you building your SQL report? in Visual studio (BIDS) or another tool like access?
2. What if tomorrow there are 100 work centers?
3. can you post a snapshot of your report designer?

If you don't have the passion to help people, you have no passion

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 11:02:22

1. I'm building it in SQL Server Management Studio as a stored procedure. We have an ERP called SyteLine which uses Crystal Reports for its report engine. After I finish the SP for getting the data I go into CR and finish the visual piece of it.
2. Good point... we have 8 now and its been that way for many years now. It is not likely that we will add more, but if we do it will only be 1 at a time.
3. You mean Crystal Reports? Or SQL management studio?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 11:10:17

do you have n+1 drop downs for work centers? can you post the report design in Crystal reports as an image

If you don't have the passion to help people, you have no passion

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 11:36:32

I don't have any report designed in CR right now, my results came from executing the SP in SQL manager. By n+1 drop downs, you mean...? I can make drop downs lists in SyteLine, if that's what you mean.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 11:54:40

sorry, I meant to say based on your sproc design it looks like you might have 8 drop downs for work center? check this thread out

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140251&SearchTerms=dynamic,report,parameters

If you don't have the passion to help people, you have no passion

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 11:54:44

Are you implying that my boolean logic won't work and you're trying to find another way? I tried a case statement too but kept hitting error messages from SQL.


CASE
	WHEN @wc1op = '>'	THEN (@wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN >  @wc1val)
	WHEN @wc1op = '>='	THEN (@wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN >= @wc1val) 
	WHEN @wc1op = '='	THEN (@wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN =  @wc1val)
	WHEN @wc1op = '<='	THEN (@wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN <= @wc1val)
	WHEN @wc1op = '<'	THEN (@wc1act = 1 AND jobroute.wc = @wc1 AND item.ORDER_MIN <  @wc1val)
END
OR
CASE
	WHEN @wc2op = '>'	THEN (@wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN >  @wc2val)
	WHEN @wc2op = '>='	THEN (@wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN >= @wc2val) 
	WHEN @wc2op = '='	THEN (@wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN =  @wc2val)
	WHEN @wc2op = '<='	THEN (@wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN <= @wc2val)
	WHEN @wc2op = '<'	THEN (@wc2act = 1 AND jobroute.wc = @wc2 AND item.ORDER_MIN <  @wc2val)
END

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 11:59:52

Yes I have a drop down for each work center. My plan is to have the form look like this:

WC1: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)
WC2: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)
WC3: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)
WC4: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)
WC5: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)
WC6: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)
WC7: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)
WC8: Include (Checkbox) Work Center: (drop-down) Operator (<,>,=) Value: (threshold)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 12:29:17

ok that explains that stored procedure. instead of a drop down for each work center why not have

1. one and only one drop down that has as first choice "All Work Centers" (with hidden main id column of NULL or -1 for "All Work Centers")
2. A list of work centers with check boxes

If you don't have the passion to help people, you have no passion

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 12:39:54

1. The threshold and operator could and will be different for each work center entered, so one drop down with All work centers won't work.
2. You mean have the work center names hardcoded, with no drop down list? I would consider this if it will solve my problem.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 12:55:01

no I would definitely not hard code it. do you have a table of work centers with primary key?

If you don't have the passion to help people, you have no passion

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 14:26:20

Yes there is a table w/work centers.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 14:57:45

does work centers table have a primary key? what other tables are involved here and how are they related to work centers table? please show us table schema of tables involved

If you don't have the passion to help people, you have no passion

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 15:35:39

Nothing is actually coming from the work centers table... it just exists and holds the available works centers that can be used for an item's routing. The item's routing is on the 'job_route' table, and the order minimum is on the 'item' table. I have to use the 'job' table to link job_route to item. My select clause looks like this:


SELECT
job.item,
MAX(item.description) as description,
MAX(item.order_min) as order_min,
MAX(item.lot_size) as lot_size,
MAX(jobroute.wc) as wc
FROM job
INNER JOIN jobroute ON jobroute.job=job.job
INNER JOIN item ON item.item=job.item
WHERE...

How do I find the schema for these tables to post it? The CREATE statement right?

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 15:55:35

I feel like maybe I'm not being specific enough about my expected result. You saw how I plan to have the user enter information above and example might be something like this. All 8 work center fields will be defaulted in because we generally already know what the thresholds should be, though editable in-case they want to change it. This was the reason to have a activator, the user could run the report for all 8 wc's if they wanted, or just 1 or 2 by un-checking the boxes..

WC1: Include (X) Work Center: G (Operator) >= (Value:) 2
WC2: Include (X) Work Center: LCM (Operator) <= (Value:) 6
WC3: Include (0) Work Center: LCS (Operator) <= (Value:) 6
...

The expected results:

Item------Work Center-----Order Min
22554-----LCM-------------4
22555-----LCM-------------1
12504-----G---------------10
11556-----G---------------20

In our machine shop we have manual machines and CNC machines. CNC machines take a long time to set up and should not be used for items that have a order minimum less than X (user entered value). Manuals have no setup but take longer to run the parts, if the order min is greater than Y it should be on a CNC. The purpose of this report is to identify items that should have their routing changed based on the order_min qty.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 16:06:37

so you mean to tell me that jobroute.wc holds work center names? so if someone changes a name in Work centers from Basement to Bazement you have a disconnect? sorry to say you got a lot of issues with your design. anyways you can try this


WHERE item.ORDER_MIN = 
        CASE WHEN @wc1act = 1 THEN 
           CASE @wc1op
              WHEN '>' THEN > @wc1val
              WHEN '>=' THEN >= @wc1val
              WHEN '=' THEN = @wc1val
              WHEN '<=' THEN <= @wc1val
              WHEN '<' THEN < @wc1val
           END   
        CASE WHEN @wc2act = 1 THEN 
              WHEN '>' THEN > @wc2val
              WHEN '>=' THEN >= @wc2val
              WHEN '=' THEN = @wc2val
              WHEN '<=' THEN <= @wc2val
              WHEN '<' THEN < @wc2val
        CASE WHEN @wc3act = 1 THEN 
              WHEN '>' THEN > @wc3val
              WHEN '>=' THEN >= @wc3val
              WHEN '=' THEN = @wc3val
              WHEN '<=' THEN <= @wc3val
              WHEN '<' THEN < @wc3val

real oogly

If you don't have the passion to help people, you have no passion

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 16:13:02

There is a separate table called wc that holds the wc names. When setting up the job route for a particular items you can choose from any of the work centers in the wc table. The wc name in the wc table is the primary key so it cannot be changed.

mmccardle
Starting Member

43 Posts

Posted - 2010-10-25 : 16:17:52

I see the WC activator, operator and value, but the work center itself is not in your code. The work center is fed into the SP as wc1, wc2, wc3, etc.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 16:21:03

good point. I was just trying to see if you followed the logic so go ahead and add that and see if it works and post back it if is working.

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 16:23:55

read up here

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

meant to say


WHERE 
        CASE WHEN @wc1act = 1 THEN 
           CASE @wc1op
              WHEN '>' THEN item.ORDER_MIN  > @wc1val
              WHEN '>=' THEN item.ORDER_MIN  >= @wc1val
              WHEN '=' THEN item.ORDER_MIN  = @wc1val
              WHEN '<=' THEN item.ORDER_MIN  <= @wc1val
              WHEN '<' THEN item.ORDER_MIN  < @wc1val
           END

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-26 : 12:57:41

quote:
Originally posted by mmccardle

I tried a case statement too but kept hitting error messages from SQL.

There is no CASE statement in SQL; there is a CASE expression that returns a single scalar value.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources