Calculated fields in Views?

Hi
I am running an SQL server with an MS Access front-end.
One of my main forms is a list, which have a number of
calculated fields within the query that sits behind it (in
Access, not in SQL server).
I would like to bring these calculations into a View in
SQL server, to speed things up as I think this is what is
causing this list to hang for a while when it first opens.
The two calcuations are as follows:
DaysOnHold: IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])
WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))
I've discovered that the functions used above are
incompatible with SQL server (I tried creating them
in 'Views' within Enterprise Manager but gave me all sorts
of errors.)
If anyone could assist with the correct phrasing of the
above for the VIews, I'd be extremely grateful!!
Thanks
Russell
hi russell,
since the im not sure what is your exact expression in the query , by
doing some assumption you can convert existing IIF conditions using CASE and
ISNULL functions.
IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])
above expression can be converted to SQL server using isnull condition.
Ex:
isnull( [DateUnsuspended]-[DateSuspended], 0)
WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))
above expression can be converted to SQL server using CASE expression.
Ex:
case when [DateClosed] is null then
( datepart(dd,getdate()) - [JobVacant]-[DaysOnHold])/7
else
( datepart(dd,[DateClosed]) - [JobVacant]-[DaysOnHold])/7 end
Look in books online on the topics "date functions", CASE, ISNULL
Vishal Parkar
vgparkar@.yahoo.co.in
|||Hi Vishal
Thanks for your help. However, I have tried to enter this
expression in a View Column, but get the following error
message:
The Query Designer does not support the CASE SQL construct.
What does this mean? Is there another way to create views
which will allow me to use this CASE function?
Thanks
Russell

>--Original Message--
> hi russell,
> since the im not sure what is your exact expression in
the query , by
>doing some assumption you can convert existing IIF
conditions using CASE and
>ISNULL functions.
> IIf([DateUnsuspended]-[DateSuspended] Is
>Null,0,[DateUnsuspended]-[DateSuspended])
> above expression can be converted to SQL server using
isnull condition.
> Ex:
> isnull( [DateUnsuspended]-[DateSuspended], 0)
> WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
> [JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-
[JobVacant]-
> [DaysOnHold])/7))
> above expression can be converted to SQL server using
CASE expression.
> Ex:
> case when [DateClosed] is null then
> ( datepart(dd,getdate()) - [JobVacant]-[DaysOnHold])/7
> else
> ( datepart(dd,[DateClosed]) - [JobVacant]-
[DaysOnHold])/7 end
> Look in books online on the topics "date functions",
CASE, ISNULL
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>
>.
>
|||hi russell,
Make use of Query analyzer rather than these tools. With Query analyzer
you can execute all t-sql commands and tools like "query designer" have
limited functionality.
Vishal Parkar
vgparkar@.yahoo.co.in
|||Vishal
Thanks for your help! Figured it out and it works!
Thanks again
Russell

>--Original Message--
> hi russell,
> Make use of Query analyzer rather than these tools.
With Query analyzer
>you can execute all t-sql commands and tools like "query
designer" have
>limited functionality.
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>
>.
>

Cache problem in Report Services

