Full Outer Join????

[Milla]

I have a problem, and I think it centers around me not being able to figure out how to do a full outer join in CR. It could be something else, and any workarounds or advice is very welcome!

I have three tables:

info acct1 acct2
==============================
cust_no acct_no acct_no
cust_nm acct_tp acct_tp
cust_ad cust_no cust_no



I want to be able to list all info.cust_no where there is NO corresponding acct1.cust_no OR acct2.cust_no. In other words, if I have customers defined with no accounts at all, I want to know who they are.

I think I've managed to do this in access (not using CR) with a full outer join (+)=+ ...but I can't seem to get it in Crystal.

I'm using CR 8.5 and connecting to a Sybase 11 DB.

Thanks very much.

 

[balves]

I don't believe that the Crystal linking expert supports Full Outer Join.

A couple of things you can do are:

- use a stored procedure as the data source and write the SQL query as you like
- you can edit the SQL statement of your Crystal report making the WHERE clause 0=1 and add an entire second SQL statement with a UNION that does a full outer join like this:

SELECT a, b, c, ...
FROM table A, ...
WHERE 0=1
UNION
SELECT a, b, c,
add your FULL OUTER JOIN stuff

 

[Milla]

Balves,

Thanks very much for your quick response. I don't really get what you mean in option #1, but that's fine. I'm trying option #2 now, I'll let you know the results.

Thanks !

 

[Ngolem]

I would approach the problem this way.

I would create 2 subreports (one for each Account table)

I would create a shared variable in each Subreport which would capture the customer no.'s in each table

Shared StringVar Account1CustNo;
Shared StringVar Account2CustNo;

the sections of both subreports would be suppressed so they wouldn't show in the main report and they would be placed in the report header in a section made as thin as possible.

In the main report I would generate the information for all customer no's but I would place a formula in the Conditional suppress of appropriate sections to suppress information if there was already an account set up for that customer

the formual would be

whileprintingrecords;
Shared StringVar Account1CustNo;
Shared StringVar Account2CustNo;

{Table.CustomerNum} in Account1CustNo or
{Table.CustomerNum} in Account2CustNo;

that should work

Jim

 

[Milla]

Ngolem,

Thank you. I understand what you are saying, and I see how you are attacking this problem. I think it will work for me, once I get the syntax down.

My questions regarding this approach are:

Do I have to assign the variable a value in the subreport in order to be able to see it in the main report? My cust_no is a number, not a string, so I have

Shared Numbervar Account1CustNo;
...etc...

In that fmla, do I need to assign it:

Account1CustNo = acct1.cust_no
....or will putting that fmla in the detail of the report "automatically" (ie: magically) assign my variable the value of the cust_no?

Do I need to specify the var as an array? How will the "whileprintingrecords" section of your conditinal formatting formula "step" thorugh all the data in the subreports, and not just compare it to "one" static value that it was assigned last (when the sub report refreshed)?

I'm sure I'm not being clear, but I appreciate your help.

Thanks so much.