Rank/Sort Exceeding Limit

[Former Member]

I am currently dealing with a report that returns over 66,000 rows for a cross-tab report and I am attempting to narrow it down to the top 50 for the row selection, but due to the rows exceeding a certain value the report will not run properly. From what I've read, it seems like I need to deal with a SQL Expression field, but cannot find anything online about them. The help file is just terrible to help with it. Is it possible to do with a SQL expression field so that we can get the top 100 or so pre-ranked on the server side and then when Crystal Reports goes to process the data it will certainly be under the larger value and produce some results?

[Former Member · Accepted solution]

I don't understand how changing the data source is going to do anything? We don't have a stored procedure on the DB for this and I'm not a DBA. It's setup with the basics information wise. Could you provide step by step directions for these queries? For my skill set I only know that you can enter "one query," that being whatever you select through the Select Expert. I'm sorry to be a pain, but I'm still stumbling through this software with all its quirks and such.

Thank you

Reid

[Former Member · Accepted solution]

Hi Reid,

In order to make this work, you need to modify the query.

You probably have pulled all the tables you need from the database,

directly onto the report.

You can try a couple things depending on your skill level.

Go to the Database Expert and click on "Links"

You probably need to modify the "joins". Right click on the link lines to

make the modificatons. Or, you can paste a query into the "Command"

table (Database Expert - "Add Command") and use that.

For instance, I might use a 3rd party software to build the query (TOAD, Visual Studio, etc)

and when I get it just right, I'll just paste it into a "Command" table and build the

report from there. One advantage is that I know exactly what the record count

and data return is before I build the report. That's big.

To see the query that Crystal has built, go to Database / Show SQL

To modify the portion of the query BEFORE the where clause, you use

the SQL Expression

To add to the where clause, you use : Report / Selection Formulas / Record

for example, "table.field > 2"

This will be added to the where clause.

Hope this helps,

The Panda

[Former Member · Accepted solution]

Hi Reid,

As you are new user its a little complex for you.

You can take help of samples.

To download samples click [here|https://boc.sdn.sap.com/crsamples]

With above suggestion I would like to add one more thing first of all Test query in your database whatever you are using Oracle, Sql server,u2026, then use that query in command make sure that it gives out accurate output according to your requirement.

Regards,

Shweta

[Former Member]

I went ahead and added a sql command in Crystal Reports and am receiving an Error referenced in my Description. "Only an ET or null statement is legal after a DDL Statement" The sql that I am adding works just fine in the TeraData SQL Assistant and figured just adding it in as a sql command would work.

database Data_DB;

SELECT INTERACT.AppK,

count(COALESCE(INTERACT.Num, '1'))

FROM INTERACT

WHERE ( INTERACT.ID ='ABCD' AND INTERACT.Res = '000300' )

GROUP BY 1

QUALIFY ( rank () over( order by count(COALESCE(INTERACT.Num, '1')) desc) = 100)

In a previous post on a TeraData forum I saw something referring to the addition of a BEGIN TRANSACTION simple sql END TRANSACTION; DDL;

What am I doing wrong and can this be remedied?

Here is the help I received in the Teradata forum

" I am not familiar with Crystal Reports but I can tell you what the message means.

Your session is running in BTET mode and you have started a transaction (BT;), or Crystal Reports did. Then you/it submitted a DDL statement (database Data_DB). Then you/it submitted the select statement in the same transaction. You need terminate the transaction after the database statement (ET;). All DDL statements have to run as a single transaction. You might want to determine why Crystal Reports is running everything as one transaction. Maybe there is an option to generate each statement as a single transaction. That is what SQL Assistant is doing for you. "

Thank you all for the help thus far. I feel as though I'm right on the crest of the hill but just can't quite get over the hump. It has been so helpful with everything I've received.

[Former Member]

Create the query as a SQL expression field? I don't understand the just create a query because I don't know how or don't think you can just edit the SQL query in Crystal Reports. What I'm looking at are accounts from a monthly view where the total number of distinct monthly accounts are roughly 100,000+. Could you provide some more detail, because I have been using Crystal Reports for a relatively short period of time and with no formal training on it, I only know how to do the basics. Thanks.

[Former Member]

We can update current data source.

Click on:-

Database->Set Datasource Location

select replace with and update data source.

try with Command or stored procedures.

Regards,

Shweta

[Former Member]

Try with this:

To select top records :

1) Create a query (Query1) to get the top three records. We are interested in field called Submit_Date from a table called M_Revisions:

Select Top 3 M_Revisions.Submit_Date from M_Revisions

Order by M_Revisions.Submit_Date;

2) Use Query1 as input to a new query:

Select Top 1 Query1.Submit_Date from Query1

Order by Query1.Submit_Date DESC;

Now you have the 3rd submit date. Note that sorting the Query1 records in descending order makes the 3rd record go to the top.

You can use Select Top 100 retrieve 100 number of records

Hope this helps!!

Regards,

Shweta