LIMIT in SQL

[ordotan]

Hi,

Is there a way to tell Cognos use the "LIMIT" (used in many DB vendores liks Vertica /Greenplum/ ect.) function for limiting the query to return only a set of the results?

For example, the SQL for showing only 10 records, will be something like|:

select ..
from ..
where ..
limit 10

I don't want to use the "Running-Count" solution, as it's less effective.

[BigChris]

I don't think that's what Cognos is all about to be honest. If that's what you want to do you can create an SQL query within Cognos and use that, but you'll need to make sure that your users are aware that the data will be limitted.

[ordotan]

I want to use this filter in a model-based report.. so I can't control it directly from the SQL.

When I worked with Oracle, I had used the following filter from within the Query  : "{rownum}<10"

But because Vertica doesn't support "rownum", I need other solution.

I had tried "{limit} 10", but it isn't working

[hittony]

I want to use this filter in a model-based report.. so I can't control it directly from the SQL.

When I worked with Oracle, I had used the following filter from within the Query  : "{rownum}<10"

But because Vertica doesn't support "rownum", I need other solution.

I had tried "{limit} 10", but it isn't working

You can limit the number of the rows retrieved in RS.

[blom0344]

If you think this through, it will make less and less sense. Without explicit ordering the user will not have any control about the data retrieved. Setting the limit within RS will give you an error message. The set returned SHOULD be controlled by setting filters (prompts) to ensure that a limited set is returned. A random set will not do..

[hittony]

Hi,

Is there a way to tell Cognos use the "LIMIT" (used in many DB vendores liks Vertica /Greenplum/ ect.) function for limiting the query to return only a set of the results?

For example, the SQL for showing only 10 records, will be something like|:

select ..
from ..
where ..
limit 10

I don't want to use the "Running-Count" solution, as it's less effective.

ordotan, you have a professer here, you SHOULD think what he/her think true, otherwise what you do will have no sense  ;D

[ordotan]

hittony - When you set the no. of rows retrieved in RS, it won't have any effect the query being generted .. It is only a matter of dispaly.

blom0344 - In my case, it makes sense, because I have huge amount of data (millions of records), and I want to give the user just a quick sample of the data.

Again, It was working fine wite rownum and Oracle.. so I'm looking for similar solution to Vertica's syntax..

[yoniw]

The report is used as a "sensor". the number of rows can be more than million rows sometimes...
I join Or's question.

[bdbits]

If you're sending the database a query that will return millions of rows, you're doing it wrong.

[Lynn]

If you're sending the database a query that will return millions of rows, you're doing it wrong.

Yup. I agree. It does not sound like you are using the right tool for the job. What will the users do with this sample set of rows? What decisions or actions are you intending to support?

[ordotan]

I understand why you think it doens't make sense...but in our business, it makes sense to use the sample to "get a sense" of the nature of the data in cases where you can't provide a "strong" filter

[Francis aka khayman]

1. crosstab or list: rows per page property

2. from query you can also add a rownumber and use a summary filter rownumber < x

3. SQL source for your query

[CognosPaul]

A slightly different way of looking at the problem. Or's company handles telecoms data, which is its own huge can of trouble.

If the data is supposed to be representative, why not create a field in the table that you can filter on. Have the tool that loads the data default it to 0, but set that value to 1 every (time range of table in seconds / 10) seconds.

What's the range of the detail table? A day? An hour? If it's a day, have the loader set the field to 1 every 8,640 seconds. Even if there are more than 10 rows left (depending on how often you drop stale data), filtering on a running-total will be far faster.