cursor

What' s wrong with this code?
Declare @.UDName varchar(150)
Declare @.UName varchar(50)
Declare @.Domain varchar(50)
Declare @.EMail varchar(20)
Declare @.GName varchar(50)
Declare @.Description varchar(150)
Declare @.FName varchar(50)
Declare @.ShortName varchar(150)
Declare UserCursor Cursor
For
SELECT ( UsrUsers.[Domain] + '' + UsrUsers.UserName) AS
UDName, UsrUsers.UserName as UName, UsrUsers.[Domain],UsrUsers.EMail,
UsrGroups.Name AS GName, UsrGroups.Description,
UsrFunctions.Name AS FName, UsrFunctions.ShortName AS
ShortName
FROM UsrUsers INNER JOIN
UsrUsersGroups ON UsrUsers.UserID =
UsrUsersGroups.UserID INNER JOIN
UsrFunctions INNER JOIN
UsrFunctionsGroups ON UsrFunctions.FunctionID =
UsrFunctionsGroups.FunctionID INNER JOIN
UsrGroups ON UsrFunctionsGroups.GroupID =
UsrGroups.GroupID ON UsrUsersGroups.GroupID = UsrGroups.GroupID
ORDER BY UName,GName,FName
open UserCursor
fetch next from UserCursor
into
@.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.ShortName
while @.@.fetch_status = 0
begin
select
@.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.ShortName
end
close UserCursor
deallocate UserCursor
I run it in sql query analizer and it goes on and on until 'Not Responding'
HrckoTechnically, you need a FETCH inside the loop, else you are working on the s
ame row endlessly. But
the cursor seems totally meaningless to me. Why not just have the SELETE sta
tement as is? What is
the purpose of using a cursor here? Why do you want to return one table for
each row, each table
containing one row?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:djl4p1$omg$1@.ss405.t-com.hr..
.
> What' s wrong with this code?
> Declare @.UDName varchar(150)
> Declare @.UName varchar(50)
> Declare @.Domain varchar(50)
> Declare @.EMail varchar(20)
> Declare @.GName varchar(50)
> Declare @.Description varchar(150)
> Declare @.FName varchar(50)
> Declare @.ShortName varchar(150)
> Declare UserCursor Cursor
> For
> SELECT ( UsrUsers.[Domain] + '' + UsrUsers.UserName) AS UDName, UsrU
sers.UserName as
> UName, UsrUsers.[Domain],UsrUsers.EMail, UsrGroups.Name AS GName, UsrGroups.Descri
ption,
> UsrFunctions.Name AS FName, UsrFunctions.ShortName AS
ShortName
> FROM UsrUsers INNER JOIN
> UsrUsersGroups ON UsrUsers.UserID = UsrUsersGroups.Us
erID INNER JOIN
> UsrFunctions INNER JOIN
> UsrFunctionsGroups ON UsrFunctions.FunctionID = UsrFu
nctionsGroups.FunctionID
> INNER JOIN
> UsrGroups ON UsrFunctionsGroups.GroupID = UsrGroups.G
roupID ON
> UsrUsersGroups.GroupID = UsrGroups.GroupID
> ORDER BY UName,GName,FName
> open UserCursor
> fetch next from UserCursor
> into
> @.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.ShortName
> while @.@.fetch_status = 0
> begin
> select @.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.Shor
tName
> end
> close UserCursor
> deallocate UserCursor
> I run it in sql query analizer and it goes on and on until 'Not Responding
'
> Hrcko
>|||I have to read all the users from the table first and then functions.
I need it for report.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uLX0HiV2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Technically, you need a FETCH inside the loop, else you are working on the
> same row endlessly. But the cursor seems totally meaningless to me. Why
> not just have the SELETE statement as is? What is the purpose of using a
> cursor here? Why do you want to return one table for each row, each table
> containing one row?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:djl4p1$omg$1@.ss405.t-com.hr...
>|||>I have to read all the users from the table first and then functions.
I'm afraid that I don't understand what you mean by "and then function"...
Just be aware that using a cursor is often *much* slower, and often you have
to write more code
which is harder to maintain, compared to a single SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:djl5p1$qnf$1@.ss405.t-com.hr..
.
>I have to read all the users from the table first and then functions.
> I need it for report.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uLX0HiV2FHA.1184@.TK2MSFTNGP12.phx.gbl...
>|||I have to tables: Users and Functions.
In my report(crystal report 11) i have to read all data from table Users and
then all data from table Functions, because in Detail section it reads
record by record - so I get a line with user name and then a line with
function name. I don't want that.
I want all users and then all functions.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcMatwV2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> I'm afraid that I don't understand what you mean by "and then
> function"...
> Just be aware that using a cursor is often *much* slower, and often you
> have to write more code which is harder to maintain, compared to a single
> SELECT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:djl5p1$qnf$1@.ss405.t-com.hr...
>|||As Tibor indicated, you'll probably be better off not using a cursor.
To help you with that, please post DDL, sample data and show your
required results. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Those are two tables, which translates to two resultsets, which in turn give
s
you a typical master/detail dataset to work with. What's wrong with that?
ML

Cube Design

Can two or more sets of 'non- related' tables be combine and form a cube? Currently I have only a query from the business domain and a cube was designed base on the query. If there are more unrelated queries coming up, should I add on to the same data source view or just add new data source view and new cube? Which is recommended?

Can I say one report model template (adhoc reporting) can only consist of one cube or possible to have many cubes?

Regards

Goh

Hello. By 'non-related tables' you mean that the result set of the queries do not share keys over their different source systems? Do the columns have the same type of information?

The standard solution for this is to build a data warehouse to create one version of the "truth" in the business. You do not load "information silos" or fragmented information into the same cube. In the data warehouse you can also keep history of changes in the source systems and be able trace what have happened.

If you, for some reason, cannot build a DW your best approach is to build separate cubes and separate dimensions for each query/source.

You can build a view and use TSQL UNION to build a general view of each source and create one cube on top of that. The problem with this approach is that in sales you must be sure that there are no internal transactions between each source. If so, your sales data will overestimate sales.

My recommendation is to build a DW and solve the problem with non-related tables there.

HTH

Thomas Ivarsson