Like we have If Not Exists in T-SQL, Is there any alternative in MDX ?

Question

Post reply

Like we have If Not Exists in T-SQL, Is there any alternative in MDX ?

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
September 22, 2014 at 3:39 am

#148678

Hi All,
I am a newbie in MDX. I am trying to execute MDX query through SSIS Execute SQL Task. I have set Single Row as a result set. Every thing is fine, But the problem arise when the query does not return any result.
Because , this is the property of Execute SQL Task that it fails , If result set is SET and query does not return any value.
This occurance is handle in T-SQL by used of If Not Exits/If Exists , Do we have something over here i.e. MDX ?
If not then what might be the alternative for this.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17461 More actions · Answer 1

It depends what type of value you are looking to return but you could do an IIF(IsEmpty( construct in a member.

So for example in adventure works I could say:

WITH MEMBER [IIFExample]

AS

IIF(IsEmpty(SUM({[Geography].[Geography].[State-Province].&[ML]&[US]}, [Measures].[Reseller Sales Amount])),

10, SUM({[Geography].[Geography].[State-Province].&[ML]&[US]}, [Measures].[Reseller Sales Amount]))

SELECT

{[Measures].[Reseller Sales Amount],[IIFExample]} ON 0,

[Geography].[Geography].[State-Province].Members ON 1

FROM

[Adventure Works]

This will show a value of "10" against all members in the result as the Maryland state reseller sales is null.

You could apply this logic to whatever you are looking at and return a default result so it won't break the SSIS control flow.

I'm on LinkedIn

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 2

Hi PB_BI,

Thanks for the reply, but i am using the following query,

SELECT top 1 lock_object_id

FROM $System.DISCOVER_LOCKS

WHERE Lock_type = 4

I googled but i found out that i cannot use, Iff in the above query.

Do you know some other alternative to do so, could you please share if doing the above is possible.

PB_BI SSCoach Points: 17461 More actions · Answer 3

Shadab Shah (9/22/2014)
Hi PB_BI,
Thanks for the reply, but i am using the following query,
SELECT top 1 lock_object_id
FROM $System.DISCOVER_LOCKS
WHERE Lock_type = 4
I googled but i found out that i cannot use, Iff in the above query.
Do you know some other alternative to do so, could you please share if doing the above is possible.

Ok, well strictly speaking that isn't MDX so IIF won't be of any use.

Are you using OpenQuery (as I suggested for you to use in a previous post for this query)? If so just do a SELECT ISNULL(lock_object_id, 'whatever') FROM OpenQuery( etc.

I'm on LinkedIn

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 4

For OpenQuery we need to have Linked Server and building Linked Server on this DB Server is not feasible :ermm:

PB_BI SSCoach Points: 17461 More actions · Answer 5

Then it isn't possible in DMX alone. You would have to build something with the precedence constraints within SSIS to get around the task returning no rows.

I'm on LinkedIn