error: An expression of non-boolean type specifie

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 2008 Forums

Transact-SQL (2008)

error: An expression of non-boolean type specifie

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2010-07-28 : 01:48:15

The following query works correctly and correctly returns the two rows I expect. It is a many to 1 relationship.

SELECT * FROM PHYSICALINV PI, ICFSS SS
WHERE (PI.Stores_Code = SS.STORES_CODE) AND (PI.Part_ID = SS.PART_ID) AND (SS.PART_ID= '100-001135') AND (PI.QTY_VAR < 0)

Now I want to be able to update the ICFSS table where the results from the previous query are found. I have the following update statement:

declare @TODAY CHAR(10)

SET @TODAY = (SELECT
CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY])

UPDATE ICFSS
SET ISSUES_MTD = (ISSUES_MTD + PHYSICALINV.NEW_QTY), ISSUES_YTD = (ISSUES_YTD + PHYSICALINV.NEW_QTY),
ON_HAND_QTY = (ON_HAND_QTY + PHYSICALINV.QTY_VAR), DATE_LAST_ISSUE = @TODAY, DATE_LAST_ACT = @TODAY, OPERATOR_ID = 'SYS.CD',
TIME_LAST_UPDT = convert(char(5),getdate(),108), DATE_LAST_UPDT = convert(char(11),getdate(),101)
WHERE (SELECT * FROM PHYSICALINV PI, ICFSS SS
WHERE (PI.Stores_Code = SS.STORES_CODE) AND (PI.Part_ID = SS.PART_ID) AND (SS.PART_ID= '100-001135') AND (PI.QTY_VAR < 0))

The correlated subquery is a copy of the one from above that works. However, when I run my update, I get the following error:
Msg 4145, Level 15, State 1, Line 13
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

I have tried joins and all other kinds of syntax and can't get by this one. A little help would be great.

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 02:18:37

Try this


UPDATE SS 
SET ISSUES_MTD = (ISSUES_MTD + PHYSICALINV.NEW_QTY), ISSUES_YTD = (ISSUES_YTD + PHYSICALINV.NEW_QTY), 
ON_HAND_QTY = (ON_HAND_QTY + PHYSICALINV.QTY_VAR), DATE_LAST_ISSUE = @TODAY, DATE_LAST_ACT = @TODAY, 
OPERATOR_ID = 'SYS.CD',
TIME_LAST_UPDT = convert(char(5),getdate(),108), DATE_LAST_UPDT = convert(char(11),getdate(),101)
FROM PHYSICALINV PI INNER JOIN ICFSS SS
ON (PI.Stores_Code = SS.STORES_CODE) AND (PI.Part_ID = SS.PART_ID) AND (SS.PART_ID= '100-001135') AND (PI.QTY_VAR < 0)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-28 : 06:42:42

Dont convert dates to varchar during the comparision
Make sure to read this
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

texassynergy
Starting Member

26 Posts

Posted - 2010-07-28 : 11:38:48

Thanks to both of you. I got it to work with your input and a slight modification. Just one more question for you. The results of the subquery returns two records. I was assuming that the Update would transact on both records. When I run this update wide open (without selecting a specific part number), I want it to process all records. Right now the Update is only updating the fields in the ICFSS table based on the values in the first record the subquery returns and not any subsequent records.

Do you have any ideas on that. I don't think I should need a flow control statement because the update should be processing against all records in a table, correct?

texassynergy
Starting Member

26 Posts

Posted - 2010-07-29 : 13:19:33

I found the solution I needed. I created a temp table summing up the data from PHYSICALINV. Then I could update the ICFSS table. Thanks for your suggestions.

Celko
Starting Member

23 Posts

Posted - 2010-07-30 : 19:10:32

Why did you convert a temporal value to a string when SQL has a temporal data types? Only COBOL Programers from the 1950's make that fundamental error. They had to store dates as strings back then. Why did you use getdate() instead of the ANSI/ISO standard CURRENT_TIMESTAMP?

You have a WHERE clause in an UPDATE that is a select statement and not a predicate. Think about that. Did you drop an EXISTS() in the code?

You carry audit data in the the table being audited. It is not just dangerous, it is illegal in many cases. Find the “last_issue_date” from a third party tool on the log files when the disk crashes.

You don't know ISO-11179 naming rules (i.e. date is a property and not an attribute, so data element names use it as a suffix). You might want to learn them.

Finally, your code and mindset are going to lead to doing a temp table or worse. DON'T. Take a day off and read about the MERGE statement. Your target table will be ICFSS and the Physical Inventory will be the source table.

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources