Query in Formula - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query in Formula

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-08 : 07:13:58

Is it possible to place a query in a calculation/formula column? I want the value of a field/column to be calculated based on a relationship. I'm using SQL Server Management Studio, but it seems I never get the syntax right.

SELECT [AxProd.dbo.CUSTTABLE.VENDEXT] AS Vendedor WHERE [AxProd.dbo.CUSTTABLE.NAME] = [Ingenieria]

Any suggestions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 07:15:28

Where is your FROM part?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 07:15:59

SELECT VENDEXT AS Vendedor
FROM AxProd.dbo.CUSTTABLE
WHERE NAME = 'Ingenieria'

E 12°55'05.63"
N 56°04'39.26"

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-09 : 04:12:54

ups... yes, I forgot to post the from expression. But it wasn't due to that. Somethings generally wrong and I still don´t know if you can place a query in the column's definition using the formula at all?!!? And if one can, how would be the syntax. Here's my whole query:

SELECT AxProd.dbo.EMPLTABLE.NAME
FROM AxProd.dbo.EMPLTABLE INNER JOIN AxProd.dbo.CUSTTABLE
ON AxProd.dbo.EMPLTABLE.EMPLID = AxProd.dbo.CUSTTABLE.VENDEXT LEFT
OUTER JOIN dbo.Estadisticas_Ofertas
ON AxProd.dbo.CUSTTABLE.NAME = dbo.Estadisticas_Ofertas.Ingeneria
WHERE AxProd.dbo.CUSTTABLE.NAME = Ingeneria

Ingeneria is the column/field of that database

I tried with any kinds of brackets... it will not accept this expression, while the QUery itself works fine.

Martin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 04:18:07

And what is your error message?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 04:19:46

[code]SELECT e.NAME
FROM AxProd.dbo.EMPLTABLE AS e
INNER JOIN AxProd.dbo.CUSTTABLE AS c ON c.VENDEXT = e.EMPLID
LEFT JOIN dbo.Estadisticas_Ofertas AS o ON o.Ingeneria = c.NAME
WHERE c.NAME = 'Ingeneria'[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 04:20:10

E 12°55'05.63"
N 56°04'39.26"

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-09 : 04:43:14

The error message just says: (translated from spanish)

ERROR on validating the Formula for the column 'AUTOVENTA'
DO you want to undo the changes?

I hope I made it clear that I'm trying to place that query as a formula in the column properties of that column/field.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 05:01:21

You can always write a FUNCTION that takes a column as an input parameter and return a scalar value.

E 12°55'05.63"
N 56°04'39.26"

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-09 : 05:06:57

That is exactly the way I want to use my query: as a function! The input is the value of a column (Ingeneria), the output is the result of that query... which I assign to column AUTOVENTAS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 05:11:43

CREATE FUNCTION dbo.fnMyFunction (ColNameValue SYSNAME)
RETURNS VARCHAR(1000)
BEGIN
...
END
GO
ALTER TABLE dbo.MyTable ADD MyNewComputedColum AS dbo.fnMyFunction(OtherColNameHere)

E 12°55'05.63"
N 56°04'39.26"

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-17 : 05:27:57

I had been busy with other jobs for the last week, and now coming back to that problem.

Well, I hoped that the creation of a function would be the solution, but unfortunately it doesn't do the job.

ALTER FUNCTION [dbo].[getVEfromIng] (
@string VARCHAR(60)
)
RETURNS VARCHAR(60)
AS
BEGIN
SELECT AxProd.dbo.EMPLTABLE.NAME FROM dbo.Estadisticas_Ofertas INNER JOIN AxProd.dbo.CUSTTABLE ON dbo.Estadisticas_Ofertas.Ingeneria = AxProd.dbo.CUSTTABLE.NAME INNER JOIN AxProd.dbo.EMPLTABLE ON AxProd.dbo.CUSTTABLE.VENDEXT = AxProd.dbo.EMPLTABLE.EMPLID
Return @string
END -- Function

"Select statements included within a function cannot return data to a client." - seems that I can't use a query in a function...

I'm confused and getting nervous as this problem seems to be soooo basic and I don't know how to handle it. Beside the fact that I always get the impression that I'm not making clear my point. I guess I'm missing some fundamental point.

In the record of dbo.TableA I want dbo.TableA.Seller to get the value from dbo.Table_B.Seller if there is a match between dbo.TableA.Industry and dbo.TableB.Industry... I can perfectly visualize the value, but I NEED TO ASSIGN the value to that column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 05:47:29

[code]ALTER FUNCTION [dbo].[getVEfromIng]
(
@Input VARCHAR(60)
)
RETURNS VARCHAR(60)
AS
BEGIN
RETURN ( SELECT TOP 1 AxProd.dbo.EMPLTABLE.NAME
FROM dbo.Estadisticas_Ofertas
INNER JOIN AxProd.dbo.CUSTTABLE ON dbo.Estadisticas_Ofertas.Ingeneria = AxProd.dbo.CUSTTABLE.NAME
INNER JOIN AxProd.dbo.EMPLTABLE ON AxProd.dbo.CUSTTABLE.VENDEXT = AxProd.dbo.EMPLTABLE.EMPLID
WHERE SomeCol = @Input
)
END[/code]

E 12°55'05.63"
N 56°04'39.26"

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-17 : 07:09:27

Hmmm... looks promising, although I don't get the WHERE part right.

@Input is supposed to be dbo.Estadisticas_Ofertas.Ingeneria. (the value that triggers the function)

But if I set
WHERE dbo.Estadisticas_Ofertas.Ingeneria = @Input
I get:
Cannot ALTER 'dbo.getVEfromIng' because it is being referenced by object 'Estadisticas_Ofertas'.

Thank you for your support

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-18 : 06:22:14

The mistake was most likely due to some inaccuracy in my tables and I had to adapt my query to make it work correctly.

The new Query is more complex and placing it into the formula expression gives a new error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Query:

SELECT dbo.Estadisticas_Ofertas.Ingeneria, T.Vendedor
FROM (SELECT AxProd.dbo.CUSTTABLE.NAME, MIN(DISTINCT AxProd.dbo.EMPLTABLE.NAME) AS Vendedor
FROM AxProd.dbo.CUSTTABLE LEFT OUTER JOIN
AxProd.dbo.EMPLTABLE ON AxProd.dbo.CUSTTABLE.VENDEXT = AxProd.dbo.EMPLTABLE.EMPLID
WHERE (AxProd.dbo.CUSTTABLE.DATAAREAID = N'wik')
GROUP BY AxProd.dbo.CUSTTABLE.NAME) AS T INNER JOIN
dbo.Estadisticas_Ofertas ON T.NAME = dbo.Estadisticas_Ofertas.Ingeneria

barnabeck
Posting Yak Master

236 Posts

Posted - 2009-06-20 : 13:36:27

Now it works!!! Thank you Peso.

For some reason it didn't worked in the first place by just modifying a test-function, I had created priorly in order to test how functions can be called from a calculation column... always got the "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" error message
I had to create a new function (although in the end the syntax was identical!!) with that same query and to my surprise it worked right away.

Thanks, Martin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-20 : 16:54:35

You're welcome.

E 12°55'05.63"
N 56°04'39.26"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 18:41:11

The only thing left now is worrying about the performance impact such a function will have on your inserts and updates.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources