Difficult Insert where clause.

Sorry for anyone who has seen this query and dataset before but this is a
seperate question/issue which I am working on.
I am struggling trying to get my insert statement not to insert record 5
because the ToURN has been used before in a previous record(1).
Basically I am trying to write some logic which says that if the ToURN in
one record already exists in the FromURN field of a previous record then do
not insert the record.
Does anyone know how I would write the sql to do this.
RecNO MergeFromURN MergeToURN MergeDateMerged
1 100 200 15/06/1982
2 200 300 15/06/1982
3 300 400 15/06/1982
4 500 600 15/06/1982
5 700 100 15/06/1982
6 100 100 15/06/1982
7 NULL 100 15/06/1982
8 700 0 15/06/1982
So far I have the following sql but need to go that step further to stop
record 5 being inserted because 100 already has been inserted as a from urn
in record 1.
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
(select MoveToURN from Move))
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN
while @.@.ROWCOUNT > 0
begin
update A set MoveToURN = B.MoveToURN
from Move A
inner join Move B on A.MoveToURN=B.MoveFromURN
end
Can anyone help me with this.Stephen
You are posted this question a few times some time ago , so people gave you
solutuion (me include), so would you mind at least posting DDL+ sample data
+ expected result
CREATE TABLE #Test
(
RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
f INT ,
t INT ,
dt DATETIME NOT NULL
)
INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
GO
INSERT INTO YourTable <column lists>
SELECT f, t , dt FROM #Test
WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
> Sorry for anyone who has seen this query and dataset before but this is a
> seperate question/issue which I am working on.
> I am struggling trying to get my insert statement not to insert record 5
> because the ToURN has been used before in a previous record(1).
> Basically I am trying to write some logic which says that if the ToURN in
> one record already exists in the FromURN field of a previous record then
> do
> not insert the record.
> Does anyone know how I would write the sql to do this.
> RecNO MergeFromURN MergeToURN MergeDateMerged
> 1 100 200 15/06/1982
> 2 200 300 15/06/1982
> 3 300 400 15/06/1982
> 4 500 600 15/06/1982
> 5 700 100 15/06/1982
> 6 100 100 15/06/1982
> 7 NULL 100 15/06/1982
> 8 700 0 15/06/1982
> So far I have the following sql but need to go that step further to stop
> record 5 being inserted because 100 already has been inserted as a from
> urn
> in record 1.
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
> from myTable
> where MergeFromURN is not null and MergeToURN is not null
> and MergeFromURN <> 0 and MergeToURN <> 0 and
> MergeFromURN <> MergeToURN and
> (MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
> (select MoveToURN from Move))
> GROUP BY MergeFromURN, MergeToURN
> Order by MergeFromURN
> while @.@.ROWCOUNT > 0
>
----
--

>
> Can anyone help me with this.
>|||Sorry but this doesn't work as I need.
It inserts 5 rows into the table including the row. 700, 100 (5th Record).
I want to write in further logic which wouldn'd allow this record to be
inserted on the basis that the ToURN value of 100 already exists in a prior
record in the FromURN column.
Its quite hard to explain and I'm sorry for the previous posts which may be
covering the same ground.
"Uri Dimant" wrote:

> Stephen
> You are posted this question a few times some time ago , so people gave yo
u
> solutuion (me include), so would you mind at least posting DDL+ sample dat
a
> + expected result
>
> CREATE TABLE #Test
> (
> RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> f INT ,
> t INT ,
> dt DATETIME NOT NULL
> )
> INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
> GO
> INSERT INTO YourTable <column lists>
> SELECT f, t , dt FROM #Test
> WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
>
> ----
--
>
>
>|||Hi
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
No it does not , look at all columns and check it out
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
> Sorry but this doesn't work as I need.
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
> I want to write in further logic which wouldn'd allow this record to be
> inserted on the basis that the ToURN value of 100 already exists in a
> prior
> record in the FromURN column.
> Its quite hard to explain and I'm sorry for the previous posts which may
> be
> covering the same ground.
> "Uri Dimant" wrote:
>|||Honestly I ran it there now and it inserts 5 rows even though I don;t want i
t
to insert 700,100. Was trying to get around things without using a cursor
but i can't seem to find a way of doing this without using a cursor.
"Uri Dimant" wrote:

