query

I have two tables i want to retrive those rows which matches as well as not matching query. I am doing outer join.

RRS_SPK_UNALLOC_BUD_STG --- > 48 rows
RRS_BUDGET_TGT  --- > 22495

It should return me 22543 . It is returning me those rows. But RRS_SPK_UNALLOC_BUD_STG has 4 rows not matching with other tables . It shows me duplicate value.

I do following query.

SELECT A.DEPARTMENT,A.EXPENSE_CODE,NVL(A.BUDGET_AMOUNT,0),
A.YEAR_END_DATE,NVL(B.BUDGET,0), (NVL(A.BUDGET_AMOUNT,0) + NVL(B.BUDGET,0)) TOTAL FROM
RRS_BUDGET_TGT A,
RRS_SPK_UNALLOC_BUD_STG B
WHERE A.DEPARTMENT = B.DEPT_ID (+)
AND   A.EXPENSE_CODE = B.SUB_ACCOUNT (+)
AND   A.YEAR_END_DATE = B.END_OF_YEAR_DATE (+)
UNION
select C.dept_id,C.sub_account,nvl(c.budget,0),c.end_of_year_date,nvl(c.budget,0),
(nvl(c.budget,0) + nvl(c.budget,0)) Total from rrs_spk_unalloc_bud_stg C where  exists
(
SELECT B.DEPT_ID,A.YEAR,B.SUB_ACCOUNT,NVL(B.BUDGET,0),
B.END_OF_YEAR_DATE,NVL(B.BUDGET,0), (NVL(A.BUDGET_AMOUNT,0) + NVL(B.BUDGET,0)) TOTAL FROM
RRS_BUDGET_TGT A,
RRS_SPK_UNALLOC_BUD_STG B
WHERE A.DEPARTMENT  = B.DEPT_ID
AND   A.EXPENSE_CODE = B.SUB_ACCOUNT
AND   A.YEAR_END_DATE = B.END_OF_YEAR_DATE
);

I just want to see 22495 rows + 4 rows from RRS_SPK_UNALLOC_BUD_STG table which are not matched.
frinpdAsked:
Who is Participating?
 
izblankCommented:
OK, I got it.  change the second part to this:

select C.dept_id,C.sub_account,nvl(c.budget,0),c.end_of_year_date,nvl(c.budget,0),
(nvl(c.budget,0) + nvl(c.budget,0)) Total from rrs_spk_unalloc_bud_stg C where  NOT EXISTS
(
SELECT *
FROM
RRS_BUDGET_TGT A
WHERE A.DEPARTMENT  = C.DEPT_ID
AND   A.EXPENSE_CODE = C.SUB_ACCOUNT
AND   A.YEAR_END_DATE = C.END_OF_YEAR_DATE
);
0
 
izblankCommented:
I take it the second part of the query is for not matched rows.  If that's true, try changing the condition to "not exists"

SELECT A.DEPARTMENT,A.EXPENSE_CODE,NVL(A.BUDGET_AMOUNT,0),
A.YEAR_END_DATE,NVL(B.BUDGET,0), (NVL(A.BUDGET_AMOUNT,0) + NVL(B.BUDGET,0)) TOTAL FROM
RRS_BUDGET_TGT A,
RRS_SPK_UNALLOC_BUD_STG B
WHERE A.DEPARTMENT = B.DEPT_ID (+)
AND   A.EXPENSE_CODE = B.SUB_ACCOUNT (+)
AND   A.YEAR_END_DATE = B.END_OF_YEAR_DATE (+)
UNION
select C.dept_id,C.sub_account,nvl(c.budget,0),c.end_of_year_date,nvl(c.budget,0),
(nvl(c.budget,0) + nvl(c.budget,0)) Total from rrs_spk_unalloc_bud_stg C where  NOT EXISTS
(
SELECT B.DEPT_ID,A.YEAR,B.SUB_ACCOUNT,NVL(B.BUDGET,0),
B.END_OF_YEAR_DATE,NVL(B.BUDGET,0), (NVL(A.BUDGET_AMOUNT,0) + NVL(B.BUDGET,0)) TOTAL FROM
RRS_BUDGET_TGT A,
RRS_SPK_UNALLOC_BUD_STG B
WHERE A.DEPARTMENT  = B.DEPT_ID
AND   A.EXPENSE_CODE = B.SUB_ACCOUNT
AND   A.YEAR_END_DATE = B.END_OF_YEAR_DATE
);
0
 
frinpdAuthor Commented:
The Second Part with Not Exists does not return any rows. It's only A.Expense_code and B.sub_account that does not match between two tables for 4 rows.
0
 
izblankCommented:
Could you post a small sample of data in the two tables involved and explain in more detail what is considered a match and what is not?
0
 
frinpdAuthor Commented:
You are the man ! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.