Alias or Group SSAS Dimension at Query time.

In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.

Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?

Any help will be appreciated.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

SET [GroupedRatings] AS

CASE

WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]

ELSE NULL

END

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.

eg.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})

MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}

MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})

MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

Member [Curve Family].[SP Rating].[AA] as

Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[AAA] as

Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BB] as

Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BBB] as

Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),

SOLVE_ORDER = 10

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],

[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.

WITH

MEMBER [Curve Family].[SP Rating].[AA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[AAA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BB] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BBB]AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})

SET [GroupedRatings] AS

{

[Curve Family].[SP Rating].[AA]

,[Curve Family].[SP Rating].[AAA]

,[Curve Family].[SP Rating].[BB]

,[Curve Family].[SP Rating].[BBB]

}

SELECT

NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,

NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS

FROM

[DM]

Alias in Query

For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchoo
lName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony RobinsonHi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
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/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchool
Name
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes matter
s worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:u%23cQP0
f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
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/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
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/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
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/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||...it's comparing it against a USERID with a value of 1:
AND WHERE U.UserID = CONVERT(varchar(15), 1)
think of it as
AND WHERE U.UserID = CONVERT(varchar(15), @.USERID)
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eEBDbNg7
FHA.3804@.TK2MSFTNGP14.phx.gbl...
You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
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/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
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/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinsonsql

Alert gets executed, but not its job. Am I doing something wrong?

I have an Alert that I verify gets activated (looked at its history under the General Tab) but it never executes the Execute Job under the Response tab.

Here is what I did:

1) Checked the Execute Job box
2) Created a job
a) Gave it a name
b) Gave it an owner
c) Left the category as Uncategorized
d) Under the Steps tab added a new step
i) Gave the step a name
ii) Selected Operating System Command (CmdExec) as the type
iii) In the Command box typed the following

cd C:\Blat
blat StandardMessage.txt -to Test.Test@.MyURL.com -s "TEST"
cd C:\

When I copy and paste that EXACT message (cd...blat...cd...) on the command line it works perfectly so it looks like the job isn't getting executed at all.

I also tried to set up an output file (under the Advanted Tab...Operating System Command. That file never gets created either! That makes me REALLY sure the job isn't getting executed.

So, if my Alert gets executed as verified by the history, why isn't my job being kicked off?

THANKS!what account the service is running under? probably system account. you need to have a domain account with minimal rights.|||Originally posted by ms_sql_dba
what account the service is running under? probably system account. you need to have a domain account with minimal rights.

You have lost me here.

My Alert executes just fine. I don't see any way to specify any "account" for the job that gets kicked off to run under.

Where should I look to specify the "account the service is running under"?

Thanks.|||Originally posted by darrinps
You have lost me here.

My Alert executes just fine. I don't see any way to specify any "account" for the job that gets kicked off to run under.

Where should I look to specify the "account the service is running under"?

Thanks.

I THINK what is happening is that the command I want to execute is local to my PC and the exec is attempting ro tun against the local server box.

What I need to do is either install the software there, or better yet (for testing) somehow redirect it to my box.|||try copying all the command line steps in a .bat file and then run that bat file from your scheduler.

Aggregation functions in Calculated Measures displays wrong values.

Hi,

I think this calculated measure implementation is making me absent minded, so if this seems like a silly question, please ignore my behaviour but do answer to my post :-)

I will ask this question with a sample data: consider that the cube consists of School Children's names as the first dimension (school_children) and date(jan, feb....) as the second dimension. the measure (M) is the 'exam scores' of the school children.

jan feb mar
school_children M M M
--
tony 50 20 40
bony 10 40 40
mony 60 60 70

Now when i add a calculated measure where I want to display the avg marks of each. so in the calculated measures formula I add: Avg([Measures].[M]). (This is how it is in the Oracle OLAP :-))

But this does not display the average of all tony's scores in a new column M2 (calculated measure). it just displays the same values as the measure M.

so what is happening here? how to get the average then? I do not want to use an avg Aggregation. I thought that I would probably have to programmatically convert all avg functions to something like this: [Measures].[M] / count([Measure].[M]=tony or soemthing like this. not sure again.

The Avg function receives a set as a first parameter, and the measure you want to calculate the average of as a second.

The second parameter is optional, so in this case you are saying to Analysis Services: "give me the average of the measures in the current query context for the set [Measures].[M]" That is: (Measures].[M] / 1)

Since you want to calculate the avg along the time dimension, you should say:

Avg([Time],[2006].Members, [Measures].[M]).

For mor information on Avg see:

http://msdn2.microsoft.com/en-us/library/ms146067.aspx

and

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390791&SiteID=1

Aggregate Problem

When I run the my code in Northwind, the ExtendedPrice is wrong because
UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice in
my statement because I couldn't figure out a better aggregate function to
use.
My goal is to simply get a GROUP by Orders.OrderDate and SUM the
ExtendedPrice expression for each date. I probably need some type of
subquery, but not sure how to go about this one.
CODE (used in northwind database):
SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
[Order Details].UnitPrice * [Order Details].Quantity)) AS ExtendedPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderDateHi
It may be clearer is you give your expected results, but this may be a
starting point!
SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
FROM Orders O
JOIN [Order Details] D ON O.OrderID = D.OrderID
GROUP BY O.OrderDate
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
> When I run the my code in Northwind, the ExtendedPrice is wrong because
> UnitPrice is averaged. I have AVG([Order Details].UnitPrice) AS UnitPrice
> in my statement because I couldn't figure out a better aggregate function
> to use.
> My goal is to simply get a GROUP by Orders.OrderDate and SUM the
> ExtendedPrice expression for each date. I probably need some type of
> subquery, but not sure how to go about this one.
> CODE (used in northwind database):
> SELECT Orders.OrderDate, AVG([Order Details].UnitPrice) AS UnitPrice,
> COUNT([Order Details].Quantity) AS Qty, AVG(CONVERT(money,
> [Order Details].UnitPrice * [Order Details].Quantity)) AS
> ExtendedPrice
> FROM Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order Details].OrderID
> GROUP BY Orders.OrderDate
>|||that did it. thx.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uty7J2Y3FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi
> It may be clearer is you give your expected results, but this may be a
> starting point!
> SELECT O.OrderDate, SUM(D.UnitPrice * D.Quantity) AS ExtendedPrice
> FROM Orders O
> JOIN [Order Details] D ON O.OrderID = D.OrderID
> GROUP BY O.OrderDate
> John
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:OtlJ$mY3FHA.636@.TK2MSFTNGP10.phx.gbl...
>

Again: eMail subscription

