Crystal Reports Freezing

[Paul12345671]

Hello,
I'm writing a sub-select in Crystal Reports against an Oracle database. I write my first LEFT JOIN, then click the check syntax button and it checks fine. I add my second sub-select, check the syntax and it freezes. It doesn't matter how many JOINS I have, as soon as I LEFT JOIN this particular table, it freezes on the check. If I click Save, it still freezes (I suppose that it still does the check). I don't type the field names in, I double click them from the menu. Has anyone had similar troubles? I have tried the following:

-Reinstalling CR on a different PC
-Switching Oracle Drivers (& updating the CRCONFIG file)
-Copying the sql sub-select to sqldeveloper & running it - it worked fine there
-Starting a new report from scratch

None of these fixed the problem. I was wondering whether anyone had any ideas?

Thank you.

 

[Madawc]

I don't use Oracle so I've no idea what the problem is. But if no one else can offer any help, you could try using a subreport for the two selections, since they aren't compatible.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[dgillz]

Is your second subselect from the first subselect, or from the main select statement?

Crystal does not support a subselect of a subselect to the best of my knowledge.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Where are you writing the subselect? What version of CR are you using?

-LB

 

[Paul12345671]

I'm using CR XI, and I'm writing the sub-select by:
- looking at field explorer
- right-clicking 'SQL Expression Fields'
- choosing New
- writing the code.

 

[lbass]

A SQL expression can only return a single value per group or report, so you might try creating the subselect in a command instead. Go to database->database expert->add command and enter the SQL query there. Note that links to the command will occur locally and slow the report so generally you are better off incorporating a subquery as one "field" in a command that acts as your entire datasource.

-LB

 

[Paul12345671]

Thanks, LB. However, the subselect does return a single value (at least it should - it's a select count (*) from <tablename> where...). It freezes when I add additional JOINs which I need to further restrict the data that it counts. Otherwise it counts all the records in the table rather than only the records that relate to that particular customer.

Nevertheless, thanks for your help. I've made a change & used a sub-report (I'd prefer to avoid sub reports if possible). I'll also look at the Add Command. I've never used it, but it sounds very interesting.

Paul.

 

[lbass]

You didn't literally use the wildcard in the SQL expression, did you? If you post the SQL expression you tried, maybe I or someone else can troubleshoot it.

-LB

 

[Paul12345671]

The code is simply counting events, but I want to link it to the arrangement table via the eventarrangementrshp, otherwise it simply prints the total number of events in the event table.

Paul.

(select max("EVENT"."DATETO") from ((event
LEFT JOIN eventarrangementrshp ON "EVENTARRANGEMENTRSHP"."EVENTCD"="EVENT"."EVENTCD")
LEFT JOIN arrangement ON "EVENTARRANGEMENTRSHP"."ARRANGEMENTCD"="ARRANGEMENT"."ARRANGEMENTCD")
WHERE "EVENT"."EVENTTYPECD" in (1,2,38,26,27,28,31))

 

[lbass]

Well, you are showing left joins which would NOT restrict the data, and which don't work in a SQL expression. Is it that you want the max per some group? What group? If your data has multiple groups, please explain at what level you want the max. This is doable in a SQL expression.

-LB