Help on understanding generated MDX WHERE clause

When your reporting services datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

I think this is what Reed Jacobson is talking about here:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/11/06/25176.aspx

To find out more about how subcubes and the Where clause work, see:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/23/subselects_sp2.aspx

HTH,

Chris

sql

Help on Partitioning column was not found.

Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT*
FROMServer1..pTable1
UNION ALL
SELECT*
FROMServer2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]

CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <1015)
) ON [PRIMARY]

SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.You cannot have identity columns in an updatable partitioned view.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180643932.644398.247270@.g37g2000prf.googlegr oups.com...
Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT *
FROM Server1..pTable1
UNION ALL
SELECT *
FROM Server2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]

CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

...<other columns>.......

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <1015)
) ON [PRIMARY]

SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.|||On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

You cannot have identity columns in an updatable partitioned view.
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.|||Consider putting an INSTEAD OF trigger on the partitioned view.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180647134.671664.320360@.a26g2000pre.googlegr oups.com...
On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

You cannot have identity columns in an updatable partitioned view.
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Yes, <is not a permitted operator. You need to rewrite

CHECK ([ID2] <1015)

to

CHECK ([ID2] < 1015 OR [ID2] 1015)

Another story is whether this view will be very efficient. You should
probably add an index on ID2, or put it first in the primary key.

--
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|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.

--
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|||On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>
Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.|||Check out:

http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Sonny" <SonnyKMI@.gmail.comwrote in message
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>
Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.|||On Jun 1, 8:12 am, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

Check out:
>
http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
"Sonny" <Sonny...@.gmail.comwrote in message
>
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

>

Quote:

Originally Posted by

Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Books Online for SQL Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.
>
Again, thank you very much for your help.

Thank you so much!!sql

help on complex query

i need your help on a complex view
i have the following tables
* tblStats
categoryID
statDate
statUserIP
statVisits
statForms
*tblCategory
categoryID
category
i need the following output
categoryID category totalForms totalVisits statDate
10 Test 10 5
2004.11
10 Test 5 1
2004.12
10 Test 4 0
2005.1
20 Test 2 6 1
2004.11
20 Test 2 4 2
2004.12
20 Test 2 0 1
2005.1
important:
- distinct select on categoryID, statDate and UserIP
users which visited a category several times on the same date should
count only once for this date
- statDate should be shown in format year.month
can somebody help me on this view ?On Wed, 19 Jan 2005 22:52:47 +0100, Mike Schwarz wrote:
>i need your help on a complex view
(snip)
Hi Mike,
You're omitting several relevant details from your post. Please provide
the following:
* Table structure, posted as CREATE TABLE statements (including datatypes,
constraints, properties and indexes, but excluding irrelevant columns).
* Sample data, posted as INSERT statements
* Expected output (based on the sample data provided)
* And an explanation of the business problem you're trying to solve.
With these details, we can try to help you. Without them, we can only
guess.
See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with Instead of Update trigger on a View

I have an application where we are replacing a subsystem including portions
of the database. In order to minimize the code impact on the existing
application, we have decided to create a few "compatibility views" - i.e.
database Views that produce the same result and with the same names as the
old tables. Also in order to allow existing code to continue to function,
we are implementing INSTEAD OF triggers on the views. Even though all our
database accesses are encapsulated in stored procs, we have around 1500 of
them, and one of the tables we needed to reengineer this way is the "main"
table for the entire app. To make sure this isn't trivial, we have a new
"master" entity table with an Identity column that is referenced by the
reengineered "main" table.
At this point, we are only aware of performance impacts - everything appears
to work OK:
1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
statement) is part of an index, we loose the use of the index as a result of
having to use NULLIF() or COALESCE() on those columns in the view. For the
same reason, we can't index those view(s).
2. (This is where the question comes in:) The INSTEAD OF UPDATE trigger
appears to require a large number of separate UPDATE statements against the
base tables, or building a dynamic SQL statement. We are looking for
guidance...
Now to my question:
In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
table. If I understand correctly, since the triggering update statement may
only update one column, I cannot use an UPDATE statement against the base
table that updates all the columns with the values from the 'updated'
pseudo-table. Instead, I will have to check if each column is updated, and
if so, either update it separately or build a dynamic SQL UPDATE statement
including those columns that have been updated.
What is the recommended approach to this?
TIA,
Tore.On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
(snip)
>1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
>statement) is part of an index, we loose the use of the index as a result o
f
>having to use NULLIF() or COALESCE() on those columns in the view.
Hi Tore,
I don't think I understand what you're saying here. Where are you using
NULLIF() or COALESCE() and why? Coould you post a simplified sample of
your code?

> For the
>same reason, we can't index those view(s).
And neither should you. If you index the views, you'll create a complete
copy of your data. I don't think that yoou should do that in your
scenario.
(snip)
>In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
>table. If I understand correctly, since the triggering update statement ma
y
>only update one column, I cannot use an UPDATE statement against the base
>table that updates all the columns with the values from the 'updated'
>pseudo-table.
You undersatnd incorrectly. There is no 'updated' pseudo-table. The
'deleted' and 'inserted' pseudo-tables contain the complete before and
after image of the updated rows, including all columns that are not
affected by the update.
If your compatibility view translates to one new table, just perform the
modification in a single UPDATE statement. If your compatibility view
translates to more than one new table, use
IF UPDATE(col1) OR UPDATE(col2) ....
to find out which table(s) need updating, then use a single UPDATE
statement for all rows in each of the tables.
Sure, you'll be setting columns to the same value they already had. The
added cost of that is much less than the cost of finding out which
columns to update and executing up to 90 (!) consecutive UPDATE
statements against the same set of rows.

