Crystal Reports Error

Database

Crystal Reports Error

I am trying to add a sql command in Crystal Reports and am receiving the 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)

I am trying to get some records pre-ranked before Crystal Reports processes the records. I have run into a situation where I have more rows than allowed in Crystal Reports. I received some messages on a CR forum stating what I needed to do and now I am receiving this error. In a previous post 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?

Thanks for the help.

Reid
1 REPLY
rgs
Enthusiast

Re: Crystal Reports Error

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.