joins not showing up in SQL

[JJBorn2]

Hi,

I have a report in CR11 that I had to rework a little because of getting a new computer and configurations being different, etc.

One table has changed, and the changes show up when I use 'Verify Database'. I map the changes and that part's OK.

The table that has changed links to 12 other tables. When I run the report, I get too many records. It's behaving as if the joins aren't there. In Database expert they are all there, however, when I 'View SQL', they are all gone. This is why I still like to have 8.5 around, so I can alter the SQL manually!

Any ideas on how to fix this?

Thanks

 

[lbass]

In the database expert, go into the link options and check "enforce both" and see if that makes a difference. If you aren't actually referencing fields in the linked tables, I don't think the links would show up in the SQL unless you had enforced them.

-LB

 

[JJBorn2]

Thanks, but that didn't help.

 

[lbass]

That's not much information. Are you saying the tables still don't show in the SQL? Please paste the SQL into the thread.

Enforcing the joins should have resulted in the joins appearing in the SQL. However, try adding a field from each of the twelve tables as a test. Add them to the report header. I would also check the record selection formula.

-LB

 

[JJBorn2]

I can't paste it, I would have to re-type it (long story - separate networks, etc.) The gist is this:
Select main.field1,main.field2, main.field3
from
main inner join vendors on main.id = vendors.id,
main inner join serialnos on main.id = serialnos.id
and so on

I now have 3 versions I am wrestling with. The original, which had inner joins not enforced, and worked fine until I moved. Now I get an error about an invalid identifier. The field it is complaining about is from a table I USED to use, and this is after I verify DB and re-map to my new view. It's like this field name is stuck in the SQL. If I hit Refresh in the SQL, I lose all the joins.

The second version, I tried enforcing the inner joins, and that worked OK for about half the tables, then when I add any that have an alias(they aren't the same tables, but tables in another schema with the same name), I get 0 records.

The third version , I tried left outer joins and enforced them, and I get too many records.

One thing that might be causing trouble, is that main, which is new, is a view, and it used to be a table. Hmm, I'll have to think about that...

Thanks

 

[lbass]

Sounds like the old field name is being used somewhere in the report--that somehow the mapping isn't working exactly. Try removing the old field name and inserting the new field (I guess this is obvious).

I don't think the use of a view in and of itself should create the problem you are seeing. Have you considered rewriting the report and using a command where you can write the query yourself?

-LB

 

[JJBorn2]

I can't find that field anywhere on the report, the section experts, I even opened the report explorer. I swear, it's nowhere but in the SQL statement.

I think I will have to use the command, as you suggested. I just didn't want to have to start over. But, at this point, it should be faster.

Thanks again.

 

[lbass]

There are a couple of other places I would check--report->sort records (I often find those fields there) or in field formatting formula areas.

You could export the report to "report definition" and then do a search on the text, too.

-LB