使用内联选择语句将INNER JOIN添加到带有子查询的查询中 [英] Adding INNER JOIN to Query With Subquery With Inline Select Statement

问题描述

我有以下有效的SQL:

I have the following SQL which works:

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) >= to_char(trunc((sysdate-1),'Y'),'dd-Mon-yy')
                  GROUP BY TRADING_DWKEY) B
          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' 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)>=20000000) NewFP;

当我尝试将INNER JOIN添加到子查询(拉名称而不是代码)时，我收到一个错误，即A.RM_SM_USER_CODE在编写INNER JOIN的行上不是有效的标识符.但是，我肯定，此字段存在于FACT_TRADE_ROLLUP表中.我怀疑该错误与我放置INNER JOIN的位置有关，但无法确定它.

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 positive 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.

这是经过完全修改的SQL(但是即使我只是将INNER JOIN语句放入其中而没有实际修改列，我也会收到错误消息.

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.

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)>=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>='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)>=20000000) NewFP;

推荐答案

没有任何定义表的DDL语句，很难测试，但是:

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

1. 您正在使用字符串比较日期-Oracle可能正在使用NLS_DATE_FORMAT会话参数(希望是DD-MON-YY)的值进行隐式转换回日期，但如果此更改发生，则它将中断查询(而且查询不会更改，因此调试起来很麻烦).停止使用字符串作为日期并使用日期文字-即DATE '2016-01-01'.
2. 您在GROUP BY子句之前有第一个HAVING子句-交换它们.
3. 您还将混合使用旧的逗号联接和ANSI联接-用INNER JOIN替换逗号联接并将联接条件移出WHERE子句.
4. 按A.TRADING_DWKEY||A.RM_SM_USER_CODE分组是无效的.您已经将A.TRADING_DWKEY作为GROUP BY术语之一，因此不要将字符串连接在一起，只需在GROUP BY子句中包括另一列即可.

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.

类似的东西:

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
         INNER JOIN
         ( SELECT   TRADING_DWKEY,
                    MIN(FIRST_TRADE_DATE) FIRST_TRADE  
           FROM     ACCOUNT_FIRST_LAST_TRADE_DATES
           GROUP BY TRADING_DWKEY
           HAVING   MIN(FIRST_TRADE_DATE)>= trunc(sysdate-1,'Y')
         ) B
         ON ( A.TRADING_DWKEY=B.TRADING_DWKEY )
         INNER JOIN 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' 
  GROUP BY A.RM_SM_USER_CODE,
           A.TRADING_DWKEY,
           q.RM_SM_NAME,
           B.FIRST_TRADE
  HAVING SUM(A.RISK_AMOUNT_ADJ)>=20000000
);

这篇关于使用内联选择语句将INNER JOIN添加到带有子查询的查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！