Problem with Boolean Nullable parameter in Report Viewer

I have a problem with a nullable boolean parameter.
By default, the value is Null and that's working fine.

The User clicks either true or false, view the report: fine again.

However, from that point on, if the user checks the Null checkbox and
submits the reports, it reverts automatically back to the last True or
False value.

Is this a known problem? Or am I doing something wrong?

Eric

You aren't doing anything wrong. This is a bug. Unfortunately, there is no workaround. If this is causing you signficant problems, please contact customer support directly for a solution.

http://www.microsoft.com/services/microsoftservices/srv_support.mspx

|||

Thanks for the answer.

Is it reasonable to assume that this will get fixed in the next service pack, whenever this is?

|||I can't make any promises at this point (there are many factors involved), but I am pushing for it.|||Thanks

Problem with attached view

I write you because I have a big problem with request access : when
I use a sql server wiev in access (attached view) and i join it with
a local access table, the sql server trace say sql server send all
the data of the view to a access . This view have 15 000 000 rows
When i do the same with an sql server table (attached table) , i
have a very fast answer. every data of the local table is send to
sqlserver (exec sp_execute 1, N'205513214066535435'), why it doesn't
happen this with the view.
there is no difference between the table and the view.
my table is :dbo.table
my view is : create view vtable as select * from dbo.table
Hi
Show us the code you use to call the view.
Where is the where clause?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"toni" <toni@.discussions.microsoft.com> wrote in message
news:2282D883-62D2-4810-9442-418C36148C9C@.microsoft.com...
> I write you because I have a big problem with request access : when
> I use a sql server wiev in access (attached view) and i join it
> with
> a local access table, the sql server trace say sql server send all
> the data of the view to a access . This view have 15 000 000 rows
> When i do the same with an sql server table (attached table) , i
> have a very fast answer. every data of the local table is send to
> sqlserver (exec sp_execute 1, N'205513214066535435'), why it
> doesn't
> happen this with the view.
> there is no difference between the table and the view.
> my table is :dbo.table
> my view is : create view vtable as select * from dbo.table
|||Hello,
The local acces table have one field id
The linked view sqlserver_view
The linked table sqlserver_table
in access the request is:
SELECT *
FROM sqlserver_view.view INNER JOIN local_acces.table ON
local_acces.table.id = sqlserver_view.view.id
All the datat of the view are send (odbc error after many minutes)
the trace give :
sqlbatchcompleted
SELECT *FROM sqlserver_view.view
when i do the same with the linked table sqlserver_table.table
SELECT *
FROM sqlserver_table.table INNER JOIN local_acces.table ON
local_acces.table.id = sqlserver_table.table.id
the data are send immediatly
the trace give
rpc.completed
declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT
*FROMsqlserver_table.table WHERE ("Carte_SAM" = @.P1)', N'453335736479610200'
select @.P1
Sorry , I don't speak a very good english because I'm spanish
regards
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Show us the code you use to call the view.
> Where is the where clause?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "toni" <toni@.discussions.microsoft.com> wrote in message
> news:2282D883-62D2-4810-9442-418C36148C9C@.microsoft.com...
>
>
|||The linked view sqlserver_view
The linked table sqlserver_table
and
the sqlserver_view is
create view dbo. sqlserver_view
as
select
*
from
dbo. sqlserver_table
regards
"toni" wrote:
[vbcol=seagreen]
> Hello,
> The local acces table have one field id
> The linked view sqlserver_view
> The linked table sqlserver_table
> in access the request is:
> SELECT *
> FROM sqlserver_view.view INNER JOIN local_acces.table ON
> local_acces.table.id = sqlserver_view.view.id
> All the datat of the view are send (odbc error after many minutes)
> the trace give :
> sqlbatchcompleted
> SELECT *FROM sqlserver_view.view
> when i do the same with the linked table sqlserver_table.table
> SELECT *
> FROM sqlserver_table.table INNER JOIN local_acces.table ON
> local_acces.table.id = sqlserver_table.table.id
> the data are send immediatly
> the trace give
> rpc.completed
> declare @.P1 int
> set @.P1=1
>
> exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT
> *FROMsqlserver_table.table WHERE ("Carte_SAM" = @.P1)', N'453335736479610200'
> select @.P1
>
> Sorry , I don't speak a very good english because I'm spanish
>
>
>
> regards
> "Mike Epprecht (SQL MVP)" wrote:
|||The view haven't clause where, it's a simply select * from the table
"toni" wrote:
[vbcol=seagreen]
> The linked view sqlserver_view
> The linked table sqlserver_table
> and
> the sqlserver_view is
> create view dbo. sqlserver_view
> as
> select
> *
> from
> dbo. sqlserver_table
>
> regards
>
>
> "toni" wrote:

Problem with a view (not a simple one)

Hi,

I have a table that is something like this:

Products
--
idProduct as int,
idPart1 as int,
idPart2 as int,
idPart3 as int

then other table, which is something like:

Parts:
-
idPart as int,
partName as VarChar(30)

Now, some products only take one part, but others will take as many as 3 parts. Since it's a relatively small number I thought it would be better to put 3 different idParts on the product table and have two of them allow nulls, instead of creating a Master / Detail kind of thing. My question is, how do I create a view with something like:

v_Products
--
idProduct as int,
namePart1 as VarChar(30),
namePart2 as VarChar(30),
namePart3 as VarChar(30)

I'm using SQL Server 2005 (Express Edition, but the SQL is the same so...). I'm using this code:

SELECT dbo.products.idProduct, dbo.parts1.partName, dbo.parts2.partName, dbo.parts3.partName
FROM dbo.products INNER JOIN dbo.parts AS parts1 ON dbo.products.idPart1=parts1.idPart INNER JOIN(...)

Help? :s

Hi,

I would suggest you not to use the inline stored child data. Store the information about the parts in a parent-child relation rather than in the table itself. Querying will be much easier for you in the future.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Storing data in this format is just asking for trouble in the future. Invariably, you will end up in a situation where you have a product with 4 parts, at which point you need to add extra columns to the table.

However, if you've got this structure already and there's nothing you can do about it, then you need to create your view as follows (this also demonstrates the problems with using this style of database schema)

SELECT p.idProduct, pt1.partName as namePart1, pt2.partName as namePart2, pt3.partName as namePart3

FROM dbo.products p

INNER JOIN dbo.parts pt1

ON p.idPart1 = pt1.idPart

LEFT JOIN dbo.parts pt2

ON p.idPart2 = pt2.idPart

LEFT JOIN dbo.parts pt3

ON p.idPart3 = pt3.idPart

You need the LEFT JOINs to get the 2nd and 3rd parts because those columns may contain null values.

Other problems with this style of table design include the fact that if you are looking for products that use a specific part, you have to query across all three part columns, rather than just having a single part column that a master/detail style would give.

Good luck

Iain

problem with a view

