How to get rows user defined range?

Hello,
I need a keywork like LIMIT(in oracle) which let me to get rows, I defined.
For exaple I want to show records from 100. record to 200. record!
In oracle I could do like this
Select * from Customer limit 100, 100
I could do a complex query so it let me to get what I want, but I don't
think it has a performance.
Is there a key word like LIMIT in Sql Server?http://www.aspfaq.com/show.asp?id=2120
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
> defined. For exaple I want to show records from 100. record to 200.
> record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||Generate a quota query with a ranking value. You can do all sorts of range
related tricks with such a value. Search the archives of this newsgroup for
some examples.
Anith|||Is LIMIT X,Y part of ANSI-SQL ?
I had a look, but I could only find it as a keyword, not actually defined
what it was for, syntax etc.
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
defined.
> For exaple I want to show records from 100. record to 200. record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||>> Is LIMIT X,Y part of ANSI-SQL ?
No, I think it is a MySQL dialect. Most prominent SQL variants have some
syntax that partially support quota queries.
Anith

How to get next range of Ident values for Merge Replication

You can use sp_adjustpublisheridentityrange on the
publisher.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Is there a method to specify what the next seed value should be?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1bc101c4fd41$e9448560$a301280a@.phx.gbl...
> You can use sp_adjustpublisheridentityrange on the
> publisher.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You could hack into MSrepl_identity_range but I
definitely wouldn't advise this. I know this sounds a
pain, but to avoid any issues like this, I'd consider
reinitializing and setting such a large range that this
can never be an issue in future - either that or manual
range management, but that can be difficult to set up if
you have a lot of subscribers.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to get missing rows?

Hello,
I've got a table in SQL Server 2005 that contains a column of unique id's
that range between something like 1123454 and 2985763. What I need to do is
order by this column (easy to do) and then find the quickest way to loop
through each column and get just the missing numbers. Such as after ordering
my uniqueid the first column would look like:
1345874
1345879
1345883
and so on...
Assuming that this table has about 2 million rows populated out of a
possible 10 million sequential unique id's (but not sequentially populated),
my program needs to check if a number exists and if so, do nothing. If the
number doesn't exist, use that missing number to process code and insert a
row with the missing unique id and data.
I can do all of this, but I'm just wondering what might be the best
(fastest) way to loop through this table and find the missing unique id's. I
t
is a remote database and my program runs locally.
Thanks for any suggestions.First suggestion is to rethink your logic. What do you plan to do with the
"missing" numbers?
Plus, this is a trivial exercise, which makes me question the design again.
loop while @.curr_id < @.max_id
begin
Select @.curr_id = min(id) where id > @.last_id...
if @.curr_id > @.last_id + 1
...
set @.last_id = @.curr_id
etc
"John Riddle" <JohnRiddle@.discussions.microsoft.com> wrote in message
news:432D5D43-43E0-4760-AEDD-531D32921F57@.microsoft.com...
> Hello,
> I've got a table in SQL Server 2005 that contains a column of unique id's
> that range between something like 1123454 and 2985763. What I need to do
> is
> order by this column (easy to do) and then find the quickest way to loop
> through each column and get just the missing numbers. Such as after
> ordering
> my uniqueid the first column would look like:
> 1345874
> 1345879
> 1345883
> and so on...
> Assuming that this table has about 2 million rows populated out of a
> possible 10 million sequential unique id's (but not sequentially
> populated),
> my program needs to check if a number exists and if so, do nothing. If the
> number doesn't exist, use that missing number to process code and insert a
> row with the missing unique id and data.
> I can do all of this, but I'm just wondering what might be the best
> (fastest) way to loop through this table and find the missing unique id's.
> It
> is a remote database and my program runs locally.
> Thanks for any suggestions.|||Here's a sample. You can modify it to your table. The #Numbers table
population SELECT ... INTO statement is courtesy of Steve Kalis:
SELECT TOP 1000000 Num = IDENTITY(INT, 1, 1) INTO #Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
CREATE TABLE #TempA (IDCol INT PRIMARY KEY NOT NULL)
INSERT INTO #TempA (IDCol)
SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 20
UNION SELECT 18
UNION SELECT 11
SELECT *
FROM #TempA ta
RIGHT JOIN #Numbers n
ON ta.IDCol = n.Num
WHERE ta.IDCol IS NULL
DROP TABLE #TempA
DROP TABLE #Numbers
"John Riddle" <JohnRiddle@.discussions.microsoft.com> wrote in message
news:432D5D43-43E0-4760-AEDD-531D32921F57@.microsoft.com...
> Hello,
> I've got a table in SQL Server 2005 that contains a column of unique id's
> that range between something like 1123454 and 2985763. What I need to do
> is
> order by this column (easy to do) and then find the quickest way to loop
> through each column and get just the missing numbers. Such as after
> ordering
> my uniqueid the first column would look like:
> 1345874
> 1345879
> 1345883
> and so on...
> Assuming that this table has about 2 million rows populated out of a
> possible 10 million sequential unique id's (but not sequentially
> populated),
> my program needs to check if a number exists and if so, do nothing. If the
> number doesn't exist, use that missing number to process code and insert a
> row with the missing unique id and data.
> I can do all of this, but I'm just wondering what might be the best
> (fastest) way to loop through this table and find the missing unique id's.
> It
> is a remote database and my program runs locally.
> Thanks for any suggestions.|||google up
"Islands and Gaps in Sequential Numbers"
by Alexander Kozak|||Yes, I can think of several ways to do it as well. First being a cursor
movement. However, I was curious as to what you database guys thought would
be the FASTEST method.
To let you know, my program uses the unique id field to check a website
which uses the field in the url. I have been manually incrementing the id in
my program and building the url. About half of the urls work (actually have
a
page associated with them). The others will be used at some unkown time in
the future until all are used up. I have no way of knowing after I've alread
y
travelled through a million or so id's which ones have since been used for m
e
to travel back through.
Since the utility takes time to set a get request to the webserver and wait
for a response to find out if there is new content, going through the same
million rows again is very time consuming just to find the new id's that now
have content. I wanted to skip straight through those id's already processed
by the utility and just check the unused id's. This would speed up the
re-checking of a block of id's considerably. Make sense?
I had initially thought of using a cursor to compare the table id against a
int variable that is incremented by one each time and processing if the
cursor id <> int. However, a friend had said that cursor operations on
millions of rows are slow. I thought there might be some sql method to
restrict by unused id's and simply loop straight trhough a set of
known-to-unused id's.
Thought I'd post it to the group for general ideas. I can easily implement
the concept of one of you guys knows that such and such method would be the
fastest.
Thanks.
"Jeff Dillon" wrote:

> First suggestion is to rethink your logic. What do you plan to do with the
> "missing" numbers?
> Plus, this is a trivial exercise, which makes me question the design again
.
> loop while @.curr_id < @.max_id
> begin
> Select @.curr_id = min(id) where id > @.last_id...
> if @.curr_id > @.last_id + 1
> ...
> set @.last_id = @.curr_id
> etc
>
> "John Riddle" <JohnRiddle@.discussions.microsoft.com> wrote in message
> news:432D5D43-43E0-4760-AEDD-531D32921F57@.microsoft.com...
>
>|||That's not the problem. I could think of at least two ways of doing. But
since this will be performed on millions of number, I was just looking for
suggestions as to what would be the highest performing approach, not a code
sample.
However, Mike's response was very good and I think I'll be using that
approach.
Thanks.
"Alexander Kuznetsov" wrote:

> google up
> "Islands and Gaps in Sequential Numbers"
> by Alexander Kozak
>|||You have a separate web page for each id? Wow. Have you considered a single
page with appropriate logic? Primary keys should never be used like this.
What are you trying to do? And why try to "fill in the gaps". Just use the
next one?
Options:
* Use a GUID
* Use an Identity column
* Use a table that stores the last number..then use Select @.NewID =
Max(LastID) + 1
"John Riddle" <JohnRiddle@.discussions.microsoft.com> wrote in message
news:229308E3-4EDF-4BB2-AD39-20CFC472AB45@.microsoft.com...
> Yes, I can think of several ways to do it as well. First being a cursor
> movement. However, I was curious as to what you database guys thought
> would
> be the FASTEST method.
> To let you know, my program uses the unique id field to check a website
> which uses the field in the url. I have been manually incrementing the id
> in
> my program and building the url. About half of the urls work (actually
> have a
> page associated with them). The others will be used at some unkown time in
> the future until all are used up. I have no way of knowing after I've
> already
> travelled through a million or so id's which ones have since been used for
> me
> to travel back through.
> Since the utility takes time to set a get request to the webserver and
> wait
> for a response to find out if there is new content, going through the same
> million rows again is very time consuming just to find the new id's that
> now
> have content. I wanted to skip straight through those id's already
> processed
> by the utility and just check the unused id's. This would speed up the
> re-checking of a block of id's considerably. Make sense?
> I had initially thought of using a cursor to compare the table id against
> a
> int variable that is incremented by one each time and processing if the
> cursor id <> int. However, a friend had said that cursor operations on
> millions of rows are slow. I thought there might be some sql method to
> restrict by unused id's and simply loop straight trhough a set of
> known-to-unused id's.
> Thought I'd post it to the group for general ideas. I can easily
> implement
> the concept of one of you guys knows that such and such method would be
> the
> fastest.
> Thanks.
> "Jeff Dillon" wrote:
>|||Its not my web page. Its an outside web page that I'm populating my data bas
e
with data from. I'm parsing the page and populating the database.
As I described before, my utility navigates to the page. In the url of the
page is an "id". The website that I'm getting the data from does "just fill
in the gaps" from time to time and hence I need to re-traverse all the
possible id's to find out which ones are now being used and populating my
database with the additional info recently posted.
Since I don't want to re-traverse id's that I've already got data for, I
need a fast way to only go to the id's that I don't yet have data for. So I
need to "restrict" my table by id's that are <not> in the table yet. I want
my utility to re-traverse a set of id's about 10million long and fill in the
gaps of missing data with id's that were not yet used at the time of the
first traversal but are now being used by the site and have data associated
with them. About 80% are used in my database, but nearly 100% are now being
used in that same id block on the remote site (that I have no control over).
Now do you understand? Its not a design issue. I can only get information as
it becomes available and associated with a certain id. The outside website
seems to have no rhyme nor reason in how they assign id's to results tables,
so I'm left with having to constantly re-check urls that had no data in them
at the last traversal.
"Jeff Dillon" wrote:

