Embedded Queries?
in [Using MS SQL Server]
|
Prev: limit resources per user
Next: itzik ben-gan
From: Dranai on 18 Feb 2006 10:47 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
From: Tom Moreau on 18 Feb 2006 11:42 If 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(a)gmail.com> wrote in message news:1140277653.986097.250450(a)g43g2000cwa.googlegroups.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
From: Dranai on 18 Feb 2006 12:23 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.
From: Tom Moreau on 18 Feb 2006 13:46 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(a)gmail.com> wrote in message news:1140283416.620277.318370(a)f14g2000cwb.googlegroups.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.
From: Dranai on 18 Feb 2006 14:08
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 |