Embedded Queries?

I'm looking into a problem a friend is having, and I'll say right off
the bat that I work with with php and MySQL, and not MS SQL.

What he is attempting to do (in MS SQL) is take two database fields
from a table (string fields), multiply them together, and put them into
a third field. This third column in the table has not yet been created
the time of running the query.

If it needs to be multiple queries, that is fine. My first thought is
to use a simple ALTER query to add the column to the table, then to
call a UPDATE function which uses a select statement inside of it. I'm
not sure if something like this can even be done.

// ---- Suggested query

UPDATE chrisslu SET 'discquantity' = '(SELECT
chrisslu.quantity*chrisslu.nr_of_disc
FROM chrisslu
WHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE
(str(period,6)>=?Andstr(period,6)<=?)

// ---- End Suggested query

It starts with an UPDATE, but replaces the value to be set with a
SELECT statement. I honestly don't even think this query is
syntactically correct, I'm just trying to get the general concept down
:).

So, question the first: Is this type of query possible? The reason
I'm doing this is because I was told MS SQL has no way of storing
temporary variables... otherwise I would just call a SELECT statement,
store the variable, and UPDATE the new field from the variable after
the ALTER statement.

Second question: If it is possible, am I on the right track, or does
it need to be entered in completely different than what I have?

Third: Regarding the 'type'. Do I need to do any kind of typecasting
or conversion of the fields? Both chrisslu.quantity and
chrisslu.nr_of_disc are string fields (that is what I was told, they
may be varchar of some kind). In order to use them in a math
statement, do they have to be floats, or doubles, or something similar?

I appreciate any response, I know this was a long winded question.

ChrisIf the new column is always to be the product of two other columns, why not
use a computed column:

alter table MyTable
add
MyCol as (Col1 * Col2)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Dranai" <dranai@.gmail.com> wrote in message
news:1140277653.986097.250450@.g43g2000cwa.googlegr oups.com...
I'm looking into a problem a friend is having, and I'll say right off
the bat that I work with with php and MySQL, and not MS SQL.

What he is attempting to do (in MS SQL) is take two database fields
from a table (string fields), multiply them together, and put them into
a third field. This third column in the table has not yet been created
the time of running the query.

If it needs to be multiple queries, that is fine. My first thought is
to use a simple ALTER query to add the column to the table, then to
call a UPDATE function which uses a select statement inside of it. I'm
not sure if something like this can even be done.

// ---- Suggested query

UPDATE chrisslu SET 'discquantity' = '(SELECT
chrisslu.quantity*chrisslu.nr_of_disc
FROM chrisslu
WHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE
(str(period,6)>=?Andstr(period,6)<=?)

// ---- End Suggested query

It starts with an UPDATE, but replaces the value to be set with a
SELECT statement. I honestly don't even think this query is
syntactically correct, I'm just trying to get the general concept down
:).

So, question the first: Is this type of query possible? The reason
I'm doing this is because I was told MS SQL has no way of storing
temporary variables... otherwise I would just call a SELECT statement,
store the variable, and UPDATE the new field from the variable after
the ALTER statement.

Second question: If it is possible, am I on the right track, or does
it need to be entered in completely different than what I have?

Third: Regarding the 'type'. Do I need to do any kind of typecasting
or conversion of the fields? Both chrisslu.quantity and
chrisslu.nr_of_disc are string fields (that is what I was told, they
may be varchar of some kind). In order to use them in a math
statement, do they have to be floats, or doubles, or something similar?

I appreciate any response, I know this was a long winded question.

Chris|||That sounds like an excellent idea to look into. Do you know if SQL
will do the math on character fields and insert the data into a float
field?

Occam's law, right? Great suggestion, I'll look into it, thanks.|||You'd just have to cast things (and hope that there were no bugs in the
original data when you added the computed column). Going forward, the two
input columns would have to be numeric:

cast (Col1 as int) * cast (Col2 as int)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Dranai" <dranai@.gmail.com> wrote in message
news:1140283416.620277.318370@.f14g2000cwb.googlegr oups.com...
That sounds like an excellent idea to look into. Do you know if SQL
will do the math on character fields and insert the data into a float
field?

Occam's law, right? Great suggestion, I'll look into it, thanks.|||Great, that is what I was looking for. I am very unfamiliar with the
SQL language, so I had no idea how to typecast. I did some web
browsing for how to do it, and honestly I got too many different
answers, so I wasn't sure which one to use. I was attempting to use
CONVERT(int, col1) first.

Thank you again for the help.

Chris|||Convert works, too. Cast is ANSI, while convert is T-SQL only.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Dranai" <dranai@.gmail.com> wrote in message
news:1140289709.094990.39720@.o13g2000cwo.googlegro ups.com...
Great, that is what I was looking for. I am very unfamiliar with the
SQL language, so I had no idea how to typecast. I did some web
browsing for how to do it, and honestly I got too many different
answers, so I wasn't sure which one to use. I was attempting to use
CONVERT(int, col1) first.

Thank you again for the help.

Chris|||Hey Chris,

Does your friend NEED to store the results of the mathematical
operation in the table, or will the value of this column always be
dependant on the other two columns? If the latter, then simply do the
calculation in the query returning data. No need to waste storage
space (or worry about validation) if the information stored is enough
to calculate your values.

Of course, Tom's suggestion about computed columns doesn't have
physical storage issues, but you have to be aware of the nature of a
computed column when doing an INSERT or UPDATE statement against the
table.

Stu|||Hmm...

I believe the answer to the question is "both". My friends needs to
store the results in the table as a new column, so that later queries
can access the data.

Your last paragraph has given me pause. Could using a computed column
from the query cause problems later on?

The query I was able to come up with is:

// -- Query --
add discquantity as ((cast(chrisslu.quantity as int)) *
cast(chrisslu.nr_of_disc as int))
// -- End Query --

And is returning "function name is missing )". Any thoughts on this?
I didn't believe that the query was related to any function call,
unless each query is considered a function call in MS SQL.

I definately believe we're getting close to a solution here.

Chris|||>From what I can see, you need to include the ALTER TABLE; eg:

ALTER TABLE chrisslu
ADD discquantity AS ((CAST(chrisslu.quantity AS int)) *
CAST(chrisslu.nr_of_disc as int))

As far as whehter or not to use a computed column, it shouldn't be a
problem if:

1. The value of the column is always dependant on the relationship with
the other columns;
2. You always use explicit column names when inserting or updating
data, and;
3. You don't plan on porting this database to another RDBMS engine.

Stu|||OK, great, none of those should be problems. Thank you for the
clarification.

Chris