> You have a separate web page for each id? Wow. Have you considered a singl
e
> page with appropriate logic? Primary keys should never be used like this.
> What are you trying to do? And why try to "fill in the gaps". Just use the
> next one?
> Options:
> * Use a GUID
> * Use an Identity column
> * Use a table that stores the last number..then use Select @.NewID =
> Max(LastID) + 1
>
> "John Riddle" <JohnRiddle@.discussions.microsoft.com> wrote in message
> news:229308E3-4EDF-4BB2-AD39-20CFC472AB45@.microsoft.com...
>
>|||
This message is for Alexander Kozak. I am trying to reach Alexey
Ostrovsky, who I believe you know. Alexey has been out of contact for
over a month and I am worried about him. If you are the correct
Alexander Kozak, please contact me at bobemail1s-alexey@.yahoo.com
(displosable email in case of spam).
My apologies for posting to the group off subject. But I do not have
Alexander's direct email.
Thanks,
Bob Flanagan
*** Sent via Developersdex http://www.examnotes.net ***|||Bob,
Alex Kozak has recently published an article "Powerful, Flexible
Text-Formatting Solutions in SQL Server " on devx.com. There is an
"E-Mail the author" button on page 3.

How to get MDX query that BI Studio generates in the Cube''s Browser

Hi,

I have been trying to write an MDX query which restricts the data based on a date range on the time dimension.

I am able to frame this query on the GUI interface of the Business Intelligence Studio (Using the cube browser) But I want the actual MDX for it.

To be specific, I am trying to view how the "Range (Inclusive") operator is implemented in the "Browser" Tab of the Cube Editor in the Business Intelligence Studio.

I have a Time dimension which has Year, Quarter, Month, Day Hierachy and I need to form the query such that I have a couple of pre-defined CALCULATED measures on the Column axis and Another dimension on the Row axis. I want to limit the records to a Range (Say Jan 1, 2007 to Aug 5, 2007).

My MDX works when it is for a single value of the Time Dimension.But for a range, I get nulls in the calculated measures.

My MDX is :

WITH

MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '

AGGREGATE({{[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.lastChild.lastChild}

+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.nextMember:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.prevMember}

+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.firstChild.firstChild:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}})

', SOLVE_ORDER = 5001, SCOPE_ISOLATION=CUBE

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]

=====================================

Where the calculated MEASURE.Agency is pre-defined as:

CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS

'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),

IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,

"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;

Any help is greatly appreciated.

Regards,

Mehernosh

I'm not entirely sure, but if I read your statement correctly all you are trying to achieve is to get the aggregate of the dates from Jan 1 to Aug 5. In which case you should be able to do the following.

SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,

{[Resource-By Pool].[Resource Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}

And if you are after a YTD amount it could be expressed even more concisely as:

SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,

{[Resource-By Pool].[Resource Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {YTD([Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5])}

I'm not sure why you are using a measure for the agency name and not just crossjoining in the Agency dimension:

SELECT {[Measures].[Available Hours] } ON AXIS(0) ,

NON EMPTY {[Resource-By Pool].[Resource Name].members} *

{Agency-By Pool].[Agency Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}

There are a couple of issues with your calculated measure. This statement "NOT ([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL)" will always return true as the IS operator does a member comparison not a value comparison and CurrentMember will always return a valid member. I think what you probably meant was "NOT IsEmpty(Resource-By Pool].[Resource-By Pool].CurrentMember)". But I would have thought including the Agency dimension in the row axis would have done a similar thing (unless agency &[0] has some other label)

|||

Hello Darren,

Thank you for your response. You are correct that I want the aggregate of the Hours for a Resource, but also want the value of the Agency dimension to be displayed along, BUT as a measure.

The MDX you provided, (with the cross join on Row Axis), certainly works. But alas I need the AGENCY to be a measure on the Column axis.

The reason is that our enterprise application has a customizable Portfolio management component (that uses Analysis Server) wherein Users can add/remove a set of predefined measures to their view. Very much like how the Cube browser does, but with limited functionality such that only predefined measures can be dropped on to the Column axis. And the ROWS are limited by Time dimension. The application generates the MDX based on the set of measures provided. Hence we need each measure to be declared separately as 'CREATE MEMBER CURRENTCUBE.Measures'.

Also the switch to using "NOT IsEmpty" instead of "NOT null" check in the definition of the Agency measure did not make any difference. The measure value still returns as null for each row.

The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue.

But I see that when I do a similar thing with the "Cube Browser", we get the correct result. (Agency is declared as a calculated measure on the cube)

Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded

Any help will be highly appreciated as the porting of the application to AS2005 has stalled because of this issue

Regards,

Mehernosh

|||

Mehernosh Vadiwala wrote:

The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue.

You did not mention that this used to run under AS2000, on double checking the calc I noticed that you are referencing the [Resource-By Pool].[Resource-By Pool] attribute hierarchy. At the attribute hierarchy level there is usually a default "All" member so the count will always be 1. If you add an extra [Resouce-By Pool] reference to specify just the level in the attribute hierarchy with the actual members.

CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS

'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),

IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,

"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;

Mehernosh Vadiwala wrote:

Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded

Yes, the traffic is encoded, but you can use SQL Profiler to connect to SSAS in 2005 and see the begin and end query events which have most of the MDX, some of the cube browser controls create named sets within the session, so you might need to assemble the MDX from a couple of trace statements.

|||

Darren,

This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember )

But that was the only change I made to the Query.

Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member.

======= Not Working =================================

WITH

MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '

AGGREGATE({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})

', SOLVE_ORDER = 5000, SCOPE_ISOLATION=CUBE

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]

======= WORKING =================================

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE ({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})

=================================================

Any insight on why this works and not the other ?

Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information.

Could this be the reason? Is there some documentation on this ?

Mehernosh

|||

Mehernosh Vadiwala wrote:

This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember )

Sorry, you're right the formula has already been adjusted correctly.

Mehernosh Vadiwala wrote:

Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member.

Yeah, that was what I suggested in my first response.

Mehernosh Vadiwala wrote:

Any insight on why this works and not the other ?

The only reason I could think that this might have issues would be if you [available hours] measure is calculated and relies on having a [Time-by Calendar] context. A cacluated aggregate in the query will not supply such a context and the formula would calculate as if it were at the all level. The Aggregate function is meant to calculate early in the solve order, but your setting of the solve order may be overriding this.

Mehernosh Vadiwala wrote:

Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information.

I don't use LookupCube any more I use multiple measure groups, possibly linking them in if they are shared across more than one cube.