Default member problem

Hi,

I have Employee dimension with DomainName attribute which is used for role - based access to cube data.

I've tried to set DefaultMember using MDX expression and username function, like this:

IIF(StrToMember('[EmployeeDim].[DomainName].&[' + username + ']') IS NULL,[EmployeeDim].[DomainName][All], [EmployeeDim].[DomainName].&[' + username + ']')

but when I try to deploy this dimension I've got error message "The level '&[PEXIMBG\borko.novakovic]' object was not found in the cube when the string, [EmployeeDim].[DomainName].&[PEXIMBG\borko.novakovic], was parsed" when there is no any EmployeeDim member which have my username defined!

I thought that usage of IIF will resolve this error, but did not.

Is there any advice or hint how to avoid this behavior, because it is not common case that cube developer is member of EmployeeDim dimension which is populated from customer table.

Thanks in advance

Borko

How about if you test StrToMember() for an error in IIF(), rather than for Null, like:

IIF(IsError(StrToMember("[EmployeeDim].[DomainName].&[" + username + "]")),

[EmployeeDim].[DomainName][All],

StrToMember("[EmployeeDim].[DomainName].&[" + username + "]"))

|||

It works great!

Why IsError is not described in SQL Server Books Online?

I'm new in MDX and I did not know how to prevent engine to raise an error.

Anyway, Deepak, thank you very, very much!

default datetime error

hi friends,

i have two datetime parameters in my report ... based on these parameters , am searching records ... the issue is i cant get the full datetime value

for example

i am searching records based on 03/16/2007 and 03/20/2007... i can't the records for the date 03/20/2007...

bcoz, the value of the date is '03/20/2007 00:00:00.000'

i want to get the value like this ' 03/20/2007 11:59:59 pm''

can any one help me

You can specify the time part for the parameter and retrieve the records. I mean, you can type 3/20/2007 11:59:59 PM in the parameter field.

Shyam

|||

but the issue is i just want to give my date only... like this 3/20/2007

... it has to take the time by default 11:59:59 PM ....

|||

You have to specify the expression for your date parameter in dataset. For example, if your parameter name is @.to_date in your stored procedure, the report parameter name would be to_date.

Go to Data tab and select the appropriate dataset from the dropdown and then click the Edit (...) button besides it. Go to Parameters tab and for the @.to_date parameter, give the expression for Value as:

=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Day, 1, Parameters!to_date.Value))

This will add 1 day to your given date and then subtract 1 second from that date and this value will be passed on to the stored procdure or SQL query (whichever you have used)

Shyam

|||i am pleased to thank you shyam...thanx for your help .. i have implemented ...|||

I know this solution is definitely working. But just wanted to be perfect on this. :-) The "correct" way to do something like this is to offset 3 milliseconds instead of 1 second. HTH.

DECODE?

I have to run a query to give a column a value based on a time range. Can I
use DECODE?

select decode(trans_date, trans_date>='01-Jul-2002' and
trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
from. . .
where. . .Sherman H. (shung@.earthlink.net) writes:
> I have to run a query to give a column a value based on a time range.
> Can I use DECODE?
> select decode(trans_date, trans_date>='01-Jul-2002' and
> trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
> from. . .
> where. . .

Maybe in some other DBMS, but there is no such function in SQL Server.

I don't know what decode is supposed to achieve, but it seems that
the CASE expression might to the task:

SELECT CASE WHEN transdate BETWEEN '20020701' AND '20030630'
THEN 'Fiscal2002'
...
END

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

declare variable based on existing column

Is it possible to declare a variable based on an existing column?
Instead of:
DECLARE @.myvariable VARCHAR(20)
Use:
DECLARE @.myvariable mytable.mycolumn%type
WHERE
Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)Not without doing the entire operation in dynamic SQL -- in other words, not
easily and probably not a great idea. Why would you want that
functionality?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
> Is it possible to declare a variable based on an existing column?
> Instead of:
> DECLARE @.myvariable VARCHAR(20)
> Use:
> DECLARE @.myvariable mytable.mycolumn%type
> WHERE
> Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)
>|||ORABLE has that functionality. I admit it's a nice thing, especially when
putting together smaller systems where data types change.
What about using a SQLVariant? I've rarely used that ... does that
Internally store the type (likea VB/JScript type deal)? If so, I'd immagine
that'd work in most scenarios ... not sure how it rates on the Best PRactice
scale though ...
-- Alex
"Adam Machanic" wrote:

> Not without doing the entire operation in dynamic SQL -- in other words, n
ot
> easily and probably not a great idea. Why would you want that
> functionality?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
>
>|||"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:ACC91FFB-ADC5-4DD8-9966-05B78F22422C@.microsoft.com...
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd
immagine
Yes, it's very similar to those languages' variant datatypes -- and just
like using them, it has lots of pitfalls... I wouldn't use it in production
code, personally, although I have found it useful in a few utility
operations -- but I had to work around a lot of problems, especially dealing
with datetime data.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||The larger the system, the more useful this is.
And its not even the data type changing, but more often the size of VARCHAR
fields for example. You design your system with LAST_NAME as VARCHAR(50),
then realize you need VARCHAR(1000). To make this change in SQL Server, it
seems that you would have to go through all your code to change any local
variables that are @.LAST_NAME. If you could base the declaration on the tabl
e
column, you would not have to do this.
In addition, it is good for global standardization of data types and sizes.
"Alex Papadimoulis" wrote:
> ORABLE has that functionality. I admit it's a nice thing, especially when
> putting together smaller systems where data types change.
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd immagi
ne
> that'd work in most scenarios ... not sure how it rates on the Best PRacti
ce
> scale though ...
> -- Alex
> "Adam Machanic" wrote:
>|||sqlwish@.microsoft.com
Ask for DOMAINs, an ANSI SQL feature (Google for more info on it) -- it's
definitely at the top of my wishlist, too.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:332D49B5-1442-46BF-A821-77C6FF61D4BA@.microsoft.com...
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of
VARCHAR
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the
table
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and
sizes.
> "Alex Papadimoulis" wrote:
>
when
immagine
PRactice
words, not|||You could use sp_addtype to create your own types which are stored in
systypes, then you can define data table columns, sp and udf parameters, and
variables using the user defined types.
in addition, INFORMATION_SCHEMA.COLUMNS contains the columns DOMAIN_CATALOG,
DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
altered to accomodate the changed type definition. you can also do a search
on syscomments to find any additional objects that need to be recompiled in
addition to the table alterations.
note: sp_rename can be used to rename a user defined type.
"Bevo" wrote:
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of VARCHA
R
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the ta
ble
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and sizes
.
> "Alex Papadimoulis" wrote:
>|||"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:23455E9D-7788-4229-80DB-DD46292346BD@.microsoft.com...
> You could use sp_addtype to create your own types which are stored in
> systypes, then you can define data table columns, sp and udf parameters,
and
> variables using the user defined types.
> in addition, INFORMATION_SCHEMA.COLUMNS contains the columns
DOMAIN_CATALOG,
> DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
> altered to accomodate the changed type definition. you can also do a
search
> on syscomments to find any additional objects that need to be recompiled
in
> addition to the table alterations.
Brian,
sp_addtype is deprecated in the next version of SQL Server. I recommend
that you do not use it. Unfortunately, those DOMAIN columns don't provide
full DOMAIN support -- the ANSI domains, as I understand them, operate
similarly to the types that can be added with sp_addtype, but with much
greater flexibility and bound constraints.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||>> the ANSI domains, as I understand them, operate similarly to the types
Other than offering a syntactic shorthand, what else can ANSI domains do?
Would it offer anything meaningful in terms of simplification, flexibility
and utility of existing ANSI standard built-in types? If T-SQL UDTs support
binding of constraints, wouldn't it obviate the need for ANSI domains, if
that is the lacking provision?
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OLLBtY1GFHA.3536@.TK2MSFTNGP14.phx.gbl...
> Other than offering a syntactic shorthand, what else can ANSI domains do?
> Would it offer anything meaningful in terms of simplification, flexibility
> and utility of existing ANSI standard built-in types? If T-SQL UDTs
support
> binding of constraints, wouldn't it obviate the need for ANSI domains, if
> that is the lacking provision?
Two things:
A) Correct me if I'm wrong but I know of no way to alter a T-SQL UDT.
The ANSI Standard does provide ALTER DOMAIN syntax. The OP in this
situation, it appears, wants his variables to be able to mimic the same
datatype used in the table -- even if that datatype should change (e.g. if
changing business requirements mean that the datatype needs to support 100
bytes instead of 50). Is that possible with a T-SQL UDT as they are
currently implemented?
B) My understanding is that the constraints bound to ANSI DOMAINs extend
to variables declared of a domain datatype... so if I define a domain
INTBETWEEN1AND10, which is an integer with a constraint that it must be
between 1 and 10, that will be enforced even for local variables of that
type. That's not the case with T-SQL UDTs, is it?
If I'm wrong on both of these counts, then I regret not making much
heavier use of T-SQL UDTs in my work to date -- I've pretty much ignored the
feature as I have been under the impression that it's inflexible and doesn't
add value.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Declare cursor based on dynamic query

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @.tempSQL varchar(1000)

