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
>
>.
>