Advice on T-SQL Solution using field expressions

I have a question about a expression used in access and would like to know
if there is a way to simulate the functionality in SQL.
The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
In access would return either 1 or 0 or the calculated value if there was
one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
boolean or calcualated result based on the condition. How can I accomplish
this SQL? At times the expression could get complicated and use several
other fields from the table TBL_QUATIONTEST in the expression.
ex:
SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
FROM TBL_QUATIONTEST
Any adivce on how I can repliacte this in SQL would help. I truely simeply
need to evaluate it to a boolean value or the caluculated reslut.
Beat Reagrds.Can you please provide DDL, sample data (both "calculated value if there was
one" and one without a calculated value), and desired results.
See http://www.aspfaq.com/5006 if you have trouble with this request...
"Tim Harvey" <papow@.yahoo.com> wrote in message
news:uyZw4zWFGHA.3900@.TK2MSFTNGP10.phx.gbl...
>I have a question about a expression used in access and would like to know
>if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
> one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
> boolean or calcualated result based on the condition. How can I
> accomplish this SQL? At times the expression could get complicated and use
> several other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
> need to evaluate it to a boolean value or the caluculated reslut.
>
> Beat Reagrds.
>|||No problem.
The Access SQL is "almost" the same as SQL Server T-SQL,
in your case with multiple fields and expressions, it is very alike.
/ola
Tim Harvey wrote:
> I have a question about a expression used in access and would like to know
> if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
> one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
> boolean or calcualated result based on the condition. How can I accomplis
h
> this SQL? At times the expression could get complicated and use several
> other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
> need to evaluate it to a boolean value or the caluculated reslut.
>
> Beat Reagrds.|||Tim Harvey wrote:
> I have a question about a expression used in access and would like to
> know if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there
> was one, where NetUsage is a field of TBL_QUATIONTEST and would
> evaluate to a boolean or calcualated result based on the condition.
> How can I accomplish this SQL? At times the expression could get
> complicated and use several other fields from the table
> TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely
> simeply need to evaluate it to a boolean value or the caluculated
> reslut.
> Beat Reagrds.
What you posted will work. Just watch your divisions by integers if your
intent is to keep the decimal value intact:
SELECT (12.06+ ((<Col1> / 750.0 + <Col2> ) * .332)) AS "Expr1"
FROM <Table>
David Gugick
Quest Software
www.quest.com|||In addition to the other posts:
There's no Boolean datatype in TSQL, so below will not compile:
SELECT au_lname <> 'White', au_lname FROM authors
But watch out for below:
SELECT au_lname = 'White', au_lname FROM authors
Above will produce a column names au_lname as the first column with all rows
having the string
"White" in that column. I.e., same as below (which is ANSI SQL and preferred
way):
SELECT 'White' AS au_lname, au_lname FROM authors
(Pretty crappy semantics in above, but should show the point I'm trying to m
ake.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tim Harvey" <papow@.yahoo.com> wrote in message news:uyZw4zWFGHA.3900@.TK2MSFTNGP10.phx.gbl.
.
>I have a question about a expression used in access and would like to know
if there is a way to
>simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
one, where NetUsage is a
> field of TBL_QUATIONTEST and would evaluate to a boolean or calcualated re
sult based on the
> condition. How can I accomplish this SQL? At times the expression could ge
t complicated and use
> several other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
need to evaluate it to
> a boolean value or the caluculated reslut.
>
> Beat Reagrds.
>|||Not sure anyone answered the question you actually asked, change your query
from:
select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST
To:
select case when NetUsage<>0 then 1 else 0 end as Expr1
FROM TBL_QUATIONTEST
Like Aaron said, there is not a boolean type, so you have to deal with this
in a CASE expression.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Tim Harvey" <papow@.yahoo.com> wrote in message
news:uyZw4zWFGHA.3900@.TK2MSFTNGP10.phx.gbl...
>I have a question about a expression used in access and would like to know
>if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
> one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
> boolean or calcualated result based on the condition. How can I
> accomplish this SQL? At times the expression could get complicated and use
> several other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
> need to evaluate it to a boolean value or the caluculated reslut.
>
> Beat Reagrds.
>