Having Clause in MDX

I have the following relationships,

DimTest > FactTestScores <- DimStudents

so if I have 5 tests, A, B, C, D, E

how do I write an MDX statement to get all students who have taken all 3 tests (not just one of the 3) A,B,C ?

pseudocode: something like this would work?

SELECT {} on 0, DimStudents.Members on 1

FROM CUBE

WHERE (A,B,C)

can I use HAVING CLAUSE anywhere to make this work?

http://www.biblogs.com/2006/01/26/the-having-clause/

thanks

Assuming that there is a "count" measure on FactTestScores like [TestCount]:

With Set [SelectedTests] as {A, B, C}

select {} on 0,

DimStudent.Student.Student.Members

Having Count(NonEmpty([SelectedTests],

{[Measures].[TestCount]})

= Count([SelectedTests] ) on 1

from CUBE

|||

thank you Deepak

Please remind me one of these days how I can get you a gift to thank you for all your help.

Having a problem inserting products

I am trying to write a bit of code that I can pass a brand name to. If the brand name exists I want to return the brandid to the calling middle tier. If the brand id does not exist I want to insert and then return the new brand id. The code below works unless the brand does not exist. Then it inserts, and I get an application exception. Next time I run the code it continues on until the next time it has to do an insert. So the inserts are working, but getting the value back is resulting in an application excetio.

Middle Tier Function (

privatestaticint GetBrandForProduct(clsProduct o)

{

int brandid = -1;// If the brand name comes in blank use the first word of the overstock product

o.BrandName = o.BrandName.Trim();

// if we do not have a brand for this productif (o.BrandName.Length == 0)return -1;Database db =CommonManager.GetDatabase();;try

{

// Get the brand id for this brand name// If it does not exist we will add it and STILL return a brand idobject obj = db.ExecuteScalar("BrandIDGetOrInsert", o.BrandName);string catid = obj.ToString(); *** FAILING LINE ***returnConvert.ToInt32(obj.ToString());

}

catch (Exception ex)

{

throw ex;return -1;

}

return brandid;

}

Stored Procedure: --------------------------------------------------

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)

AS

BEGIN

-- SET NOCOUNT ONSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)-- If we found a record, exit

if@.@.rowcount> 0return-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)

END

Hi Dear,

You have not mention that What is the Exception Message? I will be in better position to answer if you share Exception Message also...

but the One thing that seems wrong in your SP is

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

@.@.rowcount return 0 if select statement didn't find any result......

so i think this check will be like this

if@.@.rowcount = 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

change this thing in your Store Procedure , if problem doesn't solve ..then post the Exception message...

Thank You

Best Regards,

Muhammad AKhtar Shiekh

|||

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

|||

patrick24601:

Why would I do that? If the rowcount > 0 then I am happy with the first select and I want the SP to exit. It will have returned the brandid that I need. If the rowcount = 0 then I want to do the insert.

That's what i am saying but there is contradiction in Your SP...it is doing this

if@.@.rowcount> 0return

-- We did not find a record, so add a new one.

begin

insertinto brands(Brandname)values(@.brandnameparm)

end

It is actually inserting when RowCount is greater then 0 ( Not equal to 0)

......

patrick24601:

The ASP.NET codes an 'object not defined' exception. The brandid is not being returned after the insert - possible two rows are being returned also which I think an executescalar would not be happy with. How do I get only row to return in either case?

You can try this code,

ALTER

PROCEDURE [dbo].[BrandIDGetOrInsert]-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endelseSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END

Thanks

Best Regards,

Muhammad AKhtar Shiekh

|||

sorry SP is no correct in above post, Remove the else part in the sp

ALTERPROCEDURE [dbo].[BrandIDGetOrInsert]

-- Add the parameters for the stored procedure here

@.brandnameparm

varchar(50)ASBEGIN-- SET NOCOUNT ONIFnotexists(SELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm))begininsertinto brands(Brandname)values(@.brandnameparm)endSELECT brandidfrom brandswhereLower(brandname)=Lower(@.brandnameparm)END|||

i think the problem lies with this:

if (o.BrandName.Length == 0)return -1;

Database db =CommonManager.GetDatabase();

;

Notice the stray ; also the if statement is missing some braces

if (o.BrandName.Length == 0)
{
return -1;
}

Thats what looks wrong to me

has anyone done something like this

I have to write an app the will take a csv file and parse the results into sql server. Not a big deal really but I need to be able to work on the data and return my own values for sql server depending on the value of data. Ideally it would be nice if i could create this an a executable. Does anyone know of any good tutorials that describe how to do this.

THanksHere's how to read CSV files using .NET:
http://weblogs.asp.net/fmarguerie/archive/2003/10/01/29964.aspx

You could read it into a DataSet and work with it from there.

Handling Schema Changes

What is the best way to handle schema changes as database needs change.
What I need to find out is
what is the best way to write database interface code in order to be
flexible to schema changes. Would having stored procedures as an interface
mechanism be best so that old app software will still be compatible? It is
impractical for us to upgrade all apps simultaneously, so if a new column is
added to a table, old apps will not fill in this column while newer apps who
support it will.

ThanksUse stored procedures as your data access layer. Supporting multiple
applications is much easier that way because the SPs can insulate the
application from underlying schema changes. This is just one of the
benefits of using SPs.

--
David Portas
SQL Server MVP
--

Hand needed with t-sql

Is it possible to write these two blocks of code as one? The only
difference between them is the AND clause: AND is_trade_date = 1 versus AND
is_sett_date = 1.

Cheers,

David

IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_trade_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_sett_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ENDDavid (auto87829@.hushmail.com) writes:
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus
> AND is_sett_date = 1.

If my guess of the logic is right:

IF EXISTS (SELECT 1
FROM calendar
WHERE calendar_date = @.date
AND (is_trade_date = 1 OR @.trade_dates = 0)
AND (is_sett_date = 1 OR @.trade_dates = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
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|||Why not:
IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date AND
(is_trade_date = 1 OR is_sett_date = 1)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END

Ricardo

"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END|||The following block should do the trick:
IF EXISTS ( SELECT 1
FROM calendar
WHERE calendar_date = @.date AND
(
( is_trade_date = 1 AND
@.trade_dates = 1
)
OR
( is_sett_date = 1 AND
@.trade_dates <> 1
)
)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END

although not very elegant...

Ricardo.
"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END

H

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!