Using Comparison Operators in Query Expression

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

General SQL Server Forums

New to SQL Server Programming

Using Comparison Operators in Query Expression

Author

Topic

huddie
Starting Member

6 Posts

Posted - 2006-09-14 : 12:27:09

I'm trying to put an expression into a view query column. I've already created a column which contains the results of a calculation in the form of an expression. What I don't know how to do is use comparison operators to return a boolean value for display. I'll give some examples of what I'm trying to do, using literal values just to simplify things...

So say I want to display the result of an expression in a view query column, as a boolean value (or BIT), as True or False. I've tried putting the following expressions into the column and none work:

4 < 5: Access converts this to the string '4 < 5' and it's just displayed as a string.

CAST( '4 < 5' AS BIT ): When run, the query displays the error 'Syntax error converting the varchar value '4 < 5' to a column of data type BIT.

Although I'm using Access 2003, It's just a front end for a SQL Server 2000 database. The actual SQL statement containing the latter SQL function is:

SELECT dbo.Tapes.No, dbo.Tapes.Status, dbo.Tapes.LastWrittenDate, dbo.Tapes.Pool, dbo.Tapes.Location, dbo.TapeJobs.No AS Expr1, dbo.TapeJobs.JobNo,
dbo.TapeJobs.TapeNo, dbo.Jobs.No AS Expr2, dbo.Jobs.Date, dbo.Jobs.Successful, dbo.Jobs.RetentionRule, dbo.RetentionRules.No AS Expr3,
dbo.RetentionRules.Name, dbo.RetentionRules.Description, dbo.RetentionRules.RetentionPeriod, dbo.RetentionRules.RetentionCycles,
dbo.Jobs.Date + dbo.RetentionRules.RetentionPeriod AS AgedJob, CAST('4 < 5' AS BIT) AS Expr4
FROM dbo.RetentionRules INNER JOIN
dbo.Jobs ON dbo.RetentionRules.No = dbo.Jobs.RetentionRule RIGHT OUTER JOIN
dbo.Tapes RIGHT OUTER JOIN
dbo.TapeJobs ON dbo.Tapes.No = dbo.TapeJobs.TapeNo ON dbo.Jobs.No = dbo.TapeJobs.JobNo

I'm new to SQL. I think the CAST function is the way forward, but I just don't know how to force it to resolve the expression '4 < 5'.

Can someone please help ?

--
Paul Anderson

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-14 : 12:48:10

what is that meant to do? cast is used to convert an expression...4 < 5 is as logical operand, not an expression

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

huddie
Starting Member

6 Posts

Posted - 2006-09-14 : 12:55:59

quote:
Originally posted by X002548

what is that meant to do? cast is used to convert an expression...4 < 5 is as logical operand, not an expression

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Ok. What I'm trying to do is use an expression which returns True or False. This boolean value will be displayed in the query column. So how do I specify an expression instead of a logical operand ?

--
Paul Anderson

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-14 : 12:59:09

But this

CAST('4 < 5' AS BIT) AS Expr4

is basically a literal and 4 is always less that 5, hence always TRUE

Not sure what you want...it's not based on any data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-14 : 13:10:55

CAST isn't going to help you, because it just takes a data value and tries to change it to a different type. You are trying to actually have it evaluate an expression and CAST isn't going to do that. SQL Server doesn't have something that will do that for a view. You could do it in a stored procedure with dynamic SQL but I don't think that's what you want in this case either.

I'd say that you want to evaluate it where you are creating it, whatever code you are using to create that string in the first place should be able to evaluate it so you put the result of the evaluation in that column instead of the string expression.

So the question is where does the '4 < 5' expression come from?

huddie
Starting Member

6 Posts

Posted - 2006-09-14 : 13:13:53

quote:
Originally posted by X002548

But this

CAST('4 < 5' AS BIT) AS Expr4

is basically a literal and 4 is always less that 5, hence always TRUE

Not sure what you want...it's not based on any data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Thanks Brett.

Ok. I'll use an example which is what I really want to do. For this I get the error "Syntax error converting the varchar value 'AgedJob < GetDate()' to a column of data type bit.":

CAST('AgedJob < GetDate()' AS BIT)

AgedJob is a column (field) containing dates. I want it to report True for each record if the date is earlier than today, so I'm trying to submit 'AgedJob < GetDate()' as an expression to CAST, which will convert it to a boolean BIT. I know the current syntax I'm using infers a literal value instead of an expression. How do I put the comparison into CAST as an expression ?

--
Paul Anderson

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-09-14 : 13:16:22

How about a case statement.

CASE WHEN Column1 < Column2 THEN 0 ELSE 1 END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-14 : 13:18:05

I figured it was something like that