Link data from from multi database multi table

[tuigvlieg]

Hi,

I am using Crystal reports version 8.5.

Examble situation:
==============
Database I
Order table
SKU: A, C, D

Receipt table:B
SKU: B, D
===============
Database II
Order table:
SKU: A,B

Receipt table:
SKU: D
===============

I need to create a report that shows only SKU's that are available on one of the two databases in one of the tables. This output need to be printed on one report showing each SKU (if available) only once.

Who can help me in the good direction?

 

[Turkbear]

Hi,
One way would be to use a Command Object that UNIONS the 2
Order tables - Link that to the Receipts table
( assuming you need something from that table, you do not mention what output you need -
This may be database dependent, so in future, please provide info as to what database and how you are connecting. Sample output ( what you want to show) is always useful as well..)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tuigvlieg]

HI Turkbear,

Thanks for you reply. It sounds that the unions is a good solution. However I am not familiar with this options. Can you help me with this statement?

The output I am looking for is a distinct overview of SKU's that have been shipped or received the last six weeks. So the I have selection criteria as well. Of these sku's I need to add the weights sizes etc coming from the SKU table. I am struckling with getting all data from 4 tables together and make a distinct overview out of it.

I can also use 2 tables that are similar however also on two different databases. I rather not use these tables as these are extremely big.

But I think when I know the union statement I will make big progress.

 

[synapsevampire]

Try reading his post:

This may be database dependent, so in future, please provide info as to what database and how you are connecting. Sample output ( what you want to show) is always useful as well..)

A Union is database dependent, so please take the time to post basic technical information, especially after someone states that it's needed.

-k

 

[tuigvlieg]

Hi,

I am sorry but we are using Oracle database version 8.0. I managed to have a union. Seems to be very easy to create. Thanks as I was not familiar with this option.

However I am still struckling to have the SKU table linked that should be linked based on the SKU field. ITRN.SKU = SKU.SKU. I need some fields of the SKU table on the report. Beside this I need a distinct report based on the SKU field. This seems to work, but only one time. When I run the report the first time it works fine. When I run it the second time it won't work. I created the distinct SKU via subtotals: Distinct count of SKU.

SQL query I have so far is:

SELECT
ITRN."ITRNKEY", ITRN."TRANTYPE", ITRN."SKU", ITRN."EDITDATE",
SKU."STDGROSSWGT", SKU."STDNETWGT"
FROM
"WH1"."ITRN" ITRN,
"WH1"."SKU" SKU union
Select SKU."STDGROSSWGT", SKU."STDNETWGT", ITRN."ITRNKEY", ITRN."TRANTYPE", ITRN."SKU", ITRN."EDITDATE" from "WH2"."ITRN" ITRN "WH2"."SKU" SKU
WHERE
ITRN."SKU" = SKU."SKU" AND
ITRN."EDITDATE" >= {ts '2006-06-02 00:00:00.00'} AND
(ITRN."TRANTYPE" = 'DP' OR
ITRN."TRANTYPE" = 'WD')

 

[tuigvlieg]

I managed to to make big progress, I still have one problem left.

It seems that the ODBC connection does not accept a distinct count based on the query below.

Does someone has a clue how I can get a distinct selection on the SKU? If I use the select distinct I will end op with 2 records for the same SKU.

Query used:
SELECT
ITRN."ITRNKEY", ITRN."TRANTYPE", ITRN."SKU", ITRN."EDITDATE",
SKU."SKU", SKU."STDGROSSWGT"
FROM
"WH1"."ITRN" ITRN,
"WH1"."SKU" SKU
WHERE
ITRN."SKU" = SKU."SKU" AND
ITRN."EDITDATE" >= {ts '2006-06-02 00:00:00.00'} AND
(ITRN."TRANTYPE" = 'DP' OR
ITRN."TRANTYPE" = 'WD')

union

SELECT
ITRN."ITRNKEY", ITRN."TRANTYPE", ITRN."SKU", ITRN."EDITDATE",
SKU."SKU", SKU."STDGROSSWGT"

FROM
"WH2"."ITRN" ITRN,
"WH1"."SKU" SKU

WHERE
ITRN."SKU" = SKU."SKU" AND
ITRN."EDITDATE" >= {ts '2006-06-02 00:00:00.00'AND
(ITRN."TRANTYPE" = 'DP' OR
ITRN."TRANTYPE" = 'WD')

 

[Turkbear]

Hi,
The Distinct Count can ( and in this case probably must) be done at the report level..Use the Insert..Summary..Distinct Count function to do that.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tuigvlieg]

Hi Turkbear,

I have solved it in the meanwhile. The proposed solution provided by you did not work. Each time I received an DLL error message.

Solution I have made is to create a formula field only contaning the SKU.sku. I created via your proposed solution the distint count and that worked.

Thanks for the help.