Hi All,
We developed 40 reports using Reporting services, all reports having
parameters, data source for all reports are views. I am facing problem
of may be cache. I am using url method to show reports, i wrote a ASP
page on link of report i am using report server url to show like -
http://servername/reportserver/fold...arOrderNo=2067,
but when i click on the link, it shows old data in report and when i
press refresh button given with RS it will refresh and shows the
updated data, every time to get updated data i have to press refresh
button. Can anyone suggest me how to show updated data on report
whenever it gets fired? Please my project submission data is too short,
suggest me asap
Thank You
Regards
Rajeshhi Raj,
I had the same problem (I'm not a developer) but managed to find the cause
and got my developer to fix it.
I don't have the solution to hand now, but if you don't get the solution by
the time I get to work and find the solution, I'll post it by tomorrow
morning.
Immy
p.s. You should try to post this message in the reporting services
newsgroup.
"Raj" <kapse_rajesh@.rediffmail.com> wrote in message
news:1141463255.332391.287870@.z34g2000cwc.googlegroups.com...
> Hi All,
> We developed 40 reports using Reporting services, all reports having
> parameters, data source for all reports are views. I am facing problem
> of may be cache. I am using url method to show reports, i wrote a ASP
> page on link of report i am using report server url to show like -
> http://servername/reportserver/fold...arOrderNo=2067,
> but when i click on the link, it shows old data in report and when i
> press refresh button given with RS it will refresh and shows the
> updated data, every time to get updated data i have to press refresh
> button. Can anyone suggest me how to show updated data on report
> whenever it gets fired? Please my project submission data is too short,
> suggest me asap
>
> Thank You
> Regards
> Rajesh
>|||sorry, forgot to say... it has something to do with the fact that you need
to append the command 'ClearSession=true' to the url at some point.
this should get you on your way if it can't wait until later.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OAF6lY4PGHA.5856@.TK2MSFTNGP10.phx.gbl...
> hi Raj,
> I had the same problem (I'm not a developer) but managed to find the cause
> and got my developer to fix it.
> I don't have the solution to hand now, but if you don't get the solution
> by the time I get to work and find the solution, I'll post it by tomorrow
> morning.
> Immy
> p.s. You should try to post this message in the reporting services
> newsgroup.
> "Raj" <kapse_rajesh@.rediffmail.com> wrote in message
> news:1141463255.332391.287870@.z34g2000cwc.googlegroups.com...
>

Business Rules -> Using Lots of UDFs & Views

I am in the process of building my first "large scale" database system (after 15+ years of developing Windows Apps and Web Apps) - so I am very VERY "Green" when it comes to Database development & SQL et al.

A little context setting: I am building a multi-tier Statistical Analysis & Reporting system where the "end product" will be Reports created in Reporting Services. There are a ton of business rules that I am implementing in a Business Logic Tier (hidden from the "end user" by a Data Access Tier) comprised of SQL in the form of UDFs (scalar) and Views.

The question: I have been reading that UDFs cause a performance hit compared to things like in-line functions. Alot of the Rules (implemented as Scalar UDFs) build on each other so that the output of UDF #1 is used as input to UDF #2.

So far I am implementing the Business Logic as a hierarchy of Views (7 Views to be exact) with each view implementing multiple Rules; each Rule basically a Scalar UDF. Below is an example of what I am doing:

Example

View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView

Then

View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1

Currently I have a hierarchy of 7 views that each use UDFs to implement the Business Rules, where the value calculated from a UDF in one View is used as input to UDF in a View further down the Hierarchy.

Is there a better way of implementing all of the Rules instead of using multiple Views with a bunch of UDFs?

The "end product" dataset is then exposed as a Stored Procedure to the reports in Reporting Services.

Any help would be GREATLY appreciated.

Thanks!
- marty

You can make View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView
and

View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1

as indexed views (if your func's are deterministic ) that will improve performance

see Books Online (look for "indexed views")

To the other part I see a reccurence in building your views; another new feature of SQL 2005 is "common table expressions" that can permit creating recursive queries (look Books Online for "common table expressions" )

|||

Hi!

Well it seems you already got an answer, but here something I would like to add:

For business rules:

use 3NF design (or dw design), use constraints (fk, check constraints, uk/indexes) whenever possible

and use stored procedures as access layer (when not using dw design).

With stored procedures as layer between you highly control access and have freedom to change

your implementation (table design) behind the scenes without breaking code.

This comes escpecially useful, when you have to solve performance problems!

|||You guys Rock!

Using a CTE works like a charm! Using a CTE in a View allows me to use the output of 1 function as input to a 2nd function within the same View.

I am I'm now looking into Indexed Views to see about improving the performance of my Business Rules.

Thanks Again!