Duplicate table error

[cjany]

I have a strange thing going on with my Crystal 9 report. I have a very simple report with subreport. On my main report, I filter for a case number in the record selection just for testing the report and so I won't have to wait every time for my data to pull. Eventually I will have a parameter from my SubReport. As long as I have that record selection, my report works fine, and the SQL looks great, but when I remove the record selection and refresh the report, I get an error that states: "Failed to open a rowset", then it states: "Not supported-Details Error -223uplicate table name in the FROM clause" When I check the SQL, one of the tables is duplicated. When I put the record selection back in, the report works. I'm only trying to remove the record selection. Please help.

 

[synapsevampire]

ACK!!!...that is weird...

Go into the Database->Database Expert and make sure that the table is only in there once, then try changing the record selection in Design mode and don't refresh the data.

Then try a Database->Verify Database.

Just guessing here, obviously Crystal shouldn't be doing this.

Anyway, I'd save it off under another name and try various things, including the Database->Set Location if need be.

-k

 

[cjany]

Yes, I've tried all sorts of stuff, even removing the table and adding back in. Then is states a different table is duplicated. Right now, I'm reacreating it and I'll see what happens. This report should work!!!

Cheryl

 

[Turkbear]

Hi,
What database and connectivity is involved?
Could there be a view with the same name as the table and the Query is 'getting confused'?

Can you post the Sql generated both with and without the selection criteria?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cjany]

I'm am pulling from 2 databases which I've done before but not these 2 in particular. One is an Informix connection, the other is ISeries for AS400.

Here is the SQL with the record selection which works:

SELECT
fcs_evnt_ldgr.cs_id, fcs_evnt_ldgr.evnt_dt, fcb_evnt_srvc.evnt_srvc_cd, fcb_evnt_srvc.desc_evnt_cd, fcb_evnt_srvc.fee, fcs_cs_mstr.cs_num_txt, fcs_cs_mstr.alt_cs_ref_num_2
FROM
distprod@pecanifmxsoc1:informix.fcs_evnt_ldgr fcs_evnt_ldgr,
distprod@pecanifmxsoc1:informix.fcb_evnt_srvc fcb_evnt_srvc,
OUTER distprod@pecanifmxsoc1:informix.fcs_cs_mstr fcs_cs_mstr
WHERE
fcs_evnt_ldgr.evnt_srvc_cd = fcb_evnt_srvc.evnt_srvc_cd AND
fcs_evnt_ldgr.cs_id = fcs_cs_mstr.cs_id AND
fcs_evnt_ldgr.cs_id = 108941
ORDER BY
fcs_cs_mstr.alt_cs_ref_num_2 ASC,
fcs_cs_mstr.cs_num_txt ASC

********************************

Here is the SQL without the record selection that does not work:

SELECT
fcb_evnt_srvc.evnt_srvc_cd, fcb_evnt_srvc.desc_evnt_cd, fcb_evnt_srvc.fee, fcs_cs_mstr.cs_num_txt, fcs_cs_mstr.alt_cs_ref_num_2, fcs_evnt_ldgr.evnt_dt
FROM
distprod@pecanifmxsoc1:informix.fcb_evnt_srvc fcb_evnt_srvc,
distprod@pecanifmxsoc1:informix.fcb_evnt_srvc fcb_evnt_srvc,
OUTER distprod@pecanifmxsoc1:informix.fcs_cs_mstr fcs_cs_mstr
WHERE
fcs_evnt_ldgr.evnt_srvc_cd = fcb_evnt_srvc.evnt_srvc_cd AND
fcs_evnt_ldgr.cs_id = fcs_cs_mstr.cs_id
ORDER BY
fcs_cs_mstr.alt_cs_ref_num_2 ASC,
fcs_cs_mstr.cs_num_txt ASC

********************
HHMM, now that I have both SQL's side by side, there seems to be other differences. I'll print this and compare, but if you see something right away, please reply.

Thank you for your help

 

[synapsevampire]

Bizarre indeed.

Unless the distprod@pecanifmxsoc1:informix.fcb_evnt_srvc fcb_evnt_srvc, is also a View or Synonym I can't understand this.

In general I suggest not using 2 different data sources within crystal, instead create a MS Access database and link )not import) the data sources in, then create a MS Access query as the data source for the report.

-k

 

[rosemaryl]

Dear cjany,

What happens if you change the alias of the existing table in Crystal to something very short? Does it solve the problem?

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762