I got a clue what might be wrong on my server machines which do not allow
eMail subscription due to a "configuration error"
I have an XP machine here with a local SQL server and a local RS client and
server. If I look at the property page of a given report there is always
something like that (I have to translate it from german, so maybe it is a
bit different in english versions):
changed at: (datetime)
changed by: SPONGEBOB\roland
created at: (datetime)
created by: SPONGEBOB\roland
size: 8kb
SPONGEBOB is the name of the machine; roland the user account.
Whereas my server machines show up the following:
changed at: (datetime)
changed by: TACCO\Administrator (unknown)
created at: (datetime)
created by: TACCO\Administrator (unknown)
size: 8kb
TACCO is the server name; administrator the user account. My concern is the
"unknown", what exactly causes the problem with eMail subscription. What
does cause RS to print "unknown" there and how to come over it?
TIA
rolandHallo Roland,
ich hatte ein ähnliches Problem, nachdem ich das Konto unter dem der Dienst läuft geändert hatte, ich mu�te es in den Orginalzustand zurücksetzen dann gingen auch wieder die eMail Abbonements.
Hi Roland,
I had a similar problem, but after resetting the account of the servce back to the one I've used while installing, the email works!
hth
sequal
"Roland" wrote:
> I got a clue what might be wrong on my server machines which do not allow
> eMail subscription due to a "configuration error"
> I have an XP machine here with a local SQL server and a local RS client and
> server. If I look at the property page of a given report there is always
> something like that (I have to translate it from german, so maybe it is a
> bit different in english versions):
> changed at: (datetime)
> changed by: SPONGEBOB\roland
> created at: (datetime)
> created by: SPONGEBOB\roland
> size: 8kb
> SPONGEBOB is the name of the machine; roland the user account.
> Whereas my server machines show up the following:
> changed at: (datetime)
> changed by: TACCO\Administrator (unknown)
> created at: (datetime)
> created by: TACCO\Administrator (unknown)
> size: 8kb
> TACCO is the server name; administrator the user account. My concern is the
> "unknown", what exactly causes the problem with eMail subscription. What
> does cause RS to print "unknown" there and how to come over it?
> TIA
> roland
>
>|||Me again, let it run under: "NT AUTHORITY\NetworkService"
sequal
"Roland" wrote:
> I got a clue what might be wrong on my server machines which do not allow
> eMail subscription due to a "configuration error"
> I have an XP machine here with a local SQL server and a local RS client and
> server. If I look at the property page of a given report there is always
> something like that (I have to translate it from german, so maybe it is a
> bit different in english versions):
> changed at: (datetime)
> changed by: SPONGEBOB\roland
> created at: (datetime)
> created by: SPONGEBOB\roland
> size: 8kb
> SPONGEBOB is the name of the machine; roland the user account.
> Whereas my server machines show up the following:
> changed at: (datetime)
> changed by: TACCO\Administrator (unknown)
> created at: (datetime)
> created by: TACCO\Administrator (unknown)
> size: 8kb
> TACCO is the server name; administrator the user account. My concern is the
> "unknown", what exactly causes the problem with eMail subscription. What
> does cause RS to print "unknown" there and how to come over it?
> TIA
> roland
>
>|||Hallo sequal,
wir haben es sowohl unter System als auch unter NetworkServices installiert.
Leider hat bei uns zumindest nichts geholfen. Unsere Server gehören zu
keiner Domain, ist das bei Euch anders?
we tried to install RS under "NTAUTHORITY\System" as well as NetworkService.
Nothing did help. Our servers don't belong to a domain, is that different at
your side?
r.|||Hallo Roland,
checken Sie doch bitte mal Ihre LogFiles, \Programme\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles. Ich hatte exact die selbe Fehlemeldung nachdem ich den User unter dem die RS laufen sollten geändert habe, die Daten in der RepServ DB werden verschlüsselt, und im Logfile finden Sie zur geplanten Laufzeit der e-Mailsubscribtion eine entsprechende Fehlermeldung.
Unsere System läuft in einem ActivDirectory (Domäne) ich hatte alles in einer Testumgebung aufgesetzt (keine Domäne) und dann ins Echtnetzwerk übertragen, als ich die User unter welchen solche Dienste bei uns in der Firma laufen nachgepflegt habe ging plötzlich die e-Mail Subs. nicht mehr, aber alles andere lief.
Grüsse s.
P.S.: Anbei ein Auszug aus meinem Logfile welches mir den HinweiÃ? gab:
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Initializing crypto as user: NT-AUTORITÃ?T\SYSTEM
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Exporting public key
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Performing sku validation
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Importing existing encryption key
ReportingServicesService!library!d18!25.05.2004-16:08:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledException: Der Berichtsserver kann den symmetrischen Schlüssel nicht entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden Sie in der Dokumentation., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledException: Der Berichtsserver kann den symmetrischen Schlüssel nicht entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden Sie in der Dokumentation. --> System.Runtime.InteropServices.COMException (0x80090005): Ungültige Daten
"Roland" wrote:
> Hallo sequal,
> wir haben es sowohl unter System als auch unter NetworkServices installiert.
> Leider hat bei uns zumindest nichts geholfen. Unsere Server gehören zu
> keiner Domain, ist das bei Euch anders?
> we tried to install RS under "NTAUTHORITY\System" as well as NetworkService.
> Nothing did help. Our servers don't belong to a domain, is that different at
> your side?
> r.
>
>|||Sequal,
Danke für Deine/Ihre Bemühungen.
In
news:ucIk$9uVEHA.1152@.TK2MSFTNGP09.phx.gbl
habe ich mal die Inhalte der Logdateien dargestellt. Irgendwie matchen die
nicht Deine/Ihre Logs :)
Grüsse und Vielen Dank
Roland
"sequal" <sequal@.discussions.microsoft.com> schrieb im Newsbeitrag
news:67196D21-20B2-4B62-A09A-32E9F7DEFEA7@.microsoft.com...
> Hallo Roland,
> checken Sie doch bitte mal Ihre LogFiles, \Programme\Microsoft SQL
Server\MSSQL\Reporting Services\LogFiles. Ich hatte exact die selbe
Fehlemeldung nachdem ich den User unter dem die RS laufen sollten geändert
habe, die Daten in der RepServ DB werden verschlüsselt, und im Logfile
finden Sie zur geplanten Laufzeit der e-Mailsubscribtion eine entsprechende
Fehlermeldung.
> Unsere System läuft in einem ActivDirectory (Domäne) ich hatte alles in
einer Testumgebung aufgesetzt (keine Domäne) und dann ins Echtnetzwerk
übertragen, als ich die User unter welchen solche Dienste bei uns in der
Firma laufen nachgepflegt habe ging plötzlich die e-Mail Subs. nicht mehr,
aber alles andere lief.
> Grüsse s.
> P.S.: Anbei ein Auszug aus meinem Logfile welches mir den Hinweiß gab:
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Initializing crypto as user: NT-AUTORITÄT\SYSTEM
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Exporting public key
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Performing sku validation
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Importing existing encryption key
> ReportingServicesService!library!d18!25.05.2004-16:08:20:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: Der Berichtsserver kann den symmetrischen Schlüssel nicht
entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in
einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen
Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten
Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden
Sie in der Dokumentation., ;
> Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: Der Berichtsserver kann den symmetrischen Schlüssel nicht
entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in
einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen
Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten
Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden
Sie in der Dokumentation. --> System.Runtime.InteropServices.COMException
(0x80090005): Ungültige Daten
>
> "Roland" wrote:
> > Hallo sequal,
> >
> > wir haben es sowohl unter System als auch unter NetworkServices
installiert.
> > Leider hat bei uns zumindest nichts geholfen. Unsere Server gehören zu
> > keiner Domain, ist das bei Euch anders?
> >
> > we tried to install RS under "NTAUTHORITY\System" as well as
NetworkService.
> > Nothing did help. Our servers don't belong to a domain, is that
different at
> > your side?
> >
> > r.
> >
> >
> >