This query will be generated based on my other conditon and will be stored in a variable

set @.tempsql = 'select * from orders'

declare cursor test for @.tempsql

open test

This code is not working.

please suggest

Nitin

Hi

I am writing the code as below

Declare @.testSQl varchar(1000)

set @.testsql = 'select * from orders'

declare test1 cursor for @.testSQl

The declare statement is not working . My @.testsql will be generated at run time.

Help

Nitin

|||You can not use dynamic sql while opening the cursors..

it should be like this

Declare Test1 cursor for
Select * From Orders|||

You could add the cursor creation to your dynamic sql and then just call sp_executesql for the built up string. Something like...

DECLARE @.sql nvarchar(4000)

--Get beginning of cursor

SELECT @.sql = 'DECLARE c CURSOR FOR'

--Decision code for what query is built

SELECT @.sql = @.sql + 'SELECT * FROM orders'

--Remainder of cursor with specific columns from above query

SELECT @.sql = @.sql + 'OPEN c FETCH NEXT FROM c INTO ....'

--Execute the string we just built

EXEC sp_executesql @.sql

|||

I don't like to ever advocate the use of cursors, but you can do this using a global cursor, if you really must:

create procedure test
as
declare @.name nvarchar(128)
exec ('declare bob cursor global for select name from sys.objects')
open bob

fetch next from bob into @.name
select @.name as works
close bob
deallocate bob
go

test

|||

Hi,

I dont know for the moment how to declare a cursor on a query from a string.. I dont think its possible this way. An alternative is to find a solution other than using the cursor, else you'd lose development time in trying to find a solution.

If you cannot find a solution, try to explain the problem, someone will try help out, and also cursors generally tend to be less performant.

|||this is not possible. i agree with waaz|||

Instead of local cursor, you can create a Global cursor with dynamic sql, which is available beyond the scope the dynamic sql

like this

set @.sql='declare test cursor global for '+ @.tempsql

exec sp_executesql @.sql

open test

close test

|||You can use dynamic SQL to create a global cursor as shown in another reply in this thread. But what are you trying to do? Why do you need to use a cursor? And why do you need to use dynamic SQL? Both have performance implications. And dynamic SQL has serious security implications that can compromise your database system and/or network. You will have to use techniques (both in the database and client-side depending on how you call your SP) that avoid SQL injection to protect your database and network from malicious users. Apart from these problems, dynamic SQL requires more maintainence because you have to grant more permissions to end users since checks are deferred to run-time unlike SPs with static SQL statements. So it is easy to create a cursor dynamically but that is not the right thing to do in majority of the cases.|||

