-1

can someone please help me in combining these 2 Teradata queries into a single query? The tables - cdb.dim_party_doc_id, cdb.dim_doc_issuer, mdb.fp_account_entity_map do not have customer_account_number in them, so I not able to directly join all these tables in a single query directly. Thanks a lot!!

SELECT
    det.cust_id AS customer_account_number,
    c.encrypt_val AS ssn_encrypted,
    det.cust_first_name AS name_1,
    bal.BALANCE_AMT AS principal 
FROM
    cdb.DIM_CUSTOMER det 
INNER JOIN
    cdb.fact_stored_val_acct_dly bal 
        ON det.cust_id = bal.customer_id AND bal.curr_cd='USD' and bal.acct_type_code='SBA'
INNER JOIN
    cdb.dim_party_acct_map b 
        ON bal.customer_id = b.cust_id 
INNER JOIN
    cdb.dim_party_doc_id c 
        ON b.party_key = c.party_key 
        AND c.status = 'A' 
INNER JOIN
    cdb.dim_doc_issuer d 
        ON c.doc_issuer_id = d.doc_issuer_id 
        AND d.doc_type = 'TAX_ID' 
        AND d.doc_subtype = 'SSN'

and

   SELECT 
        own.owner_id AS customer_account_number,
        entity.entity_id AS dd_number
    FROM
        mdb.fp_account_owner_map own
    LEFT JOIN
        mdb.fp_account_entity_map entity
            ON own.fp_account_id = entity.fp_account_id
    WHERE 
        entity.entity_type in (12)
    AND 
        own.product_id in (5501)
user3903418
  • 143
  • 1
  • 1
  • 11
  • What do you mean by *combining*? There's either `union` with NULLs for non-existing columns or joining both Selects via Derived Tables/CTEs. – dnoeth Dec 28 '20 at 11:00
  • @dnoeth thanks for the quick response! Sorry for the confusion. By combining I meant to ask if there is a way in which I can get all the columns I am selecting in the above 2 queries by triggering a single SQL query. Right now, the only way I can think of is by creating 2 different views for each of the above query and then join those 2 views (if thats even possible). So union might not help me here I guess. I am exploring the Derived table that you suggested, I think it might work. Thanks again for the comment. – user3903418 Dec 28 '20 at 11:24
  • 1
    `select * from (select #1) as a join (select #2) as b on a.customer_account_number = b.customer_account_number)`, maybe an outer join. Works as long as one of those queries returns unique customer_account_numbers. – dnoeth Dec 28 '20 at 14:01
  • @dnoeth, thanks so much !! I also figured out a query slightly different from the one you posted above, which I have posted in the below section. Both the queries - urs and mine seem to return same number of rows. Thanks again for your answer :) – user3903418 Dec 28 '20 at 17:13
  • @dnoeth, I will be very thankful, if you could also review my below query, and let me know if I am wrong. TIA – user3903418 Dec 28 '20 at 17:24
  • Your query is probably correct, you're the only one to know this :-) – dnoeth Dec 28 '20 at 23:08

1 Answers1

0

Below query solves my problem

SELECT
    det.cust_id AS customer_account_number,
    temp.direct_deposit_account_number AS account_number,
    c.encrypt_val AS ssn_encrypted,
    det.cust_first_name AS name_1,
    bal.BALANCE_AMT AS principal
FROM
    cdb.DIM_CUSTOMER det 
LEFT JOIN
    cdb.fact_stored_val_acct_dly bal 
        ON det.cust_id = bal.customer_id AND bal.curr_cd='USD' and bal.acct_type_code='SBA'
INNER JOIN
    cdb.dim_party_acct_map b 
        ON bal.customer_id = b.cust_id 
INNER JOIN
    cdb.dim_party_doc_id c 
        ON b.party_key = c.party_key 
        AND c.status = 'A' 
INNER JOIN
    cdb.dim_doc_issuer d 
        ON c.doc_issuer_id = d.doc_issuer_id 
        AND d.doc_type = 'TAX_ID' 
        AND d.doc_subtype = 'SSN'    
INNER JOIN 
    (SELECT 
         own.owner_id AS customer_id,
         entity.entity_id AS direct_deposit_account_number
     FROM mdb.fp_account_owner_map own 
     LEFT JOIN
     mdb.fp_account_entity_map entity
     ON own.fp_account_id = entity.fp_account_id
     WHERE entity.entity_type in (12)
     AND own.product_id in (5501)) AS temp
 ON customer_account_number=temp.customer_id
user3903418
  • 143
  • 1
  • 1
  • 11