CODEWITHSUNDEEP
sqlsql-server
David Smith
David Smith

Reputation: 91

Nested sub-query

For the column Q03Total and Q02TOTAL Im getting a invalid column name when executed. Im not sure where the issue is. The nested query is running as expected.

INSERT INTO LARGE_EXPOSURE_MAIN
select counterparty, CType, CParties, CStatus , CDemands, CSavings, CTime , CFinIns , CLiab,
CCp , (CDemands + CSavings + CTime + CFinIns+ CLiab + CCp ) Q03_Total , CTrn, CLoans, COnBal, COffBal,
CNonPerf, CFunCoun, (CLoans + COnBal + COffBal + CNonPerf + CFunCoun) Q02_TOTAL
from
(
  select counterparty , dbo.Q03_get_type_of_counterparty(customerid, 'Y') CType,
  dbo.Q03_get_list_of_counterparties(customerid, 'Y') CParties , 
  dbo.Q03_get_status_of_cp(customerid, 'Y') CStatus,
  dbo.Q03_get_demand_deposits(@as_at_date, customerid, 'YES') CDemands,
  dbo.Q03_get_savings_deposits(@as_at_date, customerid, 'YES') CSavings,
  dbo.Q03_get_time_deposits_qs(@as_at_date, customerid, 'YES') CTime,
  dbo.Q03_get_due_to_dep_taking_inst(@as_at_date, customerid, 'YES', trn) CFinIns,
  dbo.Q03_get_other_liabilities(@as_at_date, customerid, 'YES') CLiab,
  dbo.Q03_get_funding_to_cp(@as_at_date, customerid, 'YES') CCp,
  trn Ctrn,
  dbo.Q03_get_new_loans_advances(@as_at_date, customerid, 'Y',trn) CLoans,
  dbo.Q03_get_accts_receivables_qs(customerid, 'Y',trn) COnBal,
  dbo.Q02_Off_Bal_Ex(@as_at_date, customerid, 'Y',trn) COffBal,
  dbo.Q02_extract_np_loans(@as_at_date, customerid, 'Y',trn) CNonPerf,
  dbo.Q02_extract_counter_funding(@as_at_date, customerid, 'Y',trn) CFunCoun
  from 
  (
    select CMG.cust_name counterparty, cust_id customerid, PAN_GIR_NUM trn from [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_CMG] cmg
    where del_flg = 'N' and entity_cre_flg  = 'Y' 
    and exists (select 1 from [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_GAM] gam where gam.cust_id = cmg.cust_id and acct_cls_flg = 'N')
    and cust_minor_flg  = 'N'
    and CUST_GRP = 'OTH'  and PAN_GIR_NUM is not null) as a) as b

WHERE ((EXISTS (SELECT DISTINCT(TRN) FROM PRIME_CUST_MAIN) OR EXISTS (SELECT DISTINCT(CUST_NAME) FROM PRIME_CUST_MAIN))
OR (NOT EXISTS(SELECT DISTINCT(TRN) FROM PRIME_CUST_MAIN ) OR NOT EXISTS(SELECT DISTINCT(CUST_NAME) FROM PRIME_CUST_MAIN) ))
AND (Q03Total > 0 OR Q02TOTAL > 0)

Upvotes: 0

Views: 55

Answers (1)

James Anderbard
James Anderbard

Reputation: 504

Your naming of Q02Total and Q03Total has an underscore in one spot and not the other. Also the WHERE clause is part of the same SELECT where those fields are defined so they are not available to the WHERE clause. You can either replace them in the WHERE clause with their value (the list of added together fields) or put the SELECT statement in a sub-query and then perform the WHERE on that.

Edit: Example where you put it in a sub-query:

INSERT INTO LARGE_EXPOSURE_MAIN
select *
from (
select counterparty, CType, CParties, CStatus , CDemands, CSavings, CTime , CFinIns , CLiab,
CCp , (CDemands + CSavings + CTime + CFinIns+ CLiab + CCp ) Q03_Total , CTrn, CLoans, COnBal, COffBal,
CNonPerf, CFunCoun, (CLoans + COnBal + COffBal + CNonPerf + CFunCoun) Q02_TOTAL
from
(
  select counterparty , dbo.Q03_get_type_of_counterparty(customerid, 'Y') CType,
  dbo.Q03_get_list_of_counterparties(customerid, 'Y') CParties , 
  dbo.Q03_get_status_of_cp(customerid, 'Y') CStatus,
  dbo.Q03_get_demand_deposits(@as_at_date, customerid, 'YES') CDemands,
  dbo.Q03_get_savings_deposits(@as_at_date, customerid, 'YES') CSavings,
  dbo.Q03_get_time_deposits_qs(@as_at_date, customerid, 'YES') CTime,
  dbo.Q03_get_due_to_dep_taking_inst(@as_at_date, customerid, 'YES', trn) CFinIns,
  dbo.Q03_get_other_liabilities(@as_at_date, customerid, 'YES') CLiab,
  dbo.Q03_get_funding_to_cp(@as_at_date, customerid, 'YES') CCp,
  trn Ctrn,
  dbo.Q03_get_new_loans_advances(@as_at_date, customerid, 'Y',trn) CLoans,
  dbo.Q03_get_accts_receivables_qs(customerid, 'Y',trn) COnBal,
  dbo.Q02_Off_Bal_Ex(@as_at_date, customerid, 'Y',trn) COffBal,
  dbo.Q02_extract_np_loans(@as_at_date, customerid, 'Y',trn) CNonPerf,
  dbo.Q02_extract_counter_funding(@as_at_date, customerid, 'Y',trn) CFunCoun
  from 
  (
    select CMG.cust_name counterparty, cust_id customerid, PAN_GIR_NUM trn from [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_CMG] cmg
    where del_flg = 'N' and entity_cre_flg  = 'Y' 
    and exists (select 1 from [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_GAM] gam where gam.cust_id = cmg.cust_id and acct_cls_flg = 'N')
    and cust_minor_flg  = 'N'
    and CUST_GRP = 'OTH'  and PAN_GIR_NUM is not null) as a) as b) as b

WHERE ((EXISTS (SELECT DISTINCT(TRN) FROM PRIME_CUST_MAIN) OR EXISTS (SELECT DISTINCT(CUST_NAME) FROM PRIME_CUST_MAIN))
OR (NOT EXISTS(SELECT DISTINCT(TRN) FROM PRIME_CUST_MAIN ) OR NOT EXISTS(SELECT DISTINCT(CUST_NAME) FROM PRIME_CUST_MAIN) ))
AND (Q03Total > 0 OR Q02TOTAL > 0)

Upvotes: 1

Related Questions