title: Adding INNER JOIN to Query With Subquery With Inline Select Statement
body: <p>I have the following SQL which works:</p>
  
  <pre><code>Select TRADING_DWKEY,
         M_SM_USER_CODE,
         FIRST_TRADE,
         'New Footprint' Status,
         Null Q4Vol,
         CQVol 
    From (Select A.TRADING_DWKEY||A.RM_SM_USER_CODE,
                 A.TRADING_DWKEY,
                 A.RM_SM_USER_CODE,
                 B.FIRST_TRADE, SUM(A.RISK_AMOUNT_ADJ)/1000000 CQVol
            from FACT_TRADE_ROLLUP A,
                 (SELECT TRADING_DWKEY,
                         MIN(FIRST_TRADE_DATE) FIRST_TRADE 
                    FROM ACCOUNT_FIRST_LAST_TRADE_DATES
                    Having MIN(FIRST_TRADE_DATE) &gt;= to_char(trunc((sysdate-1),'Y'),'dd-Mon-yy')
                    GROUP BY TRADING_DWKEY) B
            WHERE A.DATE_KEY&gt;='01-Jan-16' AND
                  ((A.PRODUCT_DWKEY IN ('RT') AND  A.Account_Type='Customer') OR
                    A.PRODUCT_DWKEY IN('OB','BS','MM')) AND
                  A.Role = 'SM' AND
                  A.TRADING_DWKEY = B.TRADING_DWKEY
            GROUP BY A.TRADING_DWKEY||A.RM_SM_USER_CODE,
                     A.TRADING_DWKEY,
                     A.RM_SM_USER_CODE,
                     B.FIRST_TRADE
            HAVING SUM(A.RISK_AMOUNT_ADJ)&gt;=20000000) NewFP;
  </code></pre>
  
  <p>When I attempt to add a INNER JOIN to the subquery (to pull a name instead of a code), I get an error that A.RM_SM_USER_CODE is not a valid identifier on the line where I write the INNER JOIN. However, I am <strong>positive</strong> this field exists in the FACT_TRADE_ROLLUP table. I suspect the error has something to do with where I place the INNER JOIN, but cannot figure it.</p>
  
  <p>Here is the fully modified SQL (but I get the error even if I just place the INNER JOIN statement in without actually modifying the columns.</p>
  
  <pre><code>Select TRADING_DWKEY,
         RM_SM_NAME,
         FIRST_TRADE,
         'New Footprint' Status,
         Null Q4Vol,
         CQVol 
    From (Select A.TRADING_DWKEY||A.RM_SM_USER_CODE,
                 A.TRADING_DWKEY,
                 q.RM_SM_NAME,
                 B.FIRST_TRADE,
                 SUM(A.RISK_AMOUNT_ADJ)/1000000 CQVol 
            from FACT_TRADE_ROLLUP A,
                 (SELECT TRADING_DWKEY, MIN(FIRST_TRADE_DATE) FIRST_TRADE  
                    FROM ACCOUNT_FIRST_LAST_TRADE_DATES
                    Having MIN(FIRST_TRADE_DATE)&gt;=to_char(trunc((sysdate-1),'Y'),'dd-Mon-yy')
                    GROUP BY TRADING_DWKEY) B
    --- error thrown on this line, specifying it at the end
            INNER JOIN REF_RM_SM_REGION q
              on q.RM_SM_USER_CODE = A.RM_SM_USER_CODE
            WHERE A.DATE_KEY&gt;='01-Jan-16' AND
                  ((A.PRODUCT_DWKEY IN('RT') AND A.Account_Type='Customer') OR 
                    A.PRODUCT_DWKEY IN('OB','BS','MM')) AND
                  A.Role = 'SM' AND
                  A.TRADING_DWKEY = B.TRADING_DWKEY
            GROUP BY A.TRADING_DWKEY||A.RM_SM_USER_CODE,
                     A.TRADING_DWKEY,
                     q.RM_SM_NAME,
                     B.FIRST_TRADE
            HAVING SUM(A.RISK_AMOUNT_ADJ)&gt;=20000000) NewFP;
  </code></pre>