none
Subselect with not exists /Checking if distinct fields exist in two other tables

    Question

  • Hello, i have to upgrade the following select statement which is part of an insert into statement.. Now, The statement checks, if in the table t_dwh_bew_all_acc (the fact table) contains distinct rows with account_id and country_id. The two table tmp_sta_bew_all_balances and tmp_sta_bew_all_pl can both contain this kind of rows (these are  two dimensions tables). The task is, if there are entries in the fact table which don't have any corresponding entry in a dimension table (I know this should not be, the DWH was created by others an uses no surrogate keys).

    The problem one further field which has to be checked by the not exists function. Beside account and country_id I need to add the company_id.

    SELECT distinct t2.ACCOUNT_ID, t2.COUNTRY_ID
    FROM T_DWH_BEW_ALL_ACC as t2
    WHERE not exists
            (    Select distinct t3.ACCOUNT, t3.COUNTRY_ID from
                    (
                        Select distinct t5.ACCOUNT as ACCOUNT , t5.COUNTRY_ID from TMP_STA_BEW_ALL_BALANCES t5 where COUNTRY_ID = 'NL'
                        union
                        Select distinct t6.ACCOUNT as ACCOUNT , t6.COUNTRY_ID from TMP_STA_BEW_ALL_PL t6 where COUNTRY_ID = 'NL'
                    ) t3
                WHERE t3.ACCOUNT = t2.ACCOUNT_ID
            )
    and t2.COUNTRY_ID = 'NL'
    

    So I added the company_id field in the select and the subselect statements. In order to check the result I've deleted rows in the two tables in the subselect so the script should find something. When I add the company_id in the where clause I've got thousands of rows.

    SELECT distinct t2.ACCOUNT_ID, t2.COUNTRY_ID, t2.company_id
    FROM T_DWH_BEW_ALL_ACC as t2
    WHERE not exists
            (    Select distinct t3.ACCOUNT, t3.COUNTRY_ID, t3.company_id from
                    (
                        Select distinct t5.ACCOUNT as ACCOUNT , t5.COUNTRY_ID, t5.company_id from TMP_STA_BEW_ALL_BALANCES t5 where COUNTRY_ID = 'NL'
                        union
                        Select distinct t6.ACCOUNT as ACCOUNT , t6.COUNTRY_ID, t6.company_id from TMP_STA_BEW_ALL_PL t6 where COUNTRY_ID = 'NL'
                    ) t3
                WHERE t3.ACCOUNT = t2.ACCOUNT_ID and
    			t3.company_id = t2.company_id
            )
    and t2.COUNTRY_ID = 'NL'

    So this does not work. Is this actually the right idea? Anay ideas are appreciated..

    Tuesday, July 10, 2012 4:17 PM

Answers

  • Try:

    (SELECT ACCOUNT_ID, COMPANY_ID FROM T_DWH_BEW_ALL_ACC WHERE COUNTRY_ID = 'NL')
    EXCEPT
    (
    SELECT ACCOUNT AS ACCOUNT_ID, COMPANY_ID FROM TMP_STA_BEW_ALL_BALANCES WHERE COUNTRY_ID = 'NL'
    UNION ALL
    SELECT ACCOUNT AS ACCOUNT_ID, COMPANY_ID FROM TMP_STA_BEW_ALL_PL WHERE COUNTRY_ID = 'NL'
    )
    GO

    - Exclude [COUNTRY_ID] since we know the ID already.

    - Use UNION ALL because we do not care if those rows are in both dimensions, and we could avoid a sorting in order to get distinct rows.

    - The operator EXCEPT will give all the distinct tuples.


    AMB

    Some guidelines for posting questions...



    Tuesday, July 10, 2012 4:49 PM
    Moderator

All replies

  • Try:

    (SELECT ACCOUNT_ID, COMPANY_ID FROM T_DWH_BEW_ALL_ACC WHERE COUNTRY_ID = 'NL')
    EXCEPT
    (
    SELECT ACCOUNT AS ACCOUNT_ID, COMPANY_ID FROM TMP_STA_BEW_ALL_BALANCES WHERE COUNTRY_ID = 'NL'
    UNION ALL
    SELECT ACCOUNT AS ACCOUNT_ID, COMPANY_ID FROM TMP_STA_BEW_ALL_PL WHERE COUNTRY_ID = 'NL'
    )
    GO

    - Exclude [COUNTRY_ID] since we know the ID already.

    - Use UNION ALL because we do not care if those rows are in both dimensions, and we could avoid a sorting in order to get distinct rows.

    - The operator EXCEPT will give all the distinct tuples.


    AMB

    Some guidelines for posting questions...



    Tuesday, July 10, 2012 4:49 PM
    Moderator
  • Thanks, I will try next morning. Currently I have no access to the system.
    Tuesday, July 10, 2012 5:07 PM
  • The statement did work. I've got the same strage results and then after examining the input data I found a huge data error with many, many accounts missing a company entry... Theanks for your help.
    Wednesday, July 11, 2012 5:40 PM