使用内联选择语句将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:
- 您正在使用字符串比较日期-Oracle可能正在使用
NLS_DATE_FORMAT
会话参数(希望是DD-MON-YY
)的值进行隐式转换回日期,但如果此更改发生,则它将中断查询(而且查询不会更改,因此调试起来很麻烦).停止使用字符串作为日期并使用日期文字-即DATE '2016-01-01'
. - 您在
GROUP BY
子句之前有第一个HAVING
子句-交换它们. - 您还将混合使用旧的逗号联接和ANSI联接-用
INNER JOIN
替换逗号联接并将联接条件移出WHERE
子句. - 按
A.TRADING_DWKEY||A.RM_SM_USER_CODE
分组是无效的.您已经将A.TRADING_DWKEY
作为GROUP BY
术语之一,因此不要将字符串连接在一起,只需在GROUP BY
子句中包括另一列即可.
- 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 hopefullyDD-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'
. - You have the first
HAVING
clause before theGROUP BY
clause - swap them around. - 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 theWHERE
clause. - Grouping by
A.TRADING_DWKEY||A.RM_SM_USER_CODE
is inefficient. You already haveA.TRADING_DWKEY
as one of theGROUP BY
terms so don't concatenate the strings just include the other column in theGROUP 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屋!