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 Timeout in SSAS

I am using Sql 2005 SSAS. When building the Data Source Views using "Edit Named Query", the data source is pointed to a Sql 7.0 server. When I tried to execute the query, it terminates after 30 seconds with "Query Timeout Expired".

Try editing the DataSource object for your SQL 7.0.

You should be able to change query timeout. I beleive it is part of the connection string.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Query String using Web Matrix

Hi folks,

I have two tables in one database.

One table has an automatic numbering primary key named ID and is named rfi.

The other table has a field named rfinumber and is named discussion.

Both ID and rfinumber have a datatype of number.

Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table.

Problem is that I am getting the entire discussion table when I use the following query:

"SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])"

For example

DISCUSSION TABLE
rfinumber
1
1
1
2

RFI TABLE
rfi
1
2

Given the query, I should get a discussion table only listing the rfinumber = 1.

When I run the query from my database package it runs fine??

Any clues?

thanks,
glenn

gmeadows:

Problem is that I am getting the entire discussion table when I use the following query:

That's exactly what I would expect. You are returning a row from discussion matched with each row from rfi where the rfinumber from discussion is the same as the ID from rfi. Why would you expect it only to return those rows where rfinumber = 1? You need to use a parameter of some sort to make that happen.