Lookup External Values

[svasquez]

Our check hours and earnings table contains 4 "row_nbr"'s with Regular under row_nbr 1, Overtime under row_nbr 2 and misc earnings codes under row_nbr 3 and 4. When I link the earnings translation table to the hours and earnings table, the query returns only the misc earning codes.

Is there a way that I can lookup the translation using an SQL derived field without linking the tables?

 

[CharlesCook]

You can put a select in a SQL Derived field. Here is a rough idea of how to do it. I am sure I am missing a few detail steps but it should get you close.

Start by creating a SIMPLE report that ONLY returns the 1 field you want from the translate table.

Have it return only 1 code at first ('A' for example). We want to make this as simple as possable to start out.

Run the report it should only return 1 row.

Copy the SQL from this report and paste it into a SQL Derived field in your good report.

Run it and see if it works for the one code.

If it works ok change the code ('A') to a data field so it will return what ever code the data field contains.

Let me know how it works out.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

Did this work?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining