query with user-define function

I have a table with 3 fields.
In the first field named a there are values i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+3*b (where a,b
supposed to be the contents of the previous fields).
How can I issue a query to get back 5, 9, 32 (5+3*9)'
Many Thanks
HelenNot sure why you want to do this in SQL. Note that if dbo.foo has more than
one row, you will need to limit both queries using a WHERE clause to
identify that single row (unless the expression in c is always the same, in
which case, it shouldn't be in the table at all).
CREATE TABLE dbo.foo
(
a INT,
b INT,
c VARCHAR(32)
)
GO
SET NOCOUNT ON
GO
INSERT dbo.foo SELECT 5,9,'a+3*b'
GO
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b,'+c+' FROM dbo.foo'
FROM dbo.foo
EXEC(@.sql)
GO
DROP TABLE dbo.foo
GO
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen|||Hi,
You can have 3 solutions
1. Direct TSQL . Select a,b,(a+3*b) as c from table_name
2. Create a view. Create view v1 as Select a,b,(a+3*b) as c from table_name
and later use
select * v1
3. Use compute columns while table creation
create table cc(a int, b int, c AS (a + 3 * b))
WHILE INSERTION INSERT DATA ONLY FORM COLUMN a AND b
Thanks
Hari
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:092F2AF9-CF18-4D28-9112-E4D9D459BE79@.microsoft.com...
>I have a table with 3 fields.
> In the first field named a there are values i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+3*b (where a,b
> supposed to be the contents of the previous fields).
> How can I issue a query to get back 5, 9, 32 (5+3*9)'
> Many Thanks
> Helen

Query with user-define function

I have a table with 3 fields. In the first field named a there are values
i.e. 5
In the second field named b there are values i.e. 9
In the third field named c there are expressions i.e. a+@.q+3*b where a,b
supposed to be the contents of the previous fields, different in each row
and @.q is a variable I wound like to type each time I run the query.
I have typed:
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
FROM dbo.foo where Index=1
EXEC(@.sql)
How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
Many Thanks
HelenDECLARE @.q INT
SET @.q = 7
SELECT a,b, a + @.q + 3 *b AS c
FROM dbo.foo
where Index=1
Jacco Schalkwijk
SQL Server MVP
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||I'm sorry. I didn't explain myself correctly. I mean I have this table in a
SQL Server with many rows and different function in each row. Inside the
function I would like to have a variable (@.q) which I don't know how to writ
e
so as when I query I can put a different value each time.

> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>
>|||Hi
Maybe
CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
varchar(10) )
INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
where [Index]=1'
FROM dbo.foo where [Index]=1
EXEC(@.sql)
John
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>|||Helen,
The T-SQL infix expression evaluator here might help:
http://users.drew.edu/skass/SQL/Infix.sql.txt
If you first replace the 'a', 'b', and @.q in your expression
with their values, InFixVal should then evaluate the result.
select
a, b,
dbo. InFixVal(replace(replace(replace(c,'a','
('+str(a,19,4)+')'),'b','('+str(
b,19,4)+')'),'@.q,str(@.q,19,4)),1)
from ...
Also look here, for some examples of its use, and comments
about its limitations. It only evaluates a simple set of possible
arithmetic expressions, but it may be enough for you.
http://groups.google.com/groups?hl=...ver&qt_s=Search
Steve Kass
Drew University
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>I have a table with 3 fields. In the first field named a there are values
> i.e. 5
> In the second field named b there are values i.e. 9
> In the third field named c there are expressions i.e. a+@.q+3*b where a,b
> supposed to be the contents of the previous fields, different in each row
> and @.q is a variable I wound like to type each time I run the query.
> I have typed:
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'SELECT a,b' + c+ ' FROM dbo.foo where Index=1 '
> FROM dbo.foo where Index=1
> EXEC(@.sql)
> How can I write a query where @.q=7 to get back 5, 9, 39 (5+7+3*9)'
> Many Thanks
> Helen
>sql

Query with MAX funtion

Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lieThis is a multi-part message in MIME format.
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=d.DeptName
, e.EmployeeName
from
Depts as =d
join
Employees as =e on e.Dept =3D d.Dept
where
e.Salary ==3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each =department butI would like to select the name of the employee who makes the =highest salary in each department.Can you help me to make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_033B_01C37935.CF55CC40--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where
e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where
= e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_0016_01C37937.E00063F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good point. I just assumed it was stored as money. When you assume, =...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce A. Carson" <bcarson@.asgoth.com> wrote in message =news:eBBfolVeDHA.3576@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Good point. I just assumed it =was stored as money. When you assume, ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce A. Carson" wrote in =message news:eBBfolVeDHA.3576=@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where = e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_037D_01C37939.390DAC40--