I am doing some task with PLSQL. It is good for several task I am doing but in this case, I don't know what happened with my join Query.
I have these query:
WITH
subsi AS (
SELECT
mrk2.CUSTOMER_NUMBER ,
mrk2.CUSTOMER_NAME ,
mrk2.GROUP_NAME
FROM MOJU_REV_KLASIFIKASI mrk2
WHERE mrk2.GROUP_NAME LIKE '%' || 'Anak perusahaan' || '%'
OR mrk2.GROUP_NAME LIKE '%' || 'Anak Perusahaan dari Entitas Asosiasi Telkom Group' || '%'
OR mrk2.GROUP_NAME LIKE '%' || 'Anak Perusahaan' || '%'
),
agentColRef1 AS (
SELECT
mrka.CONTRACT_NUMBER ,
mrka.BP_NUMBER ,
mrka.CUSTOMER_NAME ,
mrka.GROUP_NAME ,
mrka.UBIS ,
mrka.REFR ,
mrka.GL_ACC ,
mrka.TOT_COST ,
mak.KL_REF1 AS NAMA_MITRA,
mak.KL_REF2 AS KL_NUMBER
FROM
MOJU_REV_KK43_ADJ mrka
LEFT JOIN MOJU_AGENT_KK11 mak ON mrka.CONTRACT_NUMBER = mak.CONTRACT_NUMBER
WHERE mrka.tahun = 2022 AND mrka.q = 6 AND mrka.TOT_COST != 0 AND mrka.REFR = '1.1'
GROUP BY mrka.CONTRACT_NUMBER , mrka.BP_NUMBER , mrka.CUSTOMER_NAME, mrka.GROUP_NAME , mrka.ubis, mrka.refr,
mrka.GL_ACC , mrka.TOT_COST, mak.KL_REF1 , mak.KL_REF2
),
agentColRef2 AS (
SELECT
mrka.CONTRACT_NUMBER ,
mrka.BP_NUMBER ,
mrka.CUSTOMER_NAME ,
mrka.GROUP_NAME ,
mrka.UBIS ,
mrka.REFR ,
mrka.GL_ACC ,
mrka.TOT_COST ,
mak.KL_REF1 AS NAMA_MITRA,
mak.KL_REF2 AS KL_NUMBER
FROM
MOJU_REV_KK43_ADJ mrka
LEFT JOIN MOJU_AGENT_KK12 mak ON mrka.CONTRACT_NUMBER = mak.CONTRACT_NUMBER
WHERE mrka.tahun = 2022 AND mrka.q = 6 AND mrka.TOT_COST != 0 AND mrka.REFR = '1.2'
GROUP BY mrka.CONTRACT_NUMBER , mrka.BP_NUMBER , mrka.CUSTOMER_NAME, mrka.GROUP_NAME , mrka.ubis, mrka.refr,
mrka.GL_ACC , mrka.TOT_COST, mak.KL_REF1 , mak.KL_REF2
),
agentColRef3 AS (
SELECT
mrka.CONTRACT_NUMBER ,
mrka.BP_NUMBER ,
mrka.CUSTOMER_NAME ,
mrka.GROUP_NAME ,
mrka.UBIS ,
mrka.REFR ,
mrka.GL_ACC ,
mrka.TOT_COST ,
mak.KL_REF1 AS NAMA_MITRA,
mak.KL_REF2 AS KL_NUMBER
FROM
MOJU_REV_KK43_ADJ mrka
LEFT JOIN MOJU_AGENT_KK13 mak ON mrka.CONTRACT_NUMBER = mak.CONTRACT_NUMBER
WHERE mrka.tahun = 2022 AND mrka.q = 6 AND mrka.TOT_COST != 0 AND mrka.REFR = '1.3'
GROUP BY mrka.CONTRACT_NUMBER , mrka.BP_NUMBER , mrka.CUSTOMER_NAME, mrka.GROUP_NAME , mrka.ubis, mrka.refr,
mrka.GL_ACC , mrka.TOT_COST, mak.KL_REF1 , mak.KL_REF2
),
agentColRefUnion AS (
SELECT *
FROM agentColRef1
UNION
SELECT *
FROM agentColRef2
UNION
SELECT *
FROM agentColRef3
),
agentColAlok1 AS (
SELECT
ac.CONTRACT_NUMBER AS CONTRACT_NUMBER ,
ac.BP_NUMBER AS BP_NUMBER ,
ac.CUSTOMER_NAME AS CUSTOMER_NAME ,
ac.GROUP_NAME AS GROUP_NAME ,
ac.ubis,
ac.REFR ,
ac.GL_ACC ,
ac.TOT_COST ,
ac.NAMA_MITRA,
ac.KL_NUMBER,
mabc.GL AS AKUN_BEBAN_1
FROM
agentColRefUnion ac
LEFT OUTER JOIN MOJU_AGENT_BEBAN_CPE mabc ON ac.KL_NUMBER = mabc.KL
GROUP BY ac.CONTRACT_NUMBER , ac.BP_NUMBER , ac.CUSTOMER_NAME, ac.GROUP_NAME , ac.ubis, ac.refr,
ac.GL_ACC , ac.TOT_COST, ac.nama_mitra , ac.kl_number, mabc.GL
),
agentColAlok2 AS (
SELECT
ac.CONTRACT_NUMBER,
ac.BP_NUMBER ,
ac.CUSTOMER_NAME ,
ac.GROUP_NAME ,
ac.ubis,
ac.REFR ,
ac.GL_ACC ,
ac.TOT_COST ,
ac.NAMA_MITRA,
ac.KL_NUMBER,
ac.AKUN_BEBAN_1,
CASE
WHEN (ac.AKUN_BEBAN_1 IS NULL) THEN maba.BEBAN_DNAPSO ELSE to_char(ac.AKUN_BEBAN_1)
END AS AKUN_BEBAN_2
FROM
agentColAlok1 ac
LEFT JOIN MOJU_AGENT_BEBAN_AKUN maba ON ac.GL_ACC = maba.GL_REVENUE
),
agentColTp AS (
SELECT
ac.CONTRACT_NUMBER,
ac.BP_NUMBER ,
ac.CUSTOMER_NAME ,
ac.GROUP_NAME ,
ac.ubis,
ac.REFR ,
ac.GL_ACC ,
ac.TOT_COST ,
ac.NAMA_MITRA,
ac.KL_NUMBER,
ac.AKUN_BEBAN_1,
ac.AKUN_BEBAN_2,
mrl.CUSTOMER_TP
FROM
agentColAlok2 ac
LEFT JOIN MOJU_REV_LTP mrl ON ac.NAMA_MITRA = mrl.SUBSIDIARIES
)
SELECT
COUNT(*)
FROM agentColAlok2 aca
WHERE aca.nama_mitra IS NOT NULL AND aca.nama_mitra != 'N/A'
When I run my agenColAlok2 to get the counts, it appears to have 911 records, but when I run my agentColTp I got 11090 records. Because it execute the results from agentColAlok2 I expect it should have 911 records also.
I have tried with INNER JOIN instead but still the records is different from what I expected. Also, the results from agentColAlok1 and agentColAlok2 is also not same. I have 833 records on agentColAlok1 and 911 records on agentColAlok2
Why it can be like that? Am I missing some concept about join or with clause? Any of you have an answer or have experienced same issue? Thank you.
The Joined table should get same exact records result
This example should help to explain it: http://sqlfiddle.com/#!18/8d8e5/1
In the example, I create two tables, a and b, and insert some data into them.
a:
| Col1 | Col2 |
|---|---|
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
b:
| Col3 | Col4 |
|---|---|
| 1 | 9 |
| 1 | 10 |
| 2 | 9 |
| 2 | 10 |
| 3 | 9 |
| 3 | 10 |
Then I run the query:
select * from a left join b on a.col1=b.col3
Which returns:
| col1 | col2 | col3 | col4 |
|---|---|---|---|
| 1 | 5 | 1 | 9 |
| 1 | 5 | 1 | 10 |
| 2 | 5 | 2 | 9 |
| 2 | 5 | 2 | 10 |
| 3 | 5 | 3 | 9 |
| 3 | 5 | 3 | 10 |
Note there are multiple matches for a.col1 in b.col3, so the count of the result was determined by the size of b rather than a.