> Hi
> No it does not , look at all columns and check it out
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
>
>|||Hi
When I ran it I also go trow 5 in the result set.
This may have something to do with the order in which records are processed.
As in the options on your SQL server setup may be different to the person
who provided the solution, thus when you are running the select to insert ro
w
5 row 1 isn't necessarily in yet?
Just a guess
--
Chan
Programmer
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
select DISTINCT t1.f, t1.t, t1.dt
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo and
t1.f != t2.t
AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
It returns 4 rows
f t dt
100 200 2005-07-27 07:26:33.490
200 300 2005-07-27 07:26:40.897
300 400 2005-07-27 07:26:48.523
500 600 2005-07-27 07:26:54.523
Is this what you are looking for? You should avoid cursors whenever possible
.
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
SKIN that one up and smoke it!!
"frank chang" wrote:
> I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
> select DISTINCT t1.f, t1.t, t1.dt
> from #Test t1
> inner join #Test t2 on t1.RecNo < t2.RecNo and
> t1.f != t2.t
> AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
>
> It returns 4 rows
> f t dt
> 100 200 2005-07-27 07:26:33.490
> 200 300 2005-07-27 07:26:40.897
> 300 400 2005-07-27 07:26:48.523
> 500 600 2005-07-27 07:26:54.523
> Is this what you are looking for? You should avoid cursors whenever possib
le.
>
> "Stephen" wrote:
>|||Stephen, This select statement is the most appropriate one:
INSERT INTO ......
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS -- like MINUS operator in ORACLE, subtract the ones you don't wa
nt
(select *
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo
and t1.f != t2.t AND t2.f != t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
AND t1.RecNo = m.RecNo)
Sorry about that. I didn't drink coffee this morning.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>|||Stephen, The previous SQL statment had a typo in it (I cut and pasted by
mistake)
This statement may help:
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS // subtract set where from-urn number is already used
(select *
from #Test t1
inner join #Test t2 on t1.RecNo > t2.RecNo
and t2.f = t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t1.f)
AND t1.RecNo = m.RecNo)
Thnak you for your help.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

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

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

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

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

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

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

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

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different of For, after, instead of clause?

I just learn how to create a trigger. I had question: what is the different of For, after, instead of clause when we create a trigger? Why we need these? When to use each of these. Thanks in advance.I doubt that anybody on here can explain it more thoroughly than Books Online. Look up Triggers.

Different behaviour in conditional clause (IF against WHERE)

Hi everyone. It's the first time I post here so forgive me if I chose the
wrong group, since this question is about a problem we found out when using
SQL Express 2005.
The problem is that a conditional clause behaves different when it's used
into a IF THEN block than when used in a WHERE clause.
It' something like ( exp1 = "A" or ( exp1 = "B" and bla bla) )
Assume that exp1 is neither "A" nor "B". That 'bla bla' part should never be
evaluated. It behaves as expected when that clause is in an IF (condition)
block, but if the clause appears in a WHERE then it behaves wrongly.
I mean, most of us rely on short-circuit evaluation. Is there something I
missed about SQL Express ?
Thanks in advance
Ignacio Burgueo> Is there something I missed about SQL Express ?
There's something you missed about SQL - conditions are not evaluated in
order, they are evaluated "all at once", or to put another way, all
conditions must evaluate to TRUE for a given record to be returned.
"Ignacio" wrote:

> Hi everyone. It's the first time I post here so forgive me if I chose the
> wrong group, since this question is about a problem we found out when usin
g
> SQL Express 2005.
> The problem is that a conditional clause behaves different when it's used
> into a IF THEN block than when used in a WHERE clause.
> It' something like ( exp1 = "A" or ( exp1 = "B" and bla bla) )
> Assume that exp1 is neither "A" nor "B". That 'bla bla' part should never
be
> evaluated. It behaves as expected when that clause is in an IF (condition)
> block, but if the clause appears in a WHERE then it behaves wrongly.
> I mean, most of us rely on short-circuit evaluation. Is there something I
> missed about SQL Express ?
> Thanks in advance
> Ignacio Burgue?o
>
>|||KH wrote:
> There's something you missed about SQL - conditions are not evaluated
> in order, they are evaluated "all at once", or to put another way, all
> conditions must evaluate to TRUE for a given record to be returned.
>
Ok, I kind of see what you mean. SQL will not necessarily evaluate in the
order I write. Instead it will do the way it thinks it's more optimal.
Take for instance this code:
select * From InstInte0245 A (nolock)
Where ( ( A.theValue = 'ENDED' and ( datediff(second,
FecFin0245, getdate()) > 0 )
)
or A.theValue = 'LOCKED'
or A.theValue = 'TAKED'
)
I've edited some parts of it, but I think you can get the idea of what I'm
trying to achieve. If 'theValue' is different from 'ENDED', you don't need
to calculate the datediff. SQL 2000 gets this right, SQL Express 2005 does
not.
It seems that the optimizer of SQL 2005 thinks wrong in this case. How could
you give hints to the optimizer in this case?
Regards,
Ignacio|||To answer simply: SQL Server does not support short-circuit in SQL
statements so you can not rely on it.
"Ignacio" <ignacio at emuunlim dot com> wrote in message
news:OH##IPD0FHA.596@.TK2MSFTNGP12.phx.gbl...
> KH wrote:
> Ok, I kind of see what you mean. SQL will not necessarily evaluate in the
> order I write. Instead it will do the way it thinks it's more optimal.
> Take for instance this code:
> select * From InstInte0245 A (nolock)
> Where ( ( A.theValue = 'ENDED' and ( datediff(second,
> FecFin0245, getdate()) > 0 )
> )
> or A.theValue = 'LOCKED'
> or A.theValue = 'TAKED'
> )
> I've edited some parts of it, but I think you can get the idea of what I'm
> trying to achieve. If 'theValue' is different from 'ENDED', you don't need
> to calculate the datediff. SQL 2000 gets this right, SQL Express 2005 does
> not.
> It seems that the optimizer of SQL 2005 thinks wrong in this case. How
could
> you give hints to the optimizer in this case?
> Regards,
> Ignacio
>|||David Frommer wrote:
> To answer simply: SQL Server does not support short-circuit in SQL
> statements so you can not rely on it.
>
Oh, thanks. I assumed that SQL behaved the same way as, say, C.
Thanks all for your replies.
Regards,
Ignacio Burgueo|||On Thu, 13 Oct 2005 18:22:12 -0300, "Ignacio" <ignacio at emuunlim dot
com> wrote:

>KH wrote:
>Ok, I kind of see what you mean. SQL will not necessarily evaluate in the
>order I write. Instead it will do the way it thinks it's more optimal.
>Take for instance this code:
>select * From InstInte0245 A (nolock)
> Where ( ( A.theValue = 'ENDED' and ( datediff(second,
>FecFin0245, getdate()) > 0 )
> )
> or A.theValue = 'LOCKED'
> or A.theValue = 'TAKED'
> )
>I've edited some parts of it, but I think you can get the idea of what I'm
>trying to achieve. If 'theValue' is different from 'ENDED', you don't need
>to calculate the datediff. SQL 2000 gets this right, SQL Express 2005 does
>not.
>It seems that the optimizer of SQL 2005 thinks wrong in this case. How coul
d
>you give hints to the optimizer in this case?
Hi Ignacio,
If you want to force an order of evaluation, you'll have to use a CASE
expression. For instance, the following simple example will never result
in division by 0 error:
SELECT a, b
FROM MyTable
WHERE CASE WHEN b > 0 THEN a / b ELSE NULL END > 20
Of course, this is a contrived example, as it's much easier to write
WHERE a / NULLIF(b, 0) > 20
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Differences betwee 2000 and 2005 SQL

Hi,

I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:

ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT

PP_PERIOD_ID,

CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC

The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help

- T.A.

How about this..

Code Snippet

ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT
PP_PERIOD_ID,
CONVERT(VARCHAR, PP_END_DATE, 101) AS PP,
rank() over (order by PP_END_DATE desc) as Rank
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC

|||

This has been answered to your almost identical post in the 'Setup and Upgrade' forum, here.

It is best to not 'multi-post' like this. Post in one forum and wait for a response. That shows respect for the folks here since they don't have to spend time answering a question that was answered in another forum.

|||

Sorry about the multi-post Sankar Reddy answer is what i was looking for.

Thanks

T.A.

|||

You're selecting another work-around that you 'may' have to fix in the future.

It is 'smarter' to use 'best practices' from the beginning, or as soon as you become aware of the need to follow better and more robust methods.

|||Use this forum to learn things to do the right way instead of getting quick answers. Listen to the masters when they give advice to you. I showed you how to get that result and it may not be the right way of doing things when you consider bigger picture. It shouldn't be a big deal to add order by clause to views and I am not sure why it adds more work to you.
|||

Thank you guys,

I had to do a massive change on my application using "select" statements rather than using the view name.

I assume views do not output the order by due to execution cost.

that is the only reasonable conclusion i could think of.

Thanks Again

T.A.