Also, try not to ask the same question twice. This question was also answered in another thread. I have merged the threads into one.

|||

hey whitney,

i got the same problem of dynamic query with cursors..

You gave the alternative but i got the big cursor and its difficult for me to put the entire stuff in string.

Because it gets difficult to maintain for me.

Any help or comment regarding this will be appreciated.

Thanks a ton!!

dromyl@.hotmail.com

Decision Statement

Hello all,
I need to generate a result based on 2 fields:
1. RelationshipID &
2. MaritalStatusID
The above fields are not embedded in the application as a
mandatory check box so some of the values maybe empty. I
need to get a list of all the members who have a
RelationshipID = 2 and if it is NULL look in
MaritalStatusID = 1 and if that is NULL, display the
result anyways. I do not need to show the values from
these fields as they are a part of my parameters (filters)
in the query. I am not a programmer and wanted to know how
would I write this in a IF THEN statement in SQL?
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber
(and fr.RelationshipID = 2 or
-- Filter for Relationship = 2 or MaritalStatusID = 1
and ''''
I greatly appreciate any help in architecting the code in
this regard.
Thansk again.
Joshitry this...
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber&&&-- Filter for
Relationship =2 or MaritalStatusID = 1--
or fr.RelationshipID = 2 or maritalstatusid = 1
"J. Joshi" wrote:

> Hello all,
> I need to generate a result based on 2 fields:
> 1. RelationshipID &
> 2. MaritalStatusID
> The above fields are not embedded in the application as a
> mandatory check box so some of the values maybe empty. I
> need to get a list of all the members who have a
> RelationshipID = 2 and if it is NULL look in
> MaritalStatusID = 1 and if that is NULL, display the
> result anyways. I do not need to show the values from
> these fields as they are a part of my parameters (filters)
> in the query. I am not a programmer and wanted to know how
> would I write this in a IF THEN statement in SQL?
> select Distinct pip.MemberID
> from tblMemberInsurancePlan pip
> Inner Join tblMember m on m.MemberID = pip.MemberID
> Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
> pip.InsurancePlanID
> Left Join ctblFamilyRelationship fr on fr.RelationshipID =
> pip.RelationToSubscriber
> Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
> m.MaritalStatusID
> -- Filtering for Current insurance only
> where (pip.InsurancePlanState in ('C') and
> pip.InsurancePlanOrdinal = 1)
> -- Filtering for Dependents only
> and pip.MemberID <> pip.InsurancePlanSubscriberID
> -- Filtering for 'CHILD' relationship to the subscriber
> (and fr.RelationshipID = 2 or
> -- Filter for Relationship = 2 or MaritalStatusID = 1
> and ''''
>
> I greatly appreciate any help in architecting the code in
> this regard.
> Thansk again.
> Joshi
>|||On Tue, 8 Feb 2005 10:41:46 -0800, J. Joshi wrote:

>I need to generate a result based on 2 fields:
>1. RelationshipID &
>2. MaritalStatusID
>The above fields are not embedded in the application as a
>mandatory check box so some of the values maybe empty. I
>need to get a list of all the members who have a
>RelationshipID = 2 and if it is NULL look in
>MaritalStatusID = 1 and if that is NULL, display the
>result anyways. I do not need to show the values from
>these fields as they are a part of my parameters (filters)
>in the query. I am not a programmer and wanted to know how
>would I write this in a IF THEN statement in SQL?
Hi Joshi,
You can do the selection in the WHERE clause of the query:
WHERE (other requirements)
AND ( RelationshipID = 2
OR (RelationshipID IS NULL AND ISNULL(MaritalStatusID, 1) = 1))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)