cleaning wrong PK FK

Hello

my 2 tables in MS SQL 2000

Report :
Report_id (PK)
name

Product :
Product_id (PK)
Report_id (FK)
name

the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand

now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK

i am trying :

DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))

DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))

but the database crash : time overflow !

how can I do it ?

thank youHere are two alternate methods:--Method #1: EXISTS
delete
from Product
where not exists (select * from Report where Report.Report_id = Product.Report_id)

--Method #2: LEFT OUTER JOIN
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
In either method, make sure Report_id is indexed in both tables.|||thank you BlindMan

on the 2nd method i am getting :
Incorrect syntax near the keyword 'left'.|||Post your code.|||that one

delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null

Incorrect syntax near the keyword 'left'

you said : in either method, make sure Report_id is indexed in both tables.

they are PK to FK but they are not indexed
how can I do it when the tables are allready full

ALTER TABLE create index ?

thank's a lot|||I'm assuming that you defined the PK and FK in your head, but haven't done anything with the database. A PK that doesn't exist using a PRIMARY KEY definition is only a good intention from my perspective. ;) I would suggest using something like:CREATE INDEX dropme01 ON Report (Report_Id)
CREATE INDEX dropme02 ON Product (Report_Id)

DELETE FROM Report
WHERE NOT EXISTS (SELECT *
FROM Product
WHERE Product.Report_Id = Report.Report_Id)

DELETE FROM Product
WHERE NOT EXISTS (SELECT *
FROM Report
WHERE Report.Report_id = Product.Report_Id)

DROP INDEX Report.dropme01
DROP INDEX Product.dropme02

ALTER TABLE Report
ADD CONSTRAINT XPKReport
PRIMARY KEY (Report_Id)

ALTER TABLE Product
ADD CONSTRAINT XPKProduct
PRIMARY KEY (Product_Id)

ALTER TABLE Product
ADD CONSTRAINT XFK01Report
FOREIGN KEY (Report_Id)
REFERENCES Report (Report_Id)-PatP|||Pat FK and PK are allready in the tables|||Pat FK and PK are allready in the tables
Are you having any problem now?|||with your first method it works very well
i was just wondering why it doesnt with the second method

but it works ...

thanks a lot|||Public kya Time pass karne aati hai kya idhar?|||Public kya Time pass karne aati hai kya idhar?

No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"|||No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)|||Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)
........;)|||Sorry. There was a syntax error in my second example. This should work:
delete Product
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null|||i try it thank you

Circular Reference?

I get this error when I ran the below statement what did I do wrong?

"The definition of MonthRange set contains a circular reference"

WITH SET [MonthRange] AS

{

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].PrevMember

:

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

}

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([MonthRange], [Industry].&[Subprime])

[Dim Originationasofmm] is a regular dimension with members like 200101, 200102, 200103 so on.

What I wanted to do (Note that I do not have a Time Dimension in the cube, just a dimension that simulates this, so would this work just as well? )

Can I have a generic set that would take as input current member and give 3 or 6 or 12 rolling months?

(this is assuming that I cannot switch to using time dimension anytime soon and just have to use a regular dimension for now?)

For example,

given 200101 and say 3 for 3 months rolling period, I would get 200012, 200101, 200102

and

given 200101 and say 7 for 6 months rolling period, I would get 200010, 200011, 200012, 200101, 200102, 200103, 200104

Try this version:

WITH

SET [MonthRange] AS

LastPeriods(3,

[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember

)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] as

Aggregate([MonthRange])

SELECT {[Measures].[Closing Balance]} on 0,

{[Dim Asofmm].FirstChild : [Dim Asofmm].[200612]} on 1

FROM [ Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],

[Industry].&[Subprime])

|||

Hi Deepark,

This query works! but as soon as I add any other measure below (1 or more) to the query, only column that has data would be [Closing Balance] and all other measures are NULL.

Is it because of some SCOPING issue in the calculation?

