ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Crystal XI - Full Outer Join
http://www.istechforum.com/YaBB.pl?num=1181167503

Message started by DPurdy on 06/06/07 at 15:05:03

Title: Crystal XI - Full Outer Join
Post by DPurdy on 06/06/07 at 15:05:03

I need to use a full outer join with two files, but crystal freezes everytime. :-/ Anybody have any suggestions? Server has 2 GB memory, could that be an issue?

Title: Re: Crystal XI - Full Outer Join
Post by GasGiant on 06/07/07 at 05:07:32

You are trying to retrieve all rows from both tables? Sounds interesting. You'll likely be getting tons of non-matches, which leaves lots of null fields, making working with the data set rather interesting. Would you be willing to share the query so that we can have a better idea what is happening?

Title: Re: Crystal XI - Full Outer Join
Post by DPurdy on 06/07/07 at 09:27:47

i am trying to create a backlog report (or what my company calls a backlog report) for the past seven days. So I need the open sales orders as well as the shipments. So I linked BKARINV and BKARHINV. That's where I needed the full outer join.

I'm actually currently trying to use BKGLTRAN instead of BKARHINV, because I only need a Left outer join for that ( or so I'm hoping) and it currently seems to be working...even if it is taking forever to run. :)

If you have any suggestions on how to set this up better, I would be grateful to hear them. Thanks!

Title: Re: Crystal XI - Full Outer Join
Post by Kelloggs on 06/07/07 at 11:53:13

When querying a DB using Crystal Reports, the work it done by the Workstation, no the Server.
That is why Data Servers have "Views" ( Or Pass-Through Queries in MS Access).

Dont know exactly what type of info you are trying to get, but I suggest you start creating Views in Pervasive. These "Views" are seen by Crystal as Tables.

If you are planning to Link BKGLTRAN with BKAR* create a view:

SELECT
BKGL_TRN_GLACCT,
BKGL_TRN_GLDPT,
BKGL_TRN_DATE,
BKGL_TRN_CODE,
BKGL_TRN_INVC,
CONVERT (LTRIM(BKGL_TRN_INVC), SQL_DOUBLE) AS INV,
BKGL_TRN_DESC,
BKGL_TRN_DC,
BKGL_TRN_AMT
FROM BKGLTRAN
WHERE BKGL_TRN_DATE >= '2006-04-01' AND BKGL_TRN_DATE <= '2007-04-30'
AND BKGL_TRN_TYPE = 'RS' AND BKGL_TRN_GLDPT = 'SAT'

Remember that the column that has the "Invoice" (BKGL_TRN_INVC) is a Char and the "Invoice" info on
BKARHINV (BKAR_INV_NUM) is a DOUBLE.

You can not link them via SO Number.

The query to link the two BKAR* tables is:

SELECT
BKARINV.BKAR_INV_NUM,
BKARINV.BKAR_INV_SONUM,
BKARHINV.BKAR_INV_NUM
FROM BKARINV LEFT JOIN BKARHINV ON (BKARINV.BKAR_INV_SONUM = BKARHINV.BKAR_INV_SONUM) AND (BKARINV.BKAR_INV_NUM = BKARHINV.BKAR_INV_NUM)
ORDER BY BKARINV.BKAR_INV_NUM

Good Luck,

Kelloggs

Title: Re: Crystal XI - Full Outer Join
Post by DPurdy on 06/07/07 at 15:08:46

Actually I'm running the whole program on a test server that I have, which is why I keep calling it the server. Sorry! The information that I need from BKARINV is the Order date and the subtotal, from BKARHINV is Ship Date and the Subtotal. If there was a way not to join these tables, I could work with that. Thanks for the info on the Left join for BKARINV, but for that one I need a Full Outer Join. Is there a command for that one? I did a short search, but didn't come up with one.

Thanks so much for all the help!

Title: Re: Crystal XI - Full Outer Join
Post by Kelloggs on 06/08/07 at 10:11:45

If a Sales Order has been fully shipped is not on BKARINV :D

Ask NovaZyg, he will tell you how these two tables interact

Regards,

Kelloggs