Return $0 value

[Mike Hughes]

The query below shows me the current amount paid for each ID_CASE. The problem is when there is no CURREN PAID the case does not get included in the table [3 CURRENT PAID]. I want the case to show up in the table with the [CURRENT PAID] as $0.

Can someone please point me in the right direction? Thanks


SELECT DISTINCT
[2 CASES BY DIST AND WORKER DETAIL].CASELOAD,
NOLDBA_LOG_SUPPORT.ID_CASE,
Sum(NOLDBA_LOG_SUPPORT.AMT_TXN_CUR_SUP) AS [CURR PAID]
INTO [3 CURRENT PAID]

FROM NOLDBA_LOG_SUPPORT INNER JOIN [2 CASES BY DIST AND WORKER DETAIL]
ON NOLDBA_LOG_SUPPORT.ID_CASE = [2 CASES BY DIST AND WORKER DETAIL].CASE

WHERE (((NOLDBA_LOG_SUPPORT.MTH_SUPPORT)=[MONTH SUPPORT YYYYMM]) AND
((NOLDBA_LOG_SUPPORT.DT_DISTRIBUTE) Between
[DATE START MM/DD/YYYY] And [END MM/DD/YYYY]) AND
((NOLDBA_LOG_SUPPORT.CD_TYPE_RECORD)="O") AND
((NOLDBA_LOG_SUPPORT.SEQ_EVENT_FUNCTIONAL) In (1810,1820,1825,1250)))

GROUP BY [2 CASES BY DIST AND WORKER DETAIL].CASELOAD,
NOLDBA_LOG_SUPPORT.ID_CASE, NOLDBA_LOG_SUPPORT.AMT_TXN_CUR_SUP;

 

[namliam]

Change your inner join to a right join, this will show nulls for when no payment has been done.
Then use NZ to replace that null with 0

 

[Mike Hughes]

Namliam
That didn't work either. Here is the query with your adjustments

SELECT DISTINCT
[2 CASES BY DIST AND WORKER DETAIL].CASELOAD,
NOLDBA_LOG_SUPPORT.ID_CASE,
Sum (NZ (NOLDBA_LOG_SUPPORT.AMT_TXN_CUR_SUP)) AS [CURR PAID]
INTO [3 CURRENT PAID]

FROM
NOLDBA_LOG_SUPPORT RIGHT JOIN [2 CASES BY DIST AND WORKER DETAIL] ON
NOLDBA_LOG_SUPPORT.ID_CASE=[2 CASES BY DIST AND WORKER DETAIL].CASE

WHERE (((NOLDBA_LOG_SUPPORT.MTH_SUPPORT)=[MONTH SUPPORT YYYYMM]) AND
((NOLDBA_LOG_SUPPORT.DT_DISTRIBUTE) Between [DATE START MM/DD/YYYY] And
[END MM/DD/YYYY]) AND ((NOLDBA_LOG_SUPPORT.CD_TYPE_RECORD)="O") AND
((NOLDBA_LOG_SUPPORT.SEQ_EVENT_FUNCTIONAL) In (1810,1820,1825,1250)))

GROUP BY [2 CASES BY DIST AND WORKER DETAIL].CASELOAD,
NOLDBA_LOG_SUPPORT.ID_CASE, NOLDBA_LOG_SUPPORT.AMT_TXN_CUR_SUP;

 

[namliam]

Then change the right join to a left join, I am never sure which is which and when to use what...

You do know and/or understand left and right (outer) joins?

 

[Mike Hughes]

Namliam
That didn't work either. Here is the query with your adjustments

SELECT DISTINCT
[2 CASES BY DIST AND WORKER DETAIL].CASELOAD,
NOLDBA_LOG_SUPPORT.ID_CASE,
Sum (NZ (NOLDBA_LOG_SUPPORT.AMT_TXN_CUR_SUP)) AS [CURR PAID]
INTO [3 CURRENT PAID]

FROM
NOLDBA_LOG_SUPPORT LEFT JOIN [2 CASES BY DIST AND WORKER DETAIL] ON
NOLDBA_LOG_SUPPORT.ID_CASE=[2 CASES BY DIST AND WORKER DETAIL].CASE

WHERE (((NOLDBA_LOG_SUPPORT.MTH_SUPPORT)=[MONTH SUPPORT YYYYMM]) AND
((NOLDBA_LOG_SUPPORT.DT_DISTRIBUTE) Between [DATE START MM/DD/YYYY] And
[END MM/DD/YYYY]) AND ((NOLDBA_LOG_SUPPORT.CD_TYPE_RECORD)="O") AND
((NOLDBA_LOG_SUPPORT.SEQ_EVENT_FUNCTIONAL) In (1810,1820,1825,1250)))

GROUP BY [2 CASES BY DIST AND WORKER DETAIL].CASELOAD,
NOLDBA_LOG_SUPPORT.ID_CASE, NOLDBA_LOG_SUPPORT.AMT_TXN_CUR_SUP;

 

[namliam]

You do know and/or understand left and right (outer) joins?

I can see why the outer joins would fail... You want blanks/nulls on the NOLDBA_LOG_SUPPORT side but also have restrictions there...
Try this:
1) Make a query with only NOLDBA_LOG_SUPPORT, which has your limitations in it (the where's)
2) Make a new query that joins the query from 1 with the table [2 CASES BY DIST AND WORKER DETAIL], play with left and right joins,

this will work 100%

 

[Mike Hughes]

Namliam
Yes I understand Left and Right joins. I figured out a way around the issue which required me to create another table, but that is no big deal. Thanks for all your time.

Mike

 

[namliam]

*UGH* creating another table? NOOOOOOOOOOOOOOO!

There is no need to create another table, and please dont! This is bad and creates all kinds of potential hazards, inluding but not limited to bloating, data duplication and data integrity.