again and again... query analyzer and wrong SQL statement plan... + outer join?

Hi,
again we have some SQL statement performance problems...
I've 2 table with 140 000 records each.
I join the 2 tables and the result is also 140 000, but the query analyzer
estimate 1000 rows only.
Its a simple join with 5 equals in the join
select * from tableA inner join table B on A.ID1 = B.ID2.....
all the stats for my tables are ok.
its not a so big problem, but when I want to estimate before an execution
and when the result is far different, its a big surprise for me!
Also, we have aproblem with a query which use 1 inner join and 3 left outer
join.
Each outer join is a sub-query which contain a group by:
select *from A inner join B on A.ID = B.ID
left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
...
(the 2 others outer join use the same syntax)
This query takes more then 10 minutes, but if I precalculate each sub-query
in a temporary table and if I use these tables instead-of the sub-queries,
the result appear in only 19seconds!!!!!
how can I tell SQL Server to use the same way without creating myself the
temporary tables?
there is any table hint option to force SQL Server to treat the sub queries
has "physical tables".
I know its not the first time you have this type of questions... so thanks
for your time (again)
Jerome.
create an Index for those 5 columns on each table.. But only those 5 columns
ll be used..
And let SQL estimate the proper Index for the execution
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:uX1Ih0htEHA.3604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> again we have some SQL statement performance problems...
> I've 2 table with 140 000 records each.
> I join the 2 tables and the result is also 140 000, but the query analyzer
> estimate 1000 rows only.
> Its a simple join with 5 equals in the join
> select * from tableA inner join table B on A.ID1 = B.ID2.....
> all the stats for my tables are ok.
> its not a so big problem, but when I want to estimate before an execution
> and when the result is far different, its a big surprise for me!
> Also, we have aproblem with a query which use 1 inner join and 3 left
outer
> join.
> Each outer join is a sub-query which contain a group by:
> select *from A inner join B on A.ID = B.ID
> left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
> on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
> ...
> (the 2 others outer join use the same syntax)
> This query takes more then 10 minutes, but if I precalculate each
sub-query
> in a temporary table and if I use these tables instead-of the sub-queries,
> the result appear in only 19seconds!!!!!
> how can I tell SQL Server to use the same way without creating myself the
> temporary tables?
> there is any table hint option to force SQL Server to treat the sub
queries
> has "physical tables".
> I know its not the first time you have this type of questions... so thanks
> for your time (again)
> Jerome.
>

