Table linking problem

[pandpp]

Hi,

I have a problem linking 2 tables to a third. The problem currently exists when linking this via CR directly.

I am using CR7 (can't upgrade), SQL Server 7.

I overcame the problem originally by using a SP, only, I have since found out the app the report is viewed through, will NOT accept any foreign tables, views, SP's, etc.


I have 3 tables, 2 of which link happily, via an AccountNo.

The third table links with one of these tables, but, not directly. Since the fields that link are of differing lengths, CR won't allow a standard link.

Possibly the easiest way to describe what I need is to list the SQL code I used in my SP:

SELECT
*

FROM
(Contact1
INNER JOIN ContHist ON
Contact1.AccountNo = ContHist.AccountNo)
INNER JOIN Lookup ON
Lookup.FieldName = 'MAPPTACTCDV'

WHERE
Lookup.Entry LIKE ContHist.ActvCode + '%'

Now, I can get Contact1 & ContHist to behave perfectly, via standard linking.

I even managed to figure out to HIDE the Lookup table. This in itself may be my problem.

The problem I have though, is when I set the Record Selection to either:
{LOOKUP.ENTRY} Like {CONTHIST.ACTVCODE} + '*'
OR
{LOOKUP.FIELDNAME} = 'MAPPTACTCDV'
I am returned zero records, which is NOT correct. I need both of these included, somehow.

Any suggestions at this point are MOST welcome.

Regards,

Peter.

 

[Stoffel24]

Hi Peter

Is it not possible to have some code running which actaully creates a TABLE with all the data you need (as opposed to a view or sp)? Eg some VB code which creates a table and populates it with the data you need.

 

[pandpp]

It might be, but, the 3rd party app the report is being called from, checks the RPT file, for data sources & calls any source that it doesn't know, an 'unsupported' table & crashes...

That's why the SP option failed so miserably in the first place.