Skip to Content
0
Former Member
Nov 16, 2010 at 03:16 PM

Outer Join in Where Clause(Record Selection Formula)

19 Views

I have this SQL (below) and i want to use it in crystal,

the point is to show all the pro_code and details (i clipped most for ease of posting it here) and i use the summary distinct count on the cust_code to display how many customers if any are using this pro_code

Basically i want to use the ➕ in my record select formula

SELECT
   "INF_DETAI"."PRO_CODE",
   "INF_DETAI"."PRO_DES",
   "INF_DETAI"."DOC_DIR,
   "CRR_HEAD"."CUST_CODE"
FROM
    CRR_HEAD,
    INF_DETAI
WHERE
   "CRR_HEAD"."PRO_CODE"(+) = "INF_DETAI"."PRO_CODE" AND
   "CRR_HEAD"."COMPANY_CODE"(+) = 'W1'
GROUP BY
   "INF_DETAI"."PRO_CODE"

Here is a extract that shows that it will return 0 cust_codes if it doesn't match the PRO_CODE with a Customer's PRO_CODE

SELECT
   "INF_DETAI"."PRO_CODE",
    COUNT(DISTINCT "CRR_HEAD"."CUST_CODE")
FROM
    CRR_HEAD,
    INF_DETAI
WHERE
   "CRR_HEAD"."PRO_CODE"(+) = "INF_DETAI"."PRO_CODE" AND
   "CRR_HEAD"."COMPANY_CODE"(+) = 'W1'
GROUP BY
   "INF_DETAI"."PRO_CODE"

Edited by: AdamCheeseman on Nov 16, 2010 4:17 PM