Oracle Join by two different fields?

[Solo4357]

Having a problem with a select query, hope you all can help me.

I this query:

SELECT     SUM(decode(JCDETAIL.JCDT_TYPE_CODE, 'C', JCDETAIL.JCDT_AMT, 0)) AS C_SUM, SUM(decode(JCDETAIL.JCDT_TYPE_CODE, 'B', 
                      JCDETAIL.JCDT_AMT, 0)) AS B_SUM, UETD_PROJECTPER.PROJECTMAN
FROM         JCJOBCAT FROM JCDETAIL, JCJOBCAT, JCJOBHPHS 

WHERE
                      JCJOBCAT.JCAT_JOB_CODE = UETD_PROJECTPER.JOB_CODE AND 
                      JCJOBCAT.JCAT_COMP_CODE = UETD_PROJECTPER.COMP_CODE  AND
                      JCJOBCAT.JCAT_COMP_CODE = JCDETAIL.JCDT_COMP_CODE AND 
                      JCJOBCAT.JCAT_JOB_CODE = JCDETAIL.JCDT_JOB_CODE AND 
                      JCJOBCAT.JCAT_PHS_CODE = JCDETAIL.JCDT_PHS_CODE AND 
                      JCJOBCAT.JCAT_CODE = JCDETAIL.JCDT_CAT_CODE

GROUP BY UETD_PROJECTPER.PROJECTMAN

To summarize: I'm adding up the JCDT_AMT wherever the job_type = 'B' and wherever the job_type = 'C' (Costs and billings.) I'm doing this for all job_nums where it matches the projectman in the UETD_PROJECTPER table. This way I can select a project manager and grab his jobs.

My problem came in when I found out that in the jcdetail table some of the jobs were sub jobs. In those cases, the jobnum would be different in the jcdetail table and the master job num that would actually exist in UETD_PROJECTPER table would be in a field in JCDETAIL called JOB_CTRL_NUM. The jobnum field in those cases would have a value of 'ALL' which doesn't exist in the UETD table. Consequently, I would end up missing records.


So how do I do the join for jcdetail.job_code against uetd_projectper.job_code when the jcdetail.job_code is a number and jcdetail.job_ctrl_code against uetd_projectper.job_code when jcdetail.job_code = 'ALL'?

A sproinging sound was heard....

 

[johnherman]

You seem to have identified the proper join criteria, so write it as two queries with a UNION statement joining the two. Or a CASE statement depending on your comfort level with those commands.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Solo4357]

Ok, I think I figured it out somewhat but then I got another problem.

SELECT UETD_PROJECTPER.PROJECTMAN, SUM(decode(JCDETAIL.JCDT_TYPE_CODE, 'B', JCDETAIL.JCDT_AMT, 0)) AS BILLED, sum(decode(JCDETAIL.JCDT_TYPE_CODE,'C',jcdetail.jcdt_amt,0)) c_sum FROM JCDETAIL,	UETD_PROJECTPER 

WHERE 
((JCDETAIL.JCDT_JOB_CODE = UETD_PROJECTPER.JOB_CODE) OR (JCDETAIL.JCDT_JOB_CTRL_CODE = UETD_PROJECTPER.JOB_CODE))  AND 
(JCDETAIL.JCDT_POST_DATE >= TO_DATE('1/1/" & Me.ddyear.SelectedValue & "', 'MM/DD/YYYY')) AND 
(JCDETAIL.JCDT_POST_DATE < TO_DATE('1/1/" & Me.ddyear.SelectedValue + 1 & "', 'MM/DD/YYYY')) 

GROUP BY 
UETD_PROJECTPER.PROJECTMAN, 
JCDETAIL.JCDT_COMP_CODE 

HAVING (JCDETAIL.JCDT_COMP_CODE = 'WN') 

ORDER BY UETD_PROJECTPER.PROJECTMAN

Ok, by using the "or" in the joining, I was able to get any that were in the JOB_CODE or JOB_CTRL_CODE. Great! But then something weird happened. Whenever the Job_code matched with the JOB_CTRL_CODE, it seems to get it twice. I get double in my c_sum and Billed. But not when it matches the JOB_CODE. So a simple divide by two isn't going to fix it. I'm thinking I may need some sort of case statement or possibly the union select you spoke of.


The years I added later so I could get jobs in a fiscal year rather than everything. (The statement is being passed in from a VB/ASP.NET program.) But the key is this:

((JCDETAIL.JCDT_JOB_CODE = UETD_PROJECTPER.JOB_CODE) OR (JCDETAIL.JCDT_JOB_CTRL_CODE = UETD_PROJECTPER.JOB_CODE))

That's what got the matching job number in the UETD_PROJECTPER.JOB_CODE whether it was in JOB_CTRL_CODE or in JOB_CODE. Why is it getting the JOB_CTRL_CODE records twice? The JOB_NUM in those records are much different and don't match in the UETD_PROJECTPER