--Query only returns data for [Closing Balance], all other measures are NULL when they should have data.

WITH SET [MonthRange] AS
LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange])

SELECT {
[Measures].[Closing Balance],
[Measures].[D BankRuptcy],
[Measures].[D Foreclosure],
[Measures].[Def OTS]
} on 0,
{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1
FROM [Bond Analytics OLAP]
WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])
GO

Calculations:

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Bankruptcy] Then
([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Bankruptcy],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Foreclosure] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[D BankRuptcy]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D BankRuptcy]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Bankruptcy]);
THIS = ([Dim MBA].[Bankruptcy],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim MBA].currentmember IS [Dim MBA].[Foreclosure] Then
([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance])
When [Dim MBA].currentmember IS [Dim MBA].[Current] Then Null
When [Dim MBA].currentmember is [Dim MBA].[All] then
([Dim MBA].[Foreclosure],[Measures].[% By Delinquincy Currentbalance])
When [Dim MBA].currentmember is [Dim MBA].[MBA 30] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 60] then null
When [Dim MBA].currentmember is [Dim MBA].[MBA 90] then null
When [Dim MBA].currentmember is [Dim MBA].[Bankruptcy] then null
When [Dim MBA].currentmember is [Dim MBA].[REO] then null
end,
FORMAT_STRING = "Percent",
VISIBLE = 1; */

CREATE MEMBER CURRENTCUBE.[MEASURES].[D Foreclosure]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[D Foreclosure]);
SCOPE(ROOT([Dim MBA]));
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim MBA].[Foreclosure]);
THIS = ([Dim MBA].[Foreclosure],[Measures].[% by Delinquincy Currentbalance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;
-

/* Rewrite of this to use SCOPE is below
CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null
When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])
When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;
*/

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE([Measures].[Def OTS]);
SCOPE(ROOT([Dim OTS]));
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = 1;
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;
END SCOPE;

|||

Well, I was able to reproduce this behavior in Adventure Works by creating a calculated measure with default value of NULL (as above); then just assigning it a constant value. But setting the new SP2 SCOPE_ISOLATION property seems to solve it - like:

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

|||

Hi Deepark,

Ok, so I don't know what SCOPE_ISOLATION does, but it works!!!

Perhaps, it is a new feature in SQL Server 2005 SP2.

Maybe it has something to do with calculation order.

Thank you very much.

You have helped me more than once (on many other discussion groups as well) and I am very grateful for that.

I think it's time you get a new title --> SUPER MVP

by the way, the code that works look like this:

--so if I want another period, I just replace # 3 and [200101] with appropriate member chosen by the user.

--I wonder if I finally do have a Time dimension, would using CurrentMember work so I don't have to hard-code [200101]?

WITH SET [MonthRange] AS

LastPeriods(3,[Dim Originationasofmm].[Dim Originationasofmm].&[200101].NextMember)

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

SELECT {

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

{[Dim Asofmm].FirstChild:[Dim Asofmm].[200612]} on 1

FROM [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])

GO

--this one also works, so if I want another rolling period, I just replace the lag #

WITH SET [MonthRange] AS

{[Dim Originationasofmm].&[200612].lag(1):[Dim Originationasofmm].&[200612].lag(-1)}

MEMBER [Dim Originationasofmm].[Dim Originationasofmm].[MonthRange] AS Aggregate([MonthRange]),

SCOPE_ISOLATION = CUBE

select

{

[Measures].[Closing Balance],

[Measures].[D BankRuptcy],

[Measures].[D Foreclosure],

[Measures].[Def OTS],

[Measures].[D30 OTS],

[Measures].[D60 OTS],

[Measures].[D90 OTS]

} on 0,

([Dim Asofmm].FIRSTchild:[Dim Asofmm].[200612]) on 1

from [Bond Analytics OLAP]

WHERE ([Dim Originationasofmm].[Dim Originationasofmm].[MonthRange],[Industry].&[Subprime])