Outer Join in Where Clause(Record Selection Formula)

[Former Member]

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

[Former Member · Accepted solution]

Adam,

I'm not familiar with that syntax, what database are you using?

What exactly are you attempting to accomplish with the (+)?

Jason

[Former Member · Accepted solution]

an outer join that brings all the results from INF_DETAIL and any results from CRR_HEAD that match on PRO_CODE but the CRR_HEAD matches must have a COMPANY_CODE of 'W1'

//INF_DETAIL//

PRO_CODE    | PRO_DES           | DOC_DIR      
............|...................|.......................................
TEST1       | Test INF          | ./tst.rpt
PROD1       | Building 1 INF    | ./customer.rpt
VENDOR1     | Building 1 INF-V  | ./vendor.rpt

//CRR_HEAD//

CUST_CODE   | PRO_CODE        | COMPANY_CODE
............|.................|..............
TEST1       | TEST1           | W1
TEST2       | TEST1           | W1
ACORTRANS   | PROD1           | W1
BOB         | PROD1           | 00

Desired Results:

PRO_CODE    | PRO_DES           | DOC_DIR                               | CUST_CODE
............|...................|.......................................|..............................
TEST1       | Test INF          | ./tst.rpt                             | TEST1
TEST1       | Test INF          | ./tst.rpt                             | TEST2
PROD1       | Building 1 INF    | ./customer.rpt                        | ACORTRANS
VENDOR1     | Building 1 INF-V  | ./vendor.rpt                          |

Edited by: AdamCheeseman on Nov 16, 2010 6:34 PM

[Former Member · Accepted solution]

You didn't say what kind of DB you're using, but you're using old SQL syntax for starters. You'll have better luck using updates syntax. The SQL below is pretty generic so it's syntax should be good for most databases.

Try this on for size...

SELECT
   id.PRO_CODE,
   id.PRO_DES,
   id.DOC_DIR,
   ch.CUST_CODE
FROM INF_DETAI id
LEFT OUTER JOIN CRR_HEAD ch ON id.PRO_CODE = ch.PRO_CODE
    AND ch.COMPANY_CODE = 'W1'
-- GROUP BY   id.PRO_CODE

The use of the LEFT OUTER JOIN allows all records from INF_DETAI to come through even if there is no corresponding record in CRR_HEAD. Moving the "AND ch.COMPANY_CODE = 'W1'" out of the WHERE clause to the join clause allows it to filter CRR_HEAD without filtering INF_DETAI

I commented out the GROUP BY clause. If you want to use the GROUP BY, you need to make sure that EVERY field in the select list is either part of an aggregated function or included in the GROUP BY... Not sure what you were trying to do there...

HTH,

Jason

[Former Member · Accepted solution]

sorry its Oracle 7i,

my query with the does work in oracle (and your right about that group by in the first query, it wasn't supposed to be in that first query just the second with the count)

i am sorry i wasn't clearer, the problem isn't with the query, both your method and mine with the return the correct result set in a regular ol' query,

my challenge is with getting Crystal to outer join with that 'W1' clause

Edited by: AdamCheeseman on Nov 16, 2010 7:32 PM

[Former Member · Accepted solution]

Gotcha... I don't use Oracle so I'm not overly familiar with PL SQL syntax.

So are you trying to build your report by adding table in CR and then linking them or are you adding your SQL code to a Command?

If you're adding the tables and letting CR write the SQL... Why? You've already gone to the trouble of writing it and testing it yourself... If you still want to do it that way, the formula in the Select Expert will look like this...

IsNull({CRR_HEAD.COMPANY_CODE}) OR
{CRR_HEAD.COMPANY_CODE} = "W1"

If you want to use your own SQL, just expand your Current Connection and choose Add Command. You can paste your SQL there and use that as your data source.

HTH,

Jason

[Former Member · Accepted solution]

its that select expert bit i was missing.

looking at that its so elementary, so "of course" grab every result and then filter out to view the nulls and the ones i am looking for

and as for the add command field...

I had NO IDEA that feature existed, i dismissed that "Add Command" option as "don't know...don't touch"

i have been always building my SQL in a scratch pad, testing it and then trying to build it letting CR make the command for me.

...WOW, thanks you will have saved my database many a View (after trying 5 ways to Sunday i concede and usually make a view and select the view in CR)

[Former Member · Accepted solution]

Yea... If you're comfortable writing your own SQL, the Add Command is the way to go. Especially if you don't have permission to add objects like views or SPs.

Anyway, I'm glad you got it working.

Jason

[Former Member]

marked as answered because i was opening the question in appropriate forum, but moderate moved original post, so its not answered yet

[saurabh_pathak]

Hi Adam,

If this question concerns designing then you may want to post it in [this forum|;.

If .NET SDK's are involved then you can go through[ this|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/12045] [original link is broken] [original link is broken] [original link is broken];.

Thanks

Saurabh

[Former Member]

your right, apologies i didn't check which forum i was in when i hit the bookmark

[former_member11696]

Moved to Report Designer forum