WHERE clause -> OUTER JOIN clause 1

[Bontebok]

Hi Everyone,

I have an OUTER JOIN in a query that is supposed to drop off the check number if a check has not been printed yet, but still include the voucher. The absense of the record in the AP Header table indicates that there is no check.

I had to modify the query outside of Crystal because I could not figure out how to get the selection expert to place the evaluation in the OUTER JOIN instead of the WHERE clause.

Here is the working query:

SELECT
apvohdr.trx_ctrl_num,
appyhdr."doc_ctrl_num", appyhdr."date_doc",
apvohdr."doc_ctrl_num", apvohdr."date_applied",
apvend."vendor_name",
apvodet."amt_extended",
esc_apeproj."job_phase", esc_apeproj."expense_type_code"
FROM
{ oj (((("NAIB"."dbo"."appyhdr" appyhdr INNER JOIN "NAIB"."dbo"."appydet" appydet ON
appyhdr."trx_ctrl_num" = appydet."trx_ctrl_num&quot
RIGHT OUTER JOIN "NAIB"."dbo"."apvohdr" apvohdr ON
appydet."apply_to_num" = apvohdr."trx_ctrl_num"
AND appyhdr.payment_type <= 2 AND appyhdr.void_flag = 0)
INNER JOIN &quot;NAIB&quot;.&quot;dbo&quot;.&quot;apvodet&quot; apvodet ON
apvohdr.&quot;trx_ctrl_num&quot; = apvodet.&quot;trx_ctrl_num&quot
INNER JOIN &quot;NAIB&quot;.&quot;dbo&quot;.&quot;apvend&quot; apvend ON
apvohdr.&quot;vendor_code&quot; = apvend.&quot;vendor_code&quot
INNER JOIN &quot;NAIB&quot;.&quot;dbo&quot;.&quot;esc_apeproj&quot; esc_apeproj ON
apvodet.&quot;trx_ctrl_num&quot; = esc_apeproj.&quot;trx_ctrl_num&quot; AND
apvodet.&quot;sequence_id&quot; = esc_apeproj.&quot;row_num&quot;}
WHERE
apvohdr.&quot;date_applied&quot; >= 600000 AND
apvohdr.&quot;date_applied&quot; <= 999999


The evaluations in the WHERE clause are ok because they are part of the INNER JOIN.

Is there a way I can get Crystal (8.0) to place the evaluation in the OUTER JOIN instead of in the WHERE clause?

If not, I'll just put the query into a view or stored procedure.

Thanks,

Bontebok

 

[FVTrainer]

In Crystal 8.0, you can go to Database | Show SQL and actually edit the FROM and WHERE clauses. It's not recommended, because if you go back in to the record selection formula and make a change, you'll lose the changes you've made here and you'll have to go in and re-enter them. And this option is not available in CR 9 (although the command object in CR 9 would server the same purpose). But it is an option.

Personally, I think the view or stored proc is the better option.

 

[Bontebok]

I figured as much. I went ahead and moved the query to a stored procedure. If I make manual changes to the SQL statement, Crystal will handle all record filtering inside of Crystal, which is definitely not what I want since we have hundreds of thousands of records.

Thanks, Happy Halloween

Bontebok

 

[synapsevampire]

This isn't true: &quot;If I make manual changes to the SQL statement, Crystal will handle all record filtering inside of Crystal&quot;

But you need to be careful with what you do.

I suggest that everyone who can, use Views or SP's.

-k

 

[Bontebok]

If you make changes to the SQL statement and add items to the selection expert, the evaluation will not make it into the WHERE clause as it would if Crystal was managing the SQL statement. This is the case under Crystal 8.0, but I have no idea what happens in later versions.

Bontebok

 

[synapsevampire]

Bontebok: Still not exactly true, though it's much less likely to be able to pass anything, but it can be done, I showed people here how last week.

Anyway, don't use the selection expert, use the record selection formula of interest directly.

-k

 

[Bontebok]

Can you point me to the thread or article that you are referring to?

- Bontebok