If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Nested SQL Statement in Query Subject?

Started by salnick4, 01 Mar 2007 11:20:03 AM

Previous topic - Next topic

salnick4

I need help with the following SQL statement that I am attempting to build.Ã,  Please note that I am new with Framework:

SELECT
   cast ( T1.ACCT_ARR_WKLY_HIST.ACCT_ARR_HIST_DT as character(10)) || '_' || RTRIM(cast ( T2.ACCT_ARR.RESPBL_CTR_NUM as character(10))) "Hierarchy_ID",
   COUNT(T1.ACCT_NUM) AS TOTAL_COUNT,
   SUM(T1.BAL_AMT) AS BALANCE_AMOUNT,
   T1.RM_IND
FROM
   (SELECT
      ARR_ID_ACCT, RESPBL_CTR_NUM
   Ã,  FROM
      [HBUS RM 2.0].ACCT_ARR
   ) T2,
   (select distinct case
      EMPLY_ID when '0' then 'N' when '' then 'N' else 'Y' end "RM_IND" ,
      ACCT_NUM,
      BAL_AMT,
      ARR_ID_ACCT,
      ACCT_ARR_HIST_DT
   from
      [HBUS RM 2.0].ACCT_ARR_WKLY_HIST
   ) T1

WHERE
   T2.ARR_ID_ACCT = T1.ARR_ID_ACCT

GROUP BY
   T1.RM_IND,
   T1.ACCT_ARR_HIST_DT,
   T2.RESPBL_CTR_NUM

I am getting an error stating that the Table or View "ACCT_ARR_WKLY_HIST" and Table or View "ACCT_ARR" was not found in the dictionary.Ã,  Yet I believe that I am creating the tables "T1" and "T2" with reference to those tables????

Any help would be GREAT!

blom0344

You seem to overuse the aliasses:

T1.ACCT_ARR_WKLY_HIST.ACCT_ARR_HIST_DT

should be T1.ACCT_ARR_HIST_DT

cause you alias  ACCT_ARR_WKLY_HIST as  T1