We have a view called "vwtblBranchData" which is this: "SELECT *
FROM Branch_Master.dbo.tblBranchData"
We have a query that uses the view that has worked for over a year and now
has stopped working. The query is: select * from vwtblBranchData with
(readuncommitted) where (closed=0) and branch_num between 2000 and 2939 orde
r
by branch_num.
If I run just "select * from vwtblBranchData" or "select * from
vwtblBranchData where branch_num between 2000 and 2939 order by branch_num",
it works but when I add the "closed=0" part, it won't work. The closed colum
n
is a bit and is populated with only 0 or 1 in the table.
If I take the view out and run: "select * from branch_master..tblBranchData
with (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order by branch_num", it works.
Any idea what is happening?
Thanks,
Dan D.I put the query - "select * from vwtblBranchData where closed = 0 and
branch_num between 2000 and 2939 order by branch_num" in the query analyzer
and saw this: WHERE (([tblBranchData].[ActivePest]=0 AND
Convert([tblBranchData].[Branch_num])<=2939 AND
Convert([tblBranchData].[Branch_num])>=2000) ORDERED FORWARD
The "ActivePest" column isn't in the query or the view. Any idea where that
came from?
"Dan D." wrote:

> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939 or
der
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by branch_num
",
> it works but when I add the "closed=0" part, it won't work. The closed col
umn
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from branch_master..tblBranchDat
a
> with (readuncommitted) where (closed=0) and branch_num between 2000 and 29
39
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Could you please tell us the error you are getting? At this point we don't
even know what is happening on your side :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
> order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
> branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
> column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
> branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
> 2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Suspect a schema or metadata change. Try recreating the view. However, the
best approach is to specify the columns you want to select, rather than use
the wildcard.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Sorry. The query wasn't returning any rows when it should have. I solved the
problem. The query analyzer was trying to use the "active_pest". We didn't
need the column so I removed it and the query worked.
Thanks.
"Louis Davidson" wrote:

> Could you please tell us the error you are getting? At this point we don'
t
> even know what is happening on your side :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
>
>|||I solved the problem. The query analyzer was trying to use the "active_pest"
.
We didn't need the column so I removed it and the query worked.
Thanks.
"Scott Morris" wrote:

> Suspect a schema or metadata change. Try recreating the view. However, t
he
> best approach is to specify the columns you want to select, rather than us
e
> the wildcard.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> order
> branch_num",
> column
> branch_master..tblBranchData
> 2939
>
>sql

Problem when editing existing report

Hi,
I have created Report1.rdl, using a simple select such as
SELECT * FROM Table1 .
Now I want to filter the result, so I go to the Data view, and add a filter
into the GroupCode field.
But when I run again the report, it doesn´t show any data, only the header.
Isn´t it possible to edit a report once you have created it? I suppose it is
possible so, what am I doing wrong?
Thanks in advance,
Ibai PeñaI have found why it happens. I compare the filter field with a blank value,
and no record is found.
Now my question is: Is it possible to create a filter, and make be able to
use it or not, depending on what the user wants?
I have succed filtering data, but once filtered, I´m not able to see all
data again.
Thanks in advance,
Ibai Peña
"Ibai Peña" wrote:
> Hi,
> I have created Report1.rdl, using a simple select such as
> SELECT * FROM Table1 .
> Now I want to filter the result, so I go to the Data view, and add a filter
> into the GroupCode field.
> But when I run again the report, it doesn´t show any data, only the header.
> Isn´t it possible to edit a report once you have created it? I suppose it is
> possible so, what am I doing wrong?
> Thanks in advance,
> Ibai Peña|||My guess is that the filter applied does not return any data and so it is
blank.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ibai Peña" <IbaiPea@.discussions.microsoft.com> wrote in message
news:A71B86D7-560C-4C3F-90C2-11D0947EFBF5@.microsoft.com...
> Hi,
> I have created Report1.rdl, using a simple select such as
> SELECT * FROM Table1 .
> Now I want to filter the result, so I go to the Data view, and add a
filter
> into the GroupCode field.
> But when I run again the report, it doesn´t show any data, only the
header.
> Isn´t it possible to edit a report once you have created it? I suppose it
is
> possible so, what am I doing wrong?
> Thanks in advance,
> Ibai Peña

Problem when changing Views

I have 3 views, two of which depend on the other:

CREATE VIEW dbo.CustomerListQueryAccounts
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 0)

CREATE VIEW dbo.CustomerListQueryProspects
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 1)

which depend on:
CREATE VIEW dbo.CustomerListQuery
AS
SELECT TOP 100 PERCENT
dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber,

dbo.NumberOfJobsPerCustomer.CountOfJobID,
dbo.NumberOfQuotesPerCustomer.CountOfQuoteID,

dbo.NumberOfComplaintsPerCustomer.CountOfComplaint ID,
dbo.NumberOfNotesPerCustomer.CountOfCustomerNoteID ,

dbo.NumberOfEnquiriesPerCustomer.CountOfEnquiryID
FROM dbo.Customers

LEFT OUTER JOIN
dbo.NumberOfJobsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfJobsPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfQuotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfQuotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfNotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfNotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfComplaintsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfComplaintsPerCustomer.CustomerID

LEFT OUTER JOIN
dbo.NumberOfEnquiriesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfEnquiriesPerCustomer.CustomerID

ORDER BY dbo.Customers.AccountName

These work well but I have an alternative version of this latter one
which has fewer columns. At present it is:
CREATE VIEW dbo.CustomerListQueryShorter
AS
SELECT dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber
FROM dbo.Customers

I now wish to make this one CustomerListQuery and so rename the
existing one CustomerListQueryOriginal (just to get is out of the way)
and change its first line, using the View's properties (in Enterprise
Manager) to :
CREATE VIEW dbo.CustomerListQueryOriginal

I then rename CustomerListQueryShorter to CustomerListQuery and change
its first line to
CREATE VIEW dbo.CustomerListQuery

Now when I 'Return all rows' of CustomerListQueryAccounts I get an
error message:
'dbo.CustomerListQueryAccounts' has more column names specified than
columns defined.

If however, I go into the design and then select Run, I get the correct
output reflecting the new version of CustomerListQuery. How do I get
the 'Return all rows' output to show the same.

It appears that the orginal version is still being used. How do I get
the system to replace this ?Jim Devenish (internet.shopping@.foobox.com) writes:

Quote:

Originally Posted by

I have 3 views, two of which depend on the other:
CREATE VIEW dbo.CustomerListQuery
AS
SELECT TOP 100 PERCENT
>...
ORDER BY dbo.Customers.AccountName

Remove this TOP 100 PERCENT and ORDER BY nonsense. It serves no
purpose. Yes, you may feel that when you do a SELECT on the view
that you get back the rows in the same order as the ORDER BY clause,
but that is due to mere chance. Many people who had this sort of
views found that they no longer the result they expected when they
moved to SQL 2005.

There is only one way to get an ordered result from a query, and that
is to add ORDER BY to the query itself.

Quote:

Originally Posted by

