Adding Inner Join To Query With Subquery With Inline Select Statement

I have the following SQL which works: Select TRADING_DWKEY, M_SM_USER_CODE, FIRST_TRADE, 'New Footprint' Status, Null Q4Vol, CQVol From (Selec

Solution 1:

Without any DDL statements defining the tables it is difficult to test but:

1. You are comparing dates using strings - Oracle is probably doing an implicit conversion back to a date using the value of the NLS_DATE_FORMAT session parameter (which is hopefully DD-MON-YY) but if this ever changes then it will break the query (and the query won't have changed so it will be a pain to debug). Stop using strings as dates and use date literals - i.e. DATE '2016-01-01'.
2. You have the first HAVING clause before the GROUP BY clause - swap them around.
3. You also are mixing a legacy comma join and ANSI join - replace the comma join with an INNER JOIN and move the join condition out of the WHERE clause.
4. Grouping by A.TRADING_DWKEY||A.RM_SM_USER_CODE is inefficient. You already have A.TRADING_DWKEY as one of the GROUP BY terms so don't concatenate the strings just include the other column in the GROUP BY clause.

Something like:

Select TRADING_DWKEY,
       RM_SM_NAME,
       FIRST_TRADE,
       'New Footprint' Status,
       Null Q4Vol,
       CQVol 
From (
  Select A.TRADING_DWKEY,
         q.RM_SM_NAME,
         B.FIRST_TRADE,
         SUM(A.RISK_AMOUNT_ADJ)/1000000 CQVol 
  from   FACT_TRADE_ROLLUP A
         INNERJOIN
         ( SELECT   TRADING_DWKEY,
                    MIN(FIRST_TRADE_DATE) FIRST_TRADE  
           FROM     ACCOUNT_FIRST_LAST_TRADE_DATES
           GROUPBY TRADING_DWKEY
           HAVINGMIN(FIRST_TRADE_DATE)>= trunc(sysdate-1,'Y')
         ) B
         ON ( A.TRADING_DWKEY=B.TRADING_DWKEY )
         INNERJOIN REF_RM_SM_REGION q
         ON ( q.RM_SM_USER_CODE = A.RM_SM_USER_CODE )
  WHERE A.DATE_KEY >=DATE'2016-01-01'AND   ( (    A.PRODUCT_DWKEY IN('RT')
           AND A.Account_Type='Customer'
          )
        OR A.PRODUCT_DWKEY IN('OB','BS','MM')
        )
  AND   A.Role ='SM'GROUPBY A.RM_SM_USER_CODE,
           A.TRADING_DWKEY,
           q.RM_SM_NAME,
           B.FIRST_TRADE
  HAVINGSUM(A.RISK_AMOUNT_ADJ)>=20000000
);

Solution 2:

Select TRADING_DWKEY, RM_SM_NAME, FIRST_TRADE, 'New Footprint' Status, Null Q4Vol, CQVol 
From (
    Select
        A.TRADING_DWKEY || A.RM_SM_USER_CODE, /* No name. So is this even used? */
        A.TRADING_DWKEY,
        q.RM_SM_NAME, B.FIRST_TRADE, SUM(A.RISK_AMOUNT_ADJ) /1000000 CQVol 
    from FACT_TRADE_ROLLUP A INNERJOIN/* <--- Changed here... */
        (
        SELECT TRADING_DWKEY, MIN(FIRST_TRADE_DATE) FIRST_TRADE  
        FROM ACCOUNT_FIRST_LAST_TRADE_DATES
        HavingMIN(FIRST_TRADE_DATE)>=to_char(trunc((sysdate-1),'Y'),'dd-Mon-yy')
        GROUPBY TRADING_DWKEY /* Very odd it ran with this after HAVING */
        ) B
            ON B.TRADING_DWKEY = A.TRADING_DWKEY /* <--- ...and here */INNERJOIN REF_RM_SM_REGION q on q.RM_SM_USER_CODE = A.RM_SM_USER_CODE
    WHERE
        A.DATE_KEY>='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'/* removed line that was here */GROUPBY
      A.TRADING_DWKEY||A.RM_SM_USER_CODE, A.TRADING_DWKEY,
      q.RM_SM_NAME, B.FIRST_TRADE
  HAVINGSUM(A.RISK_AMOUNT_ADJ) >=20000000
) NewFP;

I made a minimal effort to clean it up. Hopefully I didn't break it in the process. As noted it was probably the mixing of two types of join syntax that caused the error.