again and again... query analyzer and wrong SQL statement plan... + outer join?

Hi,
again we have some SQL statement performance problems...
I've 2 table with 140 000 records each.
I join the 2 tables and the result is also 140 000, but the query analyzer
estimate 1000 rows only.
Its a simple join with 5 equals in the join
select * from tableA inner join table B on A.ID1 = B.ID2.....
all the stats for my tables are ok.
its not a so big problem, but when I want to estimate before an execution
and when the result is far different, its a big surprise for me!
Also, we have aproblem with a query which use 1 inner join and 3 left outer
join.
Each outer join is a sub-query which contain a group by:
select *from A inner join B on A.ID = B.ID
left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
...
(the 2 others outer join use the same syntax)
This query takes more then 10 minutes, but if I precalculate each sub-query
in a temporary table and if I use these tables instead-of the sub-queries,
the result appear in only 19seconds!!!!!
how can I tell SQL Server to use the same way without creating myself the
temporary tables?
there is any table hint option to force SQL Server to treat the sub queries
has "physical tables".
I know its not the first time you have this type of questions... so thanks
for your time (again)
Jerome.create an Index for those 5 columns on each table.. But only those 5 columns
ll be used..
And let SQL estimate the proper Index for the execution
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:uX1Ih0htEHA.3604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> again we have some SQL statement performance problems...
> I've 2 table with 140 000 records each.
> I join the 2 tables and the result is also 140 000, but the query analyzer
> estimate 1000 rows only.
> Its a simple join with 5 equals in the join
> select * from tableA inner join table B on A.ID1 = B.ID2.....
> all the stats for my tables are ok.
> its not a so big problem, but when I want to estimate before an execution
> and when the result is far different, its a big surprise for me!
> Also, we have aproblem with a query which use 1 inner join and 3 left
outer
> join.
> Each outer join is a sub-query which contain a group by:
> select *from A inner join B on A.ID = B.ID
> left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
> on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
> ...
> (the 2 others outer join use the same syntax)
> This query takes more then 10 minutes, but if I precalculate each
sub-query
> in a temporary table and if I use these tables instead-of the sub-queries,
> the result appear in only 19seconds!!!!!
> how can I tell SQL Server to use the same way without creating myself the
> temporary tables?
> there is any table hint option to force SQL Server to treat the sub
queries
> has "physical tables".
> I know its not the first time you have this type of questions... so thanks
> for your time (again)
> Jerome.
>