Bulding expressions in views

[Jose Perdigao]

in mdb, i can simplify expressions in queries using the the following:



a:field1*field2/24

b:field3/field4

c:a+b



In views I can't do this ( I believe is possible). I must type all
expressions (cfield1*field2/24)+field3/field4)

I have complex expressions, it means, the final expression will be too long
and more complex to evaluate them.



Question.

There is a way to simplify expressions in views, sp or in-line-functions?



Thans,

Jose Perdigão

 

[Razvan Socol]

Hi, Jose

You can use subqueries, like this:

SELECT a, b, a+b AS c FROM (
SELECT column1*column2/24 AS a, column3/column4 AS b
FROM YourTable
) x

Razvan

 

[Jose Perdigao]

If I use subqueries the text is also long.
Well, if I don't have another solution, I will use.
Thanks

 

[aaron.kempf]

you can always stack views on top of views.. i do that more often than
i shoud-- it's just too easy; and it's reliable in ADP.. doing that in
MDB is risky business; it just sporadically errors out

more imortantly

you can use ORDER BY 1, 2, 3 in order to refer to the ordinal column
that you're selecting.. you know what i mean?

so instead of

Select Field1 + Field2 as Field3, Field1, Field2 From myTable order by
Field1 + Field2, Field3, Field1

you can just use this syntax
Select Field1 + Field2 as Field3, Field1, Field2 From myTable order by
1, 2, 3

you notice how oyu dont have to duplicate the expression in the order
by clause? i've always assumed this makes it FASTER because it's
sorting on a previous calculation.. instead of doing the calc twice

i wish i knew more baout how sql worked on the insides

i hope i've helped some

 

[Jose Perdigao]

You are help me and I'm still continues search information about this.

If you have more information, please let me know.



Thanks a lot and have a nice Sunday

Jose perdigao

 

[Vadim Rapp]

Hello Jose,
You wrote in conference microsoft.public.access.adp.sqlserver on Sat, 4 Feb
2006 11:27:02 +0100:

JP> There is a way to simplify expressions in views, sp or
JP> in-line-functions?

consider using user-defined functions in sql server.

Vadim Rapp

 

[Jose Perdigao]

Good Morning Vadim,

I’m disappointed with UDF.

I created two scalar functions to use in views, sp or inlinefunctions and
the result was low performance (too slow)

I give you the examples:



query 1:
SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN dbo. iStdt() AND dbo.iDate())

time:30sec; 31502 rows; 49 columns

query2:

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN
(SELECT iStdt
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())) AND
(SELECT iDate
FROM dbo.A4_Users
WHERE (Login = dbo.iLogin())))

time:8sec; 31502 rows; 49 columns



The query 2 (without functions), the performance is much better than query
1.

Off course the statement of query1 is easier than query 2

Do you think some this is wrong?

I would like to create views with functions in criteria.

From this example, can I conclude, we must not use functions in criteria?





My owner functions:



ALTER FUNCTION dbo.iDate ()
RETURNS datetime
AS
BEGIN
RETURN (SELECT iDate FROM dbo.A4_Users WHERE (Login = dbo.iLogin()))
END



ALTER FUNCTION dbo.iStdt ()
RETURNS datetime
AS
BEGIN
RETURN (SELECT iStdt FROM dbo.A4_Users WHERE (Login = dbo.iLogin()))
END



ALTER FUNCTION dbo.iLogin ()

RETURNS varchar(30)

AS

BEGIN

/* Sql Server autentication mode */

IF CharIndex('\',system_user)=0

RETURN system_user

/* Windows Autentication mode */

RETURN SUBSTRING(system_user,CharIndex('\',system_user)+1,30)

END



I appreciate your suggestions,

José Perdigão

 

[Vadim Rapp]

Hello Jose:
You wrote on Mon, 6 Feb 2006 09:02:43 +0100:

JP> I created two scalar functions to use in views, sp or inlinefunctions
JP> and the result was low performance (too slow)

Yes, this is expected. SQL Server Optimizer does not know what's inside the
function, so it's not optimized. Maybe you can do better with table-based
UDF's.

The ideal solution would be preprocessor. The only capable product I know
that has it is AdeptSQL Workshop - try it out. It seems to be abandoned by
the developer, however.


Vadim Rapp

 

[Sylvain Lafontaine]

When using a function that's returning the same value for all rows of a
query, you should store the value in a local variable whenever possible.

 

[Jose Perdigao]

Hi Sylvain
The fuunction return the same value for all rows. So, how can I store in a
local variable? Could you give me an example?

Thanks
José Perdigão

 

[Sylvain Lafontaine]

If iStdt() and iDate() are of type datetime:

declare @iStdt2 datetime
declare @iDate2 datetime

set @iStd2 = dbo.Stdt()
set @iDate2 = dbo.iDate()

SELECT dbo.J1_Allocation.*
FROM dbo.J1_Allocation
WHERE (dDatez BETWEEN @iStd2 AND @iDate2)

Caution: if you are in batch mode instead of inside a stored procedure, each
time you use the command GO, the declarations and values of previously
defined local variables are lost thereafter. In another way, you start with
a blank slate after each Go command.

 

[Jose Perdigao]

Hi Silvain,

I could apply your example but only as store procedure and I had a good
perfomance. Can we not create views if in the statement has parameters?
Also, I could not create your statment as function, can we not? I don't
like to use store procedures because we can not create views, sp or function
based in store procedures. This is a big disadvantage, isn't it?



Thanks a lot



jose perdigao

 

[Sylvain Lafontaine]

I, sorry, it's my error: this thread and the other one were so long that I
forgot you were talking about using a view.

However, as you want to access this view from ADP, I don't see any advantage
of using a view here; even when you take into account security problems such
as hidding data from others users. In my personal experience, the easiest
(and probably the best) way of accessing the backend database from ADP is by
using SPs instead of Views.

 

[Jose Perdigao]

Hi Sylvain,



Your information, suggestions, was very, very useful.

Thanks a lot

jose perdigao

 

[aaron.kempf]

of course.. if you want to use views-- so that you can JOIN to them--
then you can.

just use the subquery; cut and paste the subquery; instead of using the
function.. i mean.. if it runs faster; who cares how much effort it
takes??