I then rename CustomerListQueryShorter to CustomerListQuery and change
its first line to
CREATE VIEW dbo.CustomerListQuery
>
Now when I 'Return all rows' of CustomerListQueryAccounts I get an
error message:
'dbo.CustomerListQueryAccounts' has more column names specified than
columns defined.
>
If however, I go into the design and then select Run, I get the correct
output reflecting the new version of CustomerListQuery. How do I get
the 'Return all rows' output to show the same.

You should stop using SELECT *. SELECT * is great for ad-hoc queries,
but it does not belong in production code. One reason for that is what
you experienced. Had you listed the columns explicitly, you would still
have gotten an error, but at least the error would have been apparent.

If you insist on using SELECT *, you need to learn to use sp_refreshview,
because you will need it a lot.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Remove this TOP 100 PERCENT and ORDER BY nonsense. It serves no
purpose. Yes, you may feel that when you do a SELECT on the view
that you get back the rows in the same order as the ORDER BY clause,
but that is due to mere chance. Many people who had this sort of
views found that they no longer the result they expected when they
moved to SQL 2005.
>
There is only one way to get an ordered result from a query, and that
is to add ORDER BY to the query itself.
>

Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQuery had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.

Are now suggesting that I remove Order By from the View and then make
the RecordSource into:
Select * From CustomerListQuery Order By AccountName

I have just looked at BOL for 'Order By' and find that it says:
"The Order By clause is invalid in Views ... unless TOP is also
specified"
This implies that it can be used in this way but it does not say that
it fails to carry out the ordering.

Similarly when I look up the syntax of Create View I find, within the
select_statement that:
"A Create View statement cannot include Order By clause, unless there
is also a TOP clause in the select list of the Select statement"

It does not say that this is nonsense.|||Erland Sommarskog wrote:

Quote:

Originally Posted by

You should stop using SELECT *. SELECT * is great for ad-hoc queries,
but it does not belong in production code. One reason for that is what
you experienced. Had you listed the columns explicitly, you would still
have gotten an error, but at least the error would have been apparent.
>
If you insist on using SELECT *, you need to learn to use sp_refreshview,
because you will need it a lot.
>

Thank you for your warning about the use of Select * . I expect that
there has been extensive discussion elsewhere as to why 'it does not
belong in production code' but I was unaware of it. Perhaps you can
point me in the right direction.

However you say that I would have still got an error had I listed the
columns explicitly. So I return to my orginal question: why does the
new view produce the expected output from the Design View but not from
'Return all rows'?

How and where do I use sp_refreshview?|||Hey Jim,

There are a number of issues with your approach, but the primary
reason that you're having the problems you are is that you're using
Enterprise Manager (known by many as Enterprise MANGLER) to edit the
script of your views. EM is a good tool for administration, but you
should be using Query Analzer for editing.

In Query Analyzer, you can right-click on the view and select EDIT, and
it will show you the script of the view, which you can then ALTER to
get to the correct format you need.

As far as the other issues, I think Erland tapped into them:

1. Don't use SELECT * in production code (or at least don't do so
wihout commenting). There was a recent discussion in another group
about it that you may want to read.
http://groups.google.com/group/micr...2efe874da82b7ef
2. The TOP...ORDER BY embedded in a view is, at best, flaky. It's
also one of the behaviors that was changed in SQL 2005, so if you can
avoid using it, I'd recommend that you do so. The only way to ensure
an order to your results is to use ORDER BY in your final outer SELECT
statement (e.g., SELECT columnlist FROM dbo.CustomerListQueryAccounts
ORDER BY AccountName).

HTH,
Stu

Jim Devenish wrote:

Quote:

Originally Posted by

I have 3 views, two of which depend on the other:
>
CREATE VIEW dbo.CustomerListQueryAccounts
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 0)
>
CREATE VIEW dbo.CustomerListQueryProspects
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 1)
>
which depend on:
CREATE VIEW dbo.CustomerListQuery
AS
SELECT TOP 100 PERCENT
dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber,
>
dbo.NumberOfJobsPerCustomer.CountOfJobID,
dbo.NumberOfQuotesPerCustomer.CountOfQuoteID,
>
dbo.NumberOfComplaintsPerCustomer.CountOfComplaint ID,
dbo.NumberOfNotesPerCustomer.CountOfCustomerNoteID ,
>
dbo.NumberOfEnquiriesPerCustomer.CountOfEnquiryID
FROM dbo.Customers
>
LEFT OUTER JOIN
dbo.NumberOfJobsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfJobsPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfQuotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfQuotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfNotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfNotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfComplaintsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfComplaintsPerCustomer.CustomerID
>
LEFT OUTER JOIN
dbo.NumberOfEnquiriesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfEnquiriesPerCustomer.CustomerID
>
ORDER BY dbo.Customers.AccountName
>
These work well but I have an alternative version of this latter one
which has fewer columns. At present it is:
CREATE VIEW dbo.CustomerListQueryShorter
AS
SELECT dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber
FROM dbo.Customers
>
I now wish to make this one CustomerListQuery and so rename the
existing one CustomerListQueryOriginal (just to get is out of the way)
and change its first line, using the View's properties (in Enterprise
Manager) to :
CREATE VIEW dbo.CustomerListQueryOriginal
>
I then rename CustomerListQueryShorter to CustomerListQuery and change
its first line to
CREATE VIEW dbo.CustomerListQuery
>
Now when I 'Return all rows' of CustomerListQueryAccounts I get an
error message:
'dbo.CustomerListQueryAccounts' has more column names specified than
columns defined.
>
If however, I go into the design and then select Run, I get the correct
output reflecting the new version of CustomerListQuery. How do I get
the 'Return all rows' output to show the same.
>
It appears that the orginal version is still being used. How do I get
the system to replace this ?

|||Jim Devenish (internet.shopping@.foobox.com) writes:

Quote:

Originally Posted by

Thank you for your warning about the use of Select * . I expect that
there has been extensive discussion elsewhere as to why 'it does not
belong in production code' but I was unaware of it. Perhaps you can
point me in the right direction.

There aree several reasons. One is tracability. Is the column xyz in use
somewhere? It's possible to find via sysdepends it is (although sysdepends
for various reasons isn't always reliable), but then you find that it
is a SELECT *, you cannot tell whether it is use at all. That is,
queries should list columns that are actually used. In any serious system
there are columns that are one point phased out - or could be phased out,
if you could verify that they are no longer in use.

If you add or drop columns, the SELECT * changes, but depending on context
not immediately, so there can be sources of confusion.

Quote:

Originally Posted by

However you say that I would have still got an error had I listed the
columns explicitly. So I return to my orginal question: why does the
new view produce the expected output from the Design View but not from
'Return all rows'?

I use neither of the tools, but I guess that Design View resubmits the
view definition something Return all Rows have no reason to do. Using
Profiler would reveal what is going on.

Quote:

Originally Posted by

How and where do I use sp_refreshview?

In Query Analyzer where you run other queries. As for how, well, did
you try Books Online?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jim Devenish (internet.shopping@.foobox.com) writes:

Quote:

Originally Posted by

Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQuery had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.
>
Are now suggesting that I remove Order By from the View and then make
the RecordSource into:
Select * From CustomerListQuery Order By AccountName

