MDX Where clause

Posted by Jeff Rix on January 13, 2012 under MDX | Be the First to Comment

This MDX query shows delinquent balance and original principal balance across the servicing plan members with a rate type id equal to 1.

SELECT {[measures].[Delinquent Balance], [measures].[Original Principal Balance]} ON COLUMNS ,NON EMPTY [Servicing Plan WID].[Servicing Plan ID].MEMBERS ON ROWS FROM [Cube T]
WHERE {([Dim Rate Type].[Rate Type ID].&[1])};

Update using joins- Query returning multiple values

Posted by Jeff Rix on under T-SQL | Be the First to Comment

If you are updating a tables column by selecting a column from another table there are different syntax you may use. If the select statement’s column contains more than 1 value for the specified join condition some syntax’s will update the value multiple times while another will fail the update statement.

In this example Dennis Rodman has multiple jersey player numbers in the UpdatePlayerData table.

create table PlayerData

(

Player_ID int identity(1,1) primary key,

Player_Name varchar(50) not null,

Player_Number int not null

)

insert into PlayerData values

('Michael Jordan', 23),('Scottie Pippen', 33),('Dennis Rodman', 91)

create table UpdatePlayerData

(

Player_ID int not null,

Player_Number int not null

)

insert into UpdatePlayerData values

(3, 10),(1, 45),(3,73)

The first two statements execute successfully and use the same execution plan. They update Dennis Rodman’s jersey to 10 and then to 73.

--Update 1

update PlayerData

set Player_Number = x.Player_Number

from

(

select Player_ID, Player_Number from UpdatePlayerData

) as x

where x.Player_ID = PlayerData.Player_ID

--Update 2 

update a

set a.Player_Number = b.Player_Number

from PlayerData as a join

UpdatePlayerData as b on a.Player_ID = b.Player_ID

The last value for his jersey is what is now displayed in the table.

This update syntax will fail the query stating that the sub-query returned more than 1 value.

--Update 3 that will fail 

update PlayerData

set Player_Number = (select upd.Player_Number from UpdatePlayerData as upd

					where upd.Player_ID = PlayerData.Player_ID)

where Player_ID in (select distinct Player_ID from UpdatePlayerData)

Even though it fails in this example I would generally advice using this syntax so you can control the logic that decides which value it will set the column to as opposed to just setting it to the last value it processes.