I’m working in a request in order to detect the rejected members for a source table.
This is the approach.
Source Table: OIA with 10 columns where 4 on this are:
| Product | Department | Entity | Account |
Otherwise, I have 4 different tables names:
| DIM_PRODUCT | DIM_DEPARTAMENT | DIM_ENTITY | DIM_ACCOUNT |
Each of this seconds tables have a columns named CHILD with data matching with table source (OIA) according the name (example: Column Product have data matching with CHILD in DIM_PRODUCT table). The members unmatched are the rejected members. So I need these members in a new table.
For that, I’ve performed 2 queries. 1) With the DISTINCT intersection between OIA with | DIM_PRODUCT | DIM_DEPARTAMENT | DIM_ENTITY | DIM_ACCOUNT | and 2) with the LEFT LIGHT JOIN where | DIM_PRODUCT | DIM_DEPARTAMENT | DIM_ENTITY | DIM_ACCOUNT | are nulls.
select COUNT(*) from (select DISTINCT OIA.* from fdmee.AIF_OPEN_INTERFACE OIA
INNER JOIN STG_AREA.DIM_ACCOUNT ACC on OIA.COL01 = ACC.CHILD
INNER JOIN STG_AREA.DIM_DEPARTAMENT PLN on OIA.COL04 = PLN.child
INNER JOIN STG_AREA.DIM_ENTITY ENT on OIA.COL07 = ENT.child
INNER JOIN STG_AREA.DIM_PRODUCT PRD on OIA.COL08 = PRD.child)
select count (*) from (select DISTINCT OIA.* from fdmee.AIF_OPEN_INTERFACE OIA
full OUTER JOIN STG_AREA.DIM_ACCOUNT ACC on OIA.COL01 = ACC.child
full OUTER JOIN STG_AREA.DIM_DEPARTAMENT PLN on OIA.COL04 = PLN.child
full OUTER JOIN STG_AREA.DIM_ENTITY ENT on OIA.COL07 = ENT.child
full OUTER JOIN STG_AREA.DIM_PRODUCT PRD on OIA.COL08 = PRD.child
where (OIA.COL01 is not null and acc.child is null) or (OIA.COL04 is not null and pln.child is null) or (OIA.COL07 is not null and ENT.child is null) or ( OIA.COL08 is not null and prd.child is null))
But when I perform the SUM of the COUNT for each query, the SUM should be = OIA. Source table.
Count of query 1) 1256609 2) 361703 SUM = 4948312 OIA= 4968007 DIFFERENCE between OIA – SUM = 19965
- I don’t understand reasons of the difference.
- Which is your feedback of these queries?
I’ve performed other queries for 1 and 2 but the result is the same. I’ve performed query 1 with IN and the queries 2 with NOT IN, NOT EXIST.
I will be awaiting your comments.