Yes.

Quote:

Originally Posted by

I have just looked at BOL for 'Order By' and find that it says:
"The Order By clause is invalid in Views ... unless TOP is also
specified"
This implies that it can be used in this way but it does not say that
it fails to carry out the ordering.

TOP 10 PERCENT ORDER BY is meaningful, because it picks the the top 10
percent according to the ORDER BY clause.

TOP 100 PERCENT ORDER BY is meaningless, because 100 percent is
everything, so it does not matter what you order by. You still get
everything.

That is, the purpose of ORDER BY in views in combination of TOP is to
determine which rows that are selected by the view.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem w/ Join in SQL View

I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX
Try:
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
dbo.GL00201.ACTINDX
AND (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX
Also, get rid of the TOP 100 PERCENT. It buys you nothing.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX
|||The immediate cause of your problem is the WHERE clause. The test
against the right table is eliminating the NULL values from the OUTER
join, reducing it to an inner join. The solution to that is to move
the test to the ON clause of the join.
FROM dbo.vwBUDGETACCTS
LEFT OUTER
JOIN dbo.GL00201
ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
AND dbo.GL00201.BUDGETID = '2007'
There are other issues, however. One is the use of TOP 100 PERCENT
and ORDER BY. Views are unordered, and SQL Server only allows use of
ORDER BY in a view when the TOP command is used. This has been
discussed endlessly in these forums, but the bottom line is that TOP
100 PERCENT should be removed from the view, along with the ORDER BY.
Ordering of the result set should be achieved by using an ORDER BY in
the query that references the view.
The second point that concerns me is the use of DISTINCT. There are
many times where DISTINCT is a valuable tool, but too often it is used
to fix a problem with the query or the database design - or even added
in a blind attempt to fix a problem and left in when it makes no
difference. There is no way to tell from the given information what
purpose it serves in this query, but it is worth reviewing why it is
there.
Roy Harvey
Beacon Falls, CT
On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
<Hutch@.discussions.microsoft.com> wrote:

>I am having problem w/ a Left Join when trying to create a SQL view. I am
>trying to get all the records from the left table plus the data from the
>right table where it has matching rows. Following is my SQL stmt I am using
>to create the view. I am getting data, but only data where the two tables
>are equal not getting all of the records from the Left table and then it
>returning null values where there is no associated data in the right table.
>Any help would be appreciated.
>SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
>dbo.GL00201.ACTINDX
>WHERE (dbo.GL00201.BUDGETID = '2007')
>ORDER BY dbo.vwBUDGETACCTS.ACTINDX
|||Thanks. That did the trick. I guess I will need to do some research on why
that would not work w/ the 'WHERE' stmt.
On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER BY
stmt?
"Tom Moreau" wrote:

> Try:
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
> dbo.GL00201.ACTINDX
> AND (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>
> Also, get rid of the TOP 100 PERCENT. It buys you nothing.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Hutch" <Hutch@.discussions.microsoft.com> wrote in message
> news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
> I am having problem w/ a Left Join when trying to create a SQL view. I am
> trying to get all the records from the left table plus the data from the
> right table where it has matching rows. Following is my SQL stmt I am using
> to create the view. I am getting data, but only data where the two tables
> are equal not getting all of the records from the Left table and then it
> returning null values where there is no associated data in the right table.
> Any help would be appreciated.
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
> dbo.GL00201.ACTINDX
> WHERE (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>
|||Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
you do not mind answering one more question, what if I want to filter on more
than one field. Above I am putting in a criteria of '2007' on the BUDGETID
field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
'Office' would the syntax be:
AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
I assume not since I cannot get that syntax to work. Your help is
appreciated.
"Roy Harvey" wrote:

> The immediate cause of your problem is the WHERE clause. The test
> against the right table is eliminating the NULL values from the OUTER
> join, reducing it to an inner join. The solution to that is to move
> the test to the ON clause of the join.
> FROM dbo.vwBUDGETACCTS
> LEFT OUTER
> JOIN dbo.GL00201
> ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
> AND dbo.GL00201.BUDGETID = '2007'
> There are other issues, however. One is the use of TOP 100 PERCENT
> and ORDER BY. Views are unordered, and SQL Server only allows use of
> ORDER BY in a view when the TOP command is used. This has been
> discussed endlessly in these forums, but the bottom line is that TOP
> 100 PERCENT should be removed from the view, along with the ORDER BY.
> Ordering of the result set should be achieved by using an ORDER BY in
> the query that references the view.
> The second point that concerns me is the use of DISTINCT. There are
> many times where DISTINCT is a valuable tool, but too often it is used
> to fix a problem with the query or the database design - or even added
> in a blind attempt to fix a problem and left in when it makes no
> difference. There is no way to tell from the given information what
> purpose it serves in this query, but it is worth reviewing why it is
> there.
> Roy Harvey
> Beacon Falls, CT
> On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
> <Hutch@.discussions.microsoft.com> wrote:
>
|||"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:0C119075-A92E-4D07-B3FA-A3FD7E3CBB2E@.microsoft.com...
> Thanks. That did the trick. I guess I will need to do some research on
> why
> that would not work w/ the 'WHERE' stmt.
> On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER
> BY
> stmt?
Only if you're making the mistake of trying to do an ORDER BY in a VIEW.
Don't do that. It doesn't work in SQL 2005.
[vbcol=seagreen]
>
> "Tom Moreau" wrote:

Problem w/ Join in SQL View

I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDXTry:
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
dbo.GL00201.ACTINDX
AND (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX
Also, get rid of the TOP 100 PERCENT. It buys you nothing.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX|||The immediate cause of your problem is the WHERE clause. The test
against the right table is eliminating the NULL values from the OUTER
join, reducing it to an inner join. The solution to that is to move
the test to the ON clause of the join.
FROM dbo.vwBUDGETACCTS
LEFT OUTER
JOIN dbo.GL00201
ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
AND dbo.GL00201.BUDGETID = '2007'
There are other issues, however. One is the use of TOP 100 PERCENT
and ORDER BY. Views are unordered, and SQL Server only allows use of
ORDER BY in a view when the TOP command is used. This has been
discussed endlessly in these forums, but the bottom line is that TOP
100 PERCENT should be removed from the view, along with the ORDER BY.
Ordering of the result set should be achieved by using an ORDER BY in
the query that references the view.
The second point that concerns me is the use of DISTINCT. There are
many times where DISTINCT is a valuable tool, but too often it is used
to fix a problem with the query or the database design - or even added
in a blind attempt to fix a problem and left in when it makes no
difference. There is no way to tell from the given information what
purpose it serves in this query, but it is worth reviewing why it is
there.
Roy Harvey
Beacon Falls, CT
On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
<Hutch@.discussions.microsoft.com> wrote:

>I am having problem w/ a Left Join when trying to create a SQL view. I am
>trying to get all the records from the left table plus the data from the
>right table where it has matching rows. Following is my SQL stmt I am usin
g
>to create the view. I am getting data, but only data where the two tables
>are equal not getting all of the records from the Left table and then it
>returning null values where there is no associated data in the right table.
>Any help would be appreciated.
>SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
>dbo.GL00201.ACTINDX
>WHERE (dbo.GL00201.BUDGETID = '2007')
>ORDER BY dbo.vwBUDGETACCTS.ACTINDX|||Thanks. That did the trick. I guess I will need to do some research on why
that would not work w/ the 'WHERE' stmt.
On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER BY
stmt?
"Tom Moreau" wrote:

> Try:
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
> dbo.GL00201.ACTINDX
> AND (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>
> Also, get rid of the TOP 100 PERCENT. It buys you nothing.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Hutch" <Hutch@.discussions.microsoft.com> wrote in message
> news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
> I am having problem w/ a Left Join when trying to create a SQL view. I am
> trying to get all the records from the left table plus the data from the
> right table where it has matching rows. Following is my SQL stmt I am usi
ng
> to create the view. I am getting data, but only data where the two tables
> are equal not getting all of the records from the Left table and then it
> returning null values where there is no associated data in the right table
.
> Any help would be appreciated.
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =
> dbo.GL00201.ACTINDX
> WHERE (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>|||Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
you do not mind answering one more question, what if I want to filter on mor
e
than one field. Above I am putting in a criteria of '2007' on the BUDGETID
field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
'Office' would the syntax be:
AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
I assume not since I cannot get that syntax to work. Your help is
appreciated.
"Roy Harvey" wrote:

> The immediate cause of your problem is the WHERE clause. The test
> against the right table is eliminating the NULL values from the OUTER
> join, reducing it to an inner join. The solution to that is to move
> the test to the ON clause of the join.
> FROM dbo.vwBUDGETACCTS
> LEFT OUTER
> JOIN dbo.GL00201
> ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
> AND dbo.GL00201.BUDGETID = '2007'
> There are other issues, however. One is the use of TOP 100 PERCENT
> and ORDER BY. Views are unordered, and SQL Server only allows use of
> ORDER BY in a view when the TOP command is used. This has been
> discussed endlessly in these forums, but the bottom line is that TOP
> 100 PERCENT should be removed from the view, along with the ORDER BY.
> Ordering of the result set should be achieved by using an ORDER BY in
> the query that references the view.
> The second point that concerns me is the use of DISTINCT. There are
> many times where DISTINCT is a valuable tool, but too often it is used
> to fix a problem with the query or the database design - or even added
> in a blind attempt to fix a problem and left in when it makes no
> difference. There is no way to tell from the given information what
> purpose it serves in this query, but it is worth reviewing why it is
> there.
> Roy Harvey
> Beacon Falls, CT
> On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
> <Hutch@.discussions.microsoft.com> wrote:
>
>|||"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:0C119075-A92E-4D07-B3FA-A3FD7E3CBB2E@.microsoft.com...
> Thanks. That did the trick. I guess I will need to do some research on
> why
> that would not work w/ the 'WHERE' stmt.
> On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER
> BY
> stmt?
Only if you're making the mistake of trying to do an ORDER BY in a VIEW.
Don't do that. It doesn't work in SQL 2005.
[vbcol=seagreen]
>
> "Tom Moreau" wrote:
>|||On Sun, 31 Dec 2006 19:57:00 -0800, Hutch wrote:

>Thanks for the feedback. I am new to SQL stmts so this info is helpful. I
f
>you do not mind answering one more question, what if I want to filter on mo
re
>than one field. Above I am putting in a criteria of '2007' on the BUDGETI
D
>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the ter
m
>'Office' would the syntax be:
>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>I assume not since I cannot get that syntax to work. Your help is
>appreciated.
Hi Hutch,
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Hugo Kornelis, SQL Server MVP|||On Sun, 31 Dec 2006 19:57:00 -0800, Hutch
<Hutch@.discussions.microsoft.com> wrote:

>Thanks for the feedback. I am new to SQL stmts so this info is helpful. I
f
>you do not mind answering one more question, what if I want to filter on mo
re
>than one field. Above I am putting in a criteria of '2007' on the BUDGETI
D
>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the ter
m
>'Office' would the syntax be:
>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>I assume not since I cannot get that syntax to work. Your help is
>appreciated.
AND dbo.GL00201.BUDGETID = '2007'
WHERE dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Tests against the left table in a LEFT OUTER JOIN can appear in the
WHERE clause. Alternately, if you included that test in the ON
clause, it would require an AND, not a comma:
ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Roy Harvey
Beacon Falls, CT|||On Mon, 01 Jan 2007 13:18:38 +0100, Hugo Kornelis wrote:

>On Sun, 31 Dec 2006 19:57:00 -0800, Hutch wrote:
>
>Hi Hutch,
>AND dbo.GL00201.BUDGETID = '2007'
>AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Disregard this. Use Roy's suggestion instead:
AND dbo.GL00201.BUDGETID = '2007'
WHERE dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Hugo Kornelis, SQL Server MVP

Problem w/ Join in SQL View

I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDXTry:
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =dbo.GL00201.ACTINDX
AND (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX
Also, get rid of the TOP 100 PERCENT. It buys you nothing.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
I am having problem w/ a Left Join when trying to create a SQL view. I am
trying to get all the records from the left table plus the data from the
right table where it has matching rows. Following is my SQL stmt I am using
to create the view. I am getting data, but only data where the two tables
are equal not getting all of the records from the Left table and then it
returning null values where there is no associated data in the right table.
Any help would be appreciated.
SELECT DISTINCT
TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =dbo.GL00201.ACTINDX
WHERE (dbo.GL00201.BUDGETID = '2007')
ORDER BY dbo.vwBUDGETACCTS.ACTINDX|||The immediate cause of your problem is the WHERE clause. The test
against the right table is eliminating the NULL values from the OUTER
join, reducing it to an inner join. The solution to that is to move
the test to the ON clause of the join.
FROM dbo.vwBUDGETACCTS
LEFT OUTER
JOIN dbo.GL00201
ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
AND dbo.GL00201.BUDGETID = '2007'
There are other issues, however. One is the use of TOP 100 PERCENT
and ORDER BY. Views are unordered, and SQL Server only allows use of
ORDER BY in a view when the TOP command is used. This has been
discussed endlessly in these forums, but the bottom line is that TOP
100 PERCENT should be removed from the view, along with the ORDER BY.
Ordering of the result set should be achieved by using an ORDER BY in
the query that references the view.
The second point that concerns me is the use of DISTINCT. There are
many times where DISTINCT is a valuable tool, but too often it is used
to fix a problem with the query or the database design - or even added
in a blind attempt to fix a problem and left in when it makes no
difference. There is no way to tell from the given information what
purpose it serves in this query, but it is worth reviewing why it is
there.
Roy Harvey
Beacon Falls, CT
On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
<Hutch@.discussions.microsoft.com> wrote:
>I am having problem w/ a Left Join when trying to create a SQL view. I am
>trying to get all the records from the left table plus the data from the
>right table where it has matching rows. Following is my SQL stmt I am using
>to create the view. I am getting data, but only data where the two tables
>are equal not getting all of the records from the Left table and then it
>returning null values where there is no associated data in the right table.
>Any help would be appreciated.
>SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =>dbo.GL00201.ACTINDX
>WHERE (dbo.GL00201.BUDGETID = '2007')
>ORDER BY dbo.vwBUDGETACCTS.ACTINDX|||Thanks. That did the trick. I guess I will need to do some research on why
that would not work w/ the 'WHERE' stmt.
On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER BY
stmt?
"Tom Moreau" wrote:
> Try:
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX => dbo.GL00201.ACTINDX
> AND (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>
> Also, get rid of the TOP 100 PERCENT. It buys you nothing.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Hutch" <Hutch@.discussions.microsoft.com> wrote in message
> news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
> I am having problem w/ a Left Join when trying to create a SQL view. I am
> trying to get all the records from the left table plus the data from the
> right table where it has matching rows. Following is my SQL stmt I am using
> to create the view. I am getting data, but only data where the two tables
> are equal not getting all of the records from the Left table and then it
> returning null values where there is no associated data in the right table.
> Any help would be appreciated.
> SELECT DISTINCT
> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX => dbo.GL00201.ACTINDX
> WHERE (dbo.GL00201.BUDGETID = '2007')
> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>|||Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
you do not mind answering one more question, what if I want to filter on more
than one field. Above I am putting in a criteria of '2007' on the BUDGETID
field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
'Office' would the syntax be:
AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
I assume not since I cannot get that syntax to work. Your help is
appreciated.
"Roy Harvey" wrote:
> The immediate cause of your problem is the WHERE clause. The test
> against the right table is eliminating the NULL values from the OUTER
> join, reducing it to an inner join. The solution to that is to move
> the test to the ON clause of the join.
> FROM dbo.vwBUDGETACCTS
> LEFT OUTER
> JOIN dbo.GL00201
> ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
> AND dbo.GL00201.BUDGETID = '2007'
> There are other issues, however. One is the use of TOP 100 PERCENT
> and ORDER BY. Views are unordered, and SQL Server only allows use of
> ORDER BY in a view when the TOP command is used. This has been
> discussed endlessly in these forums, but the bottom line is that TOP
> 100 PERCENT should be removed from the view, along with the ORDER BY.
> Ordering of the result set should be achieved by using an ORDER BY in
> the query that references the view.
> The second point that concerns me is the use of DISTINCT. There are
> many times where DISTINCT is a valuable tool, but too often it is used
> to fix a problem with the query or the database design - or even added
> in a blind attempt to fix a problem and left in when it makes no
> difference. There is no way to tell from the given information what
> purpose it serves in this query, but it is worth reviewing why it is
> there.
> Roy Harvey
> Beacon Falls, CT
> On Sun, 31 Dec 2006 16:59:00 -0800, Hutch
> <Hutch@.discussions.microsoft.com> wrote:
> >I am having problem w/ a Left Join when trying to create a SQL view. I am
> >trying to get all the records from the left table plus the data from the
> >right table where it has matching rows. Following is my SQL stmt I am using
> >to create the view. I am getting data, but only data where the two tables
> >are equal not getting all of the records from the Left table and then it
> >returning null values where there is no associated data in the right table.
> >Any help would be appreciated.
> >
> >SELECT DISTINCT
> > TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
> >dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
> > dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
> >dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
> > dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
> >FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
> > dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX => >dbo.GL00201.ACTINDX
> >WHERE (dbo.GL00201.BUDGETID = '2007')
> >ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>|||"Hutch" <Hutch@.discussions.microsoft.com> wrote in message
news:0C119075-A92E-4D07-B3FA-A3FD7E3CBB2E@.microsoft.com...
> Thanks. That did the trick. I guess I will need to do some research on
> why
> that would not work w/ the 'WHERE' stmt.
> On the TOP 100 PERCENT comment, don't I have to use that to use the ORDER
> BY
> stmt?
Only if you're making the mistake of trying to do an ORDER BY in a VIEW.
Don't do that. It doesn't work in SQL 2005.
>
> "Tom Moreau" wrote:
>> Try:
>> SELECT DISTINCT
>> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
>> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
>> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
>> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =>> dbo.GL00201.ACTINDX
>> AND (dbo.GL00201.BUDGETID = '2007')
>> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>>
>> Also, get rid of the TOP 100 PERCENT. It buys you nothing.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> ..
>> "Hutch" <Hutch@.discussions.microsoft.com> wrote in message
>> news:C1547D5D-BE2E-4CE8-8973-18A6453FA298@.microsoft.com...
>> I am having problem w/ a Left Join when trying to create a SQL view. I
>> am
>> trying to get all the records from the left table plus the data from the
>> right table where it has matching rows. Following is my SQL stmt I am
>> using
>> to create the view. I am getting data, but only data where the two
>> tables
>> are equal not getting all of the records from the Left table and then it
>> returning null values where there is no associated data in the right
>> table.
>> Any help would be appreciated.
>> SELECT DISTINCT
>> TOP 100 PERCENT dbo.vwBUDGETACCTS.ACTINDX,
>> dbo.vwBUDGETACCTS.ACCOUNT, dbo.vwBUDGETACCTS.DESCRIPTION,
>> dbo.vwBUDGETACCTS.ADMCTR, dbo.vwBUDGETACCTS.ACTIVE,
>> dbo.vwBUDGETACCTS.PERIODID, dbo.vwBUDGETACCTS.PERNAME,
>> dbo.GL00201.BUDGETID, dbo.GL00201.BUDGETAMT
>> FROM dbo.vwBUDGETACCTS LEFT OUTER JOIN
>> dbo.GL00201 ON dbo.vwBUDGETACCTS.ACTINDX =>> dbo.GL00201.ACTINDX
>> WHERE (dbo.GL00201.BUDGETID = '2007')
>> ORDER BY dbo.vwBUDGETACCTS.ACTINDX
>>|||On Sun, 31 Dec 2006 19:57:00 -0800, Hutch wrote:
>Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
>you do not mind answering one more question, what if I want to filter on more
>than one field. Above I am putting in a criteria of '2007' on the BUDGETID
>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
>'Office' would the syntax be:
>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>I assume not since I cannot get that syntax to work. Your help is
>appreciated.
Hi Hutch,
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
--
Hugo Kornelis, SQL Server MVP|||On Sun, 31 Dec 2006 19:57:00 -0800, Hutch
<Hutch@.discussions.microsoft.com> wrote:
>Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
>you do not mind answering one more question, what if I want to filter on more
>than one field. Above I am putting in a criteria of '2007' on the BUDGETID
>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
>'Office' would the syntax be:
>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>I assume not since I cannot get that syntax to work. Your help is
>appreciated.
AND dbo.GL00201.BUDGETID = '2007'
WHERE dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Tests against the left table in a LEFT OUTER JOIN can appear in the
WHERE clause. Alternately, if you included that test in the ON
clause, it would require an AND, not a comma:
ON dbo.vwBUDGETACCTS.ACTINDX = dbo.GL00201.ACTINDX
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.GL00201.BUDGETID = '2007'
AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Roy Harvey
Beacon Falls, CT|||On Mon, 01 Jan 2007 13:18:38 +0100, Hugo Kornelis wrote:
>On Sun, 31 Dec 2006 19:57:00 -0800, Hutch wrote:
>>Thanks for the feedback. I am new to SQL stmts so this info is helpful. If
>>you do not mind answering one more question, what if I want to filter on more
>>than one field. Above I am putting in a criteria of '2007' on the BUDGETID
>>field. If I wanted to also filter on dbo.vwBUDGETACCTS.ADMCTR with the term
>>'Office' would the syntax be:
>>AND dbo.GL00201.BUDGETID = '2007', dbo.vwBUDGETACCTS.ADMCTR = 'Office'?
>>I assume not since I cannot get that syntax to work. Your help is
>>appreciated.
>Hi Hutch,
>AND dbo.GL00201.BUDGETID = '2007'
>AND dbo.vwBUDGETACCTS.ADMCTR = 'Office'
Disregard this. Use Roy's suggestion instead:
AND dbo.GL00201.BUDGETID = '2007'
WHERE dbo.vwBUDGETACCTS.ADMCTR = 'Office'
--
Hugo Kornelis, SQL Server MVP

Problem viewing Reports from Network

Hi,
We have a network of XP Machines.
When i try to view the reports in the application from the same
machine itself it's coming up properly,but when i try to acess the
reports in the application from another machine it's giving me the
following error,
?The permissions granted to user ?OLDSERVER\Guest' are insufficient
for performing this operation. (rsAccessDenied)
As a solution to the problem of permissions in the help it's mentioned
that we need to create roles in the SQL Server Report Manager for the
users whom we need to give access to the reports.When we are accessing
the reports from the network by default we are accessing the server as
guests who doesn't have much rights.So I have created role (Browser )
on SQL Server Report Manager for the guest user and tried accessing
the server.
Then it's giving the following error
The Error Log description for the error was as follows,
aspnet_wp!library!cd4!10/13/2004-21:48:41:: e
ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server.
See the error log for more details. -->
System.InvalidOperationException: Computer name could not be obtained.
at System.Environment.get_MachineName()
at Microsoft.ReportingServices.Diagnostics.RunningJobContext..ctor(String
jobId, String requestPath,
JobActionEnum action, JobTypeEnum type, RunningJobList parentList,
String userName, HttpContext
optionalWebCtx)
at Microsoft.ReportingServices.Diagnostics.RunningJobList.AddJob(String
jobId, String requestPath,
JobActionEnum action, JobTypeEnum type, String userName, HttpContext
optionalCtx)
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase..ctor(String
jobId, String requestPath,
JobActionEnum action, JobTypeEnum jobType, String userName)
at Microsoft.ReportingServices.Library.ProcessReportParametersCancelableStep..ctor(RSService
rs,
CatalogItemContext reportContext, Guid reportID, Guid linkID,
DateTime historyDate, Int32 executionOption,
ReportSnapshot compiledDefinition, ReportSnapshot snapshotData,
String savedParametersXml,
NameValueCollection values, JobTypeEnum jobType, String userName)
at Microsoft.ReportingServices.Library.RSService.GetReportParametersForRendering(CatalogItemContext
reportContext, Guid reportID, Guid linkID, DateTime historyDate,
Int32 executionOption, ReportSnapshot
compiledDefinition, ReportSnapshot snapshotData, String
savedParametersXml, NameValueCollection values,
JobTypeEnum jobType)
at Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
report, String historyID, Boolean
forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
at Microsoft.ReportingServices.Library.RSService.GetReportParameters(String
report, String historyID, Boolean
forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
-- End of inner exception stack trace --
aspnet_wp!webserver!cd4!10/13/2004-21:48:41:: e ERROR:
Reporting Services error
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log
for more details. -->
System.InvalidOperationException: Computer name could not be obtained.
Thanks in Advance,
RajeshTake a look at
http://msdn.microsoft.com/library/en-us/rswork/htm/rms_security_v1_10bm.asp
. Try using built-in accounts Everyone or Users instead of Guest.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Rajesh" <rajeshkchandran@.gmail.com> wrote in message
news:cad1e70a.0410201456.280c018f@.posting.google.com...
> Hi,
> We have a network of XP Machines.
> When i try to view the reports in the application from the same
> machine itself it's coming up properly,but when i try to acess the
> reports in the application from another machine it's giving me the
> following error,
> 'The permissions granted to user 'OLDSERVER\Guest' are insufficient
> for performing this operation. (rsAccessDenied)
> As a solution to the problem of permissions in the help it's mentioned
> that we need to create roles in the SQL Server Report Manager for the
> users whom we need to give access to the reports.When we are accessing
> the reports from the network by default we are accessing the server as
> guests who doesn't have much rights.So I have created role (Browser )
> on SQL Server Report Manager for the guest user and tried accessing
> the server.
> Then it's giving the following error
> The Error Log description for the error was as follows,
> aspnet_wp!library!cd4!10/13/2004-21:48:41:: e
> ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details., ;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server.
> See the error log for more details. -->
> System.InvalidOperationException: Computer name could not be obtained.
> at System.Environment.get_MachineName()
> at
> Microsoft.ReportingServices.Diagnostics.RunningJobContext..ctor(String
> jobId, String requestPath,
> JobActionEnum action, JobTypeEnum type, RunningJobList parentList,
> String userName, HttpContext
> optionalWebCtx)
> at Microsoft.ReportingServices.Diagnostics.RunningJobList.AddJob(String
> jobId, String requestPath,
> JobActionEnum action, JobTypeEnum type, String userName, HttpContext
> optionalCtx)
> at
> Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase..ctor(String
> jobId, String requestPath,
> JobActionEnum action, JobTypeEnum jobType, String userName)
> at
> Microsoft.ReportingServices.Library.ProcessReportParametersCancelableStep..ctor(RSService
> rs,
> CatalogItemContext reportContext, Guid reportID, Guid linkID,
> DateTime historyDate, Int32 executionOption,
> ReportSnapshot compiledDefinition, ReportSnapshot snapshotData,
> String savedParametersXml,
> NameValueCollection values, JobTypeEnum jobType, String userName)
> at
> Microsoft.ReportingServices.Library.RSService.GetReportParametersForRendering(CatalogItemContext
> reportContext, Guid reportID, Guid linkID, DateTime historyDate,
> Int32 executionOption, ReportSnapshot
> compiledDefinition, ReportSnapshot snapshotData, String
> savedParametersXml, NameValueCollection values,
> JobTypeEnum jobType)
> at
> Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
> report, String historyID, Boolean
> forRendering, NameValueCollection values,
> DatasourceCredentialsCollection credentials)
> at
> Microsoft.ReportingServices.Library.RSService.GetReportParameters(String
> report, String historyID, Boolean
> forRendering, NameValueCollection values,
> DatasourceCredentialsCollection credentials)
> -- End of inner exception stack trace --
> aspnet_wp!webserver!cd4!10/13/2004-21:48:41:: e ERROR:
> Reporting Services error
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log
> for more details. -->
> System.InvalidOperationException: Computer name could not be obtained.
>
> Thanks in Advance,
> Rajesh

Problem using CAST with Zeroed out Field Values

Hello --
I have a field with date values like: 20050714. When using SQL Server
to convert it to a Date in a View, I use the following:
CAST(MyDate as DateTime)

That works great. The problem is that some rows have the value:
00000000

When it hits one of these, I get a SQL Server Enterprise Manager Error:

Database Server: Microsoft SQL Server
Version: 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Is there a workaround for this? Any assistance greatly appreciated.

RBollinger1) Quit storing dates as strings and numbers. This is the REAL answer.
And kill the guy who did this so he will not screw up the rest of the
database.

2) You did not give us specs; did you want the non-existent date
0000-00-00 to be a NULL or what? The error is exactly right ad shows
that your design has some serious problems. .

CAST (CASE @.my_string_date
WHEN '00000000'
THEN NULL -- or dummy value
ELSE @.my_string_date END AS DATETIME)

A better would be to do an update on the column, get rid of the
mythical dates and then replace that column with a temporal one after
validating the existing dates with a procedure. Don't just mop the
floor; fix the leak!|||robboll (robboll@.hotmail.com) writes:
> I have a field with date values like: 20050714. When using SQL Server
> to convert it to a Date in a View, I use the following:
> CAST(MyDate as DateTime)
> That works great. The problem is that some rows have the value:
> 00000000
> When it hits one of these, I get a SQL Server Enterprise Manager Error:
> Database Server: Microsoft SQL Server
> Version: 08.00.0760
> Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> Is there a workaround for this? Any assistance greatly appreciated.

You can use the isdate() function to check whether a string is a
legal date value or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Combining your answer with the second guys answer, I think I will try
something like:

CAST(CASE@.my_string_date
WHEN isdate(my_string_date) = True
THEN NULL
ELSE @.my_string_date END AS DATETIME)

Thanks for your help!
RBollinger|||"isdate(my_string_date) = TRUE " is wrong; there is no Boolean datatype
or constants in SQL. You need a numeric value Did you fix the leak?|||--CELKO-- wrote:
> "isdate(my_string_date) = TRUE " is wrong; there is no Boolean
datatype
> or constants in SQL. You need a numeric value Did you fix the leak?

Yes I did "fix the leak". I ended up removing all invalid date values
as part of the append process -- the recommended solution.

Thanks.

problem updating view with instead of trigger

Hi all,
I have created wv_details view that has an instead of update trigger. My
question is how can I update this view?
When I run the following query I get an error - "View 'wv_details' has
an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
statement."
UPDATE wv_details
SET REFERENCE=latest.REFERENCE,
[NOTES]=latest.NOTES,
[TITLE]=latest.TITLE,
[LINKACCT]=latest.LINKACCT,
[COUNTRY]=latest.COUNTRY,
[ZIP]=latest.ZIP,
[EXT]=latest.EXT,
[STATE]=latest.STATE,
[ADDRESS1]=latest.ADDRESS1,
[ADDRESS2]=latest.ADDRESS2,
[MERGECODES]=latest.MERGECODES,
[STATUS]=latest.STATUS,
[LASTUSER]=latest.LASTUSER
FROM wv_details latest
JOIN wv_details
ON latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
So I changed this query as shown below. It does not use a FROM clause
anymore. But I get a different error with this query - "The text, ntext, and
image data types are invalid in this subquery or aggregate expression.". The
NOTES field is a text column.
UPDATE wv_details
SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[NOTES]=(SELECT TOP 1 latest.NOTES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[TITLE]=(SELECT TOP 1 latest.TITLE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LINKACCT]=(SELECT TOP 1 latest.LINKACCT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[COUNTRY]=(SELECT TOP 1 latest.COUNTRY
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ZIP]=(SELECT TOP 1 latest.ZIP
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[EXT]=(SELECT TOP 1 latest.EXT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATE]=(SELECT TOP 1 latest.STATE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[MERGECODES]=(SELECT TOP 1 latest.MERGECODES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATUS]=(SELECT TOP 1 latest.STATUS
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LASTUSER]=(SELECT TOP 1 latest.LASTUSER
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
)
So my question is how can I update this view?
Thanks...
-NikhilThere are quite a few issues:
1. You reference the view as if it's either an "inserted" or "deleted"
virtual table. It's not so.
2. You have an Instead Of trigger on the view, you should be updating the
base table within that trigger. You have full access the virtual tables
there.
3. You cannot do (update obj set col =(select top 1 lob_col from ...)).
You're are doing aggregation on the blob which is not allowed (by MS
design).
4. Even if (col=select top 1 lob) is allowed, this update is going to cost
you royally.
You have been posting for a while here. You know it would be easier if you
post ddl+sample data/code+expected output, it would be easier to help you.
http://groups.google.co.uk/groups?h...er+Nikhil+Patel
http://www.aspfaq.com/etiquette.asp?id=5006
-oj
"Nikhil Patel" <donotspam@.nospaml.com> wrote in message
news:eI6SpeYBFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created wv_details view that has an instead of update trigger.
> My question is how can I update this view?
> When I run the following query I get an error - "View 'wv_details' has
> an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
> statement."
> UPDATE wv_details
> SET REFERENCE=latest.REFERENCE,
> [NOTES]=latest.NOTES,
> [TITLE]=latest.TITLE,
> [LINKACCT]=latest.LINKACCT,
> [COUNTRY]=latest.COUNTRY,
> [ZIP]=latest.ZIP,
> [EXT]=latest.EXT,
> [STATE]=latest.STATE,
> [ADDRESS1]=latest.ADDRESS1,
> [ADDRESS2]=latest.ADDRESS2,
> [MERGECODES]=latest.MERGECODES,
> [STATUS]=latest.STATUS,
> [LASTUSER]=latest.LASTUSER
> FROM wv_details latest
> JOIN wv_details
> ON latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> So I changed this query as shown below. It does not use a FROM clause
> anymore. But I get a different error with this query - "The text, ntext,
> and image data types are invalid in this subquery or aggregate
> expression.". The NOTES field is a text column.
> UPDATE wv_details
> SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [NOTES]=(SELECT TOP 1 latest.NOTES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [TITLE]=(SELECT TOP 1 latest.TITLE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LINKACCT]=(SELECT TOP 1 latest.LINKACCT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [COUNTRY]=(SELECT TOP 1 latest.COUNTRY
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ZIP]=(SELECT TOP 1 latest.ZIP
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [EXT]=(SELECT TOP 1 latest.EXT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATE]=(SELECT TOP 1 latest.STATE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [MERGECODES]=(SELECT TOP 1 latest.MERGECODES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATUS]=(SELECT TOP 1 latest.STATUS
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LASTUSER]=(SELECT TOP 1 latest.LASTUSER
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> )
>
> So my question is how can I update this view?
> Thanks...
> -Nikhil
>