We help IT Professionals succeed at work.

Crystal reports XI use table from two databases

TomBalla
TomBalla asked
on
I have two databases with some common fields such as stockcode and available quantity.  So I basically want to take 1 table from both databases and compare results.  Is this possible?

The only fields I would look at are stockcode, quantity, and date.
Comment
Watch Question

If you are connecting directly to the databases and not via a Business oBjects interface then you can select both tables.

Author

Commented:
Ok, one problem.  In one table the driver is 103 but in the other table it is 03.  How would I go about saying 03 is equivalent to 103?
You will have to use a sql command to get the data from one of the tables, so that you can create an 'adjusted' field value for use i linking the tables.

For example

Select * , "1" & Driver as AdjDriver from the03table

Author

Commented:
Ok, I will try that out.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you trying to link the tables in the SQL?

mlmcc

Author

Commented:
I am using SQL, OLE(DB) to get the two databases into database expert.  Then I just attach the two tables needed by common fields.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is it a 1 to 1 relationship in the 2 databases?

mlmcc

Author

Commented:
I don't know much about the sql part of making reports.  The two tables match on stockcodes.  The other field I mentioned is the warehouse field, where in one table a warehouse is '03' and in the other table the same warehouse is '103', a 1 is added to the front of the warehouses in that table.

I need these two fields to coexist and I think the report will work fine.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I think you are going to have to use a subreport for one of the tables.

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you create a linked table or a view in one of the databases that joins the tables?

mlmcc

Author

Commented:
I was able to link the tables through the stockcode fields and data showed.  I need to get Table A warehouse '03' to be equivalent to Table B warehouse '103'.  

Kind of a If Table A = '03 then add a 1 to the front and compare to table B.  I think I need to make a view but I have never made one.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Get with the DBA and explain what you need.

It will be similar to

SELECT '1' & Table03.WareHouse as Table03Warehouse, Table03.StockCode, Table03.Quantity, Table03.DateField, Table103.Quantity, Table103.Datefield
FROM Table03 INNER JOIN Table103 ON Table03Warehouse = Table103.Warehouse AND Table03.StockCode = Table103.StockCode

mlmcc

Author

Commented:
I don't have DBA, we are pretty much a 1 person IT squad.

Ok it should look something like this

SELECT '1' & InvWarehouse.Warehouse as Table03Warehouse, InvWarehouse.Stockcode, InvWarehouse.QtyOnHand,  , BTWarehouseHistory.Datetime
FROM InvWarehouse INNER JOIN BTWarehouseHistory ON InvWarehouse = BTWarehouseHistory.WarehouseNo AND InvWarehouse.Stockcode = BTWarehouseHistory.ItemNo

InvWarehouse has no date field it is always the current date.
Do I create a view by going to 'Add command' and putting it in there?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
This has to be done in the database.  A COMMAND can only use 1 database.

mlmcc

Author

Commented:
Ok, thanks for the help.