1 Reply Latest reply on Jun 20, 2017 12:47 PM by B.Delmée

    VALIDATION OF COUNT QUERIES ARE WRONG

    3249256

      Hi Team,

      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.

       

      Queies:

       

       

              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

       

      QUESTIONS :

       

      1. I don’t understand reasons of the difference.
      2. Which is your feedback of these queries?

       

      NOTE:

       

      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.

       

      Many thanks.

      1. Regards.