> Instead, I will have to check if each column is updated, and
>if so, either update it separately or build a dynamic SQL UPDATE statement
>including those columns that have been updated.
Using the dynamic SQL is even a worse option - it forces you to give
every user update permissions to the table. (And it will be slow because
of the extra recompiles).
www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP|||Thanks Hugo,
I'll be looking at this tomorrow.
Tore.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p75762lfnlmg789b20fv9jk1jfjrhs2it0@.
4ax.com...
> On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
> (snip)
of
> Hi Tore,
> I don't think I understand what you're saying here. Where are you using
> NULLIF() or COALESCE() and why? Coould you post a simplified sample of
> your code?
>
> And neither should you. If you index the views, you'll create a complete
> copy of your data. I don't think that yoou should do that in your
> scenario.
> (snip)
may
> You undersatnd incorrectly. There is no 'updated' pseudo-table. The
> 'deleted' and 'inserted' pseudo-tables contain the complete before and
> after image of the updated rows, including all columns that are not
> affected by the update.
> If your compatibility view translates to one new table, just perform the
> modification in a single UPDATE statement. If your compatibility view
> translates to more than one new table, use
> IF UPDATE(col1) OR UPDATE(col2) ....
> to find out which table(s) need updating, then use a single UPDATE
> statement for all rows in each of the tables.
> Sure, you'll be setting columns to the same value they already had. The
> added cost of that is much less than the cost of finding out which
> columns to update and executing up to 90 (!) consecutive UPDATE
> statements against the same set of rows.
>
statement
> Using the dynamic SQL is even a worse option - it forces you to give
> every user update permissions to the table. (And it will be slow because
> of the extra recompiles).
> www.sommarskog.se/dynamic_sql.html
> --
> Hugo Kornelis, SQL Server MVP

Help needed for creating view

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required......

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.

ThanksQuestion: If anything in an itemgroup does not say complete, then it's incomplete?

Sounds simple enough...|||Is that an anwer or a question?|||Well it was a question...but...how's about

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
GO

INSERT INTO myTable99(item, itemgroup, status)
SELECT 1, 1, 'complete' UNION ALL
SELECT 2, 1, 'Xyz' UNION ALL
SELECT 3, 2, 'complete' UNION ALL
SELECT 4, 2, 'complete' UNION ALL
SELECT 5, 2, 'complete'
GO

CREATE VIEW myView99
AS
SELECT DISTINCT l.itemgroup
, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
FROM myTable99 l
LEFT JOIN ( SELECT itemgroup, COUNT(*) AS Status_COUNT
FROM myTable99
WHERE status <> 'Complete'
GROUP BY itemgroup) AS r
ON l.itemgroup = r.itemgroup
GO

SELECT * FROM myView99
GO|||Oh, oh! Can I play too?SELECT DISTINCT a.itemgroup
, CASE
WHEN EXISTS (SELECT *
FROM myTable AS b
WHERE b.itemgroup = a.itemgroup
AND b.status <> 'complete') THEN 'incomplete'
ELSE 'complete'
END AS groupStatus
FROM myTable AS a-PatP|||I like that one better....|||Thanks Guys...Both of them are much better than the function I have

help needed

How to view the updated table in a day to day transaction in sql server2000?
Hi
Please explain what you actually want... If this is auditing then check out
the CREATE TRIGGER example in Books online as this has an example of simple
auditing. Another way of auditing is to use a third party too such as those
from Lumigent (Lumigent Log Explorer)
http://www.lumigent.com/ or PI, http://www.logpi.com
John
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?

help needed

How to view the updated table in a day to day transaction in sql server200
0?Hi
Please explain what you actually want... If this is auditing then check out
the CREATE TRIGGER example in Books online as this has an example of simple
auditing. Another way of auditing is to use a third party too such as those
from Lumigent (Lumigent Log Explorer)
http://www.lumigent.com/ or PI, http://www.logpi.com
John
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?

help needed

How to view the updated table in a day to day transaction in sql server2000?Hi
Please explain what you actually want... If this is auditing then check out
the CREATE TRIGGER example in Books online as this has an example of simple
auditing. Another way of auditing is to use a third party too such as those
from Lumigent (Lumigent Log Explorer)
http://www.lumigent.com/ or PI, http://www.logpi.com
John
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?|||Yes it is possible!!
Jay Freeman
The answer is only as descriptive as the question...
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?

help me in connection

I am using sql server and c# (windows application)>>

the task is to retrieve records from table in the database and view it in page load , with the abilty to choose the records >>

this means that select range >>> e.g. from 3 to 7 (this can done by textbox)

so how can i do this >>

then I shall have two search buttons ( by name and by id) >> how can I implement that >>>

the last thing is to do the validation for every thing i can validate how I can do this >>>

thank u at the beginning for help me

adorer:

I am using sql server and c# (windows application)>>

Since you are writing a Windows application, and these forums are for ASP.NET, you should post your question on theMSDN forums.

|||

SqlConnection cn =

new SqlConnection("User ID=sa;password=;Initial Catalog=Training;Data Source=local");

cn.Open();

DataSet ds =

new DataSet();

SqlDataAdapter da =

new SqlDataAdapter("select * from Students where ID = '"+ textBox1.Text + "'", cn);

da.Fill(ds, "Students");

dataGrid1.DataSource = ds;

cn.Close();

does this do the task