Join data from two tables

[YANKRAY]

CR 10.0

I have the following 3 tables:
Employee_table
Transaction_table
Transaction_table_history

Employee_number is the field that links the 3 tables.

The Transactions are located in both of the Transactions_tables for an Employee in the Employee_table.

Daily Transactions are moved from the Transaction_table to the Transaction_table_history every night.

I am trying to pull Transaction information for an employee from both Transaction tables but need to limit the Transaction_date and Transaction_type fields for my selection.

The Transaction_date is a date range that spans both Transaction_tables and the Transaction_type parameter needs to be set to "Absent-18" (also from both tables).

Example output would look something like:

Employee_Number Transaction_date Transaction_type
000001 04/03/2006 Absent-18
000001 04/06/2006 Absent-18
000002 04/01/2006 Absent-18

This result would be returned because the user selected * All Employees from Employee_table.Employee_number, a date range of 04/01/2006 - 04/06/2006 and the CR select was for the Transaction_type of "Absent-18".

Is there a special join or special selection I should be using?

 

[synapsevampire]

A shame you didn't post your database type, as this is an ideal candidate for a View or advanced SQL.

I would resolve this long term by creating a View on the database of:

select * from Transaction_table
union all
select * from Transaction_table_history

The * should be replaced by the fields you need.

Now you can treat it as a single entity, which simplifies this sort of task.

Within Crystal you might use a Command object, and use similar SQL.

You can attempot to address it within Crystal using standard joins from the employee table to each of the history tables, but you may get inflated results.

I would tackle this permanently using a View. Or I would use a UNION ALL in a command object.

-k

 

[BStatnick]

Software: Crystal Report 8.0.0.371, Crystal Page Server Version 8.0.0.14,
Connecting to Oracle 9.2(?) DB, Users have Windows 2000 Professional or XP operating systems.

We have a client that has requested that we add a field displaying the “ACCTNO" to each line of data on the report shown below:

Original Value Current Value Application Indexer

3210 3037 BkrptVerify b34756
3111 3037 BkrptVerify b34756
3220 3233 BkrptVerify b34756
3037 3043 BkrptVerify b34756


The problem is that the only field available for joining the Objects and Mods tables are the “BATCHNAME” field, and, in order to do this, I need to modify the values of the BATCHNAME field in the Objects (names are fictitious) table in order to retrieve this data.

Linking: Mods -> Index: Equal Join
Mods -> Objects: Equal Join

Table Data:

Objects Mods Index
W76606 w76606 w76606
W76607 w76607 w76607
W76608 w76610 w76610
W76618 w76618 w76618
W76619 w76619 w76619

The SQL statement generated by Crystal for the report is:

SELECT
MODS."FIELDNAME", MODS."ORIGINALVALUE", MODS."CURRENTVALUE", MODS."CREATEDDATE", MODS."CREATEDBY", MODS."CREATEDBYAPP",
INDEX."USERNAME",
OBJECTS."ACCTNO"
FROM
"REPORT"."MODS" MODS,
"REPORT"."INDEX" INDEX,
"REPORT"."OBJECTS" OBJECTS
WHERE
MODS."BATCHNAME" = INDEX."BATCHNAME" AND
MODS."BATCHNAME" = OBJECTS."BATCHNAME" AND
MODS."CREATEDDATE" >= ? AND
MODS."CREATEDDATE" < ?
ORDER BY
MODS."CREATEDBY" ASC,
MODS."FIELDNAME" ASC

Unfortunately, creating a view with a modified field is not an option, nor is it possible to change the process that writes the data to the table.

I have attempted to alter the statement linking the Objects table as follows:

MODS."BATCHNAME" = LOWERCASE({OBJECTS."BATCHNAME"})

But I get the following errors:

ODBC error: [Microsoft][ODBC driver for Oracle]Syntax error or access violation

Followed by:

Error detected by database DLL

I was able to get results by creating an SQL expression field and using this to link to a subreport, but since that requires a separate login for the subreport, we would prefer not to use this solution if at all possible, due to issues with the Page Server.

I presume that my error is that I am not using “LowerCase” correctly, but so far, I have not been able to come with the correct solution. As always, any and all help will be greatly appreciated.

 

[lbass]

In 8.0 you can modify the where clause directly (database->show SQL query). Not sure, but in the where statement you could try using "lcase" instead of "lowercase" or if the letter is irrelevant for the linking, try:

right(MODS."BATCHNAME",5) = right({OBJECTS."BATCHNAME"},5)

-LB

 

[synapsevampire]

As previously posted, use a Command Object as the data source.

-k

 

[lbass]

I didn't realize this before, but these are two separate posters. Yankray is using CR10. I was responding to BStatnick who is using 8.0, where there is no command option.

-LB