Teradata 从外部导入的数据表的 INNER JOIN 问题

 一、无法INNER JOIN 

SELECT T1.Party_ID, T1.R, T1.F, T1.M,  T2.lab
FROM 
(      SELECT Party_ID,
        CAST(ROUND((CAST(MAX_R AS DECIMAL(20,4))-CAST(R AS DECIMAL(20,4)))/ (CAST(MAX_R AS DECIMAL(20,4))-CAST(MIN_R AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4)) AS R,
        CAST(ROUND((CAST(F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))/ (CAST(MAX_F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4))    AS F,
        CAST(ROUND((CAST(M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))/ (CAST(MAX_M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))*100,1) AS DECIMAL(20,4)) AS M
        FROM (
        SELECT  t1.Party_ID,  t1.R,  t1.F,  t1.M
        FROM    PT_TEMP_P.PTY_ANALYSIS t1
        INNER   JOIN   PT_TEMP_P.PTY_LANE  t2
        ON      t1.Party_Id  =  t2.Party_Id 
        WHERE   t1.F>0   
        AND     t1.M<100000 AND  t1.M>0  ) A ,
        
        ( SELECT
        MAX(R) AS MAX_R, MIN(R) AS MIN_R,
        MAX(F) AS MAX_F, MIN(F) AS MIN_F,
        MAX(M) AS MAX_M, MIN(M) AS MIN_M
        FROM PT_TEMP_P.PTY_ANALYSIS
        WHERE  F>0  AND  M>0 AND M<100000 ) B
 
 ) T1 ,
 
(   SELECT  R,F,M,lab
     FROM   PT_TEMP_P.RFM_LABEL_TRANS   ) T2
WHERE  
T1.R=T2.R AND T1.F = T2.F AND T1.M=T2.M

目标阐述：

表PT_TEMP_P.PTY_ANALYSIS：原始数据表, 包含的变量R、F、M、ID_Nbr；
表PT_TEMP_P.RFM_LABEL_TRANS：为从外部导入teradata数据库中的表. 变量为R、F、M、lab, 该表中的R、F、M为”原始数据“标准化后的结果, 另有一列lab表示一个新的变量.
表 PT_TEMP_P.PTY_LANE 中的ID_Nbr 为原始数据表中 ID_Nbr 的子集. 
首先INNER JOIN 表PT_TEMP_P.PTY_ANALYSIS 和 表PT_TEMP_P.PTY_LANE, 得到表PT_TEMP_P.PTY_LANE中的ID_Nbr对应的R、F、M值,
目标：需要通过INNER JOIN 找到表PT_TEMP_P.PTY_LANE中的ID_Nbr对应的lab值.

二、数据检查

SELECT   R,F,M,lab
FROM PT_TEMP_P.RFM_LABEL_TRANS
WHERE   
(R=79.4  AND F=13.1AND M=17.4)OR
(R=96.7AND F=2.3AND M=3.4)OR
(R=88.2AND F=3.9AND M=9)OR
(R=96.4AND F=13.9AND M=27.6)......

/*其中, where条件中的数据来自表T1 */

利用Excel快速生成文本：



检查结果：表PT_TEMP_P.RFM_LABEL_TRANS 中能够找到表T1中的数据，两个表本应该可以INNER JOIN.

三、解决办法

/*先建表, 将表T1存入某个被定义的表中*/
CREATE MULTISET TABLE Pt_temp_p.p01 (
Party_ID varchar(255),  R decimal(20,4),  F decimal(20,4),  M decimal(20,4)
);

 INSERT INTO PT_TEMP_P.p01 (
    Party_ID,
    R,
    F,
    M
 )
 SELECT Party_ID,
        CAST(ROUND((CAST(MAX_R AS DECIMAL(20,4))-CAST(R AS DECIMAL(20,4)))/ (CAST(MAX_R AS DECIMAL(20,4))-CAST(MIN_R AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4)) AS R,
        CAST(ROUND((CAST(F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))/ (CAST(MAX_F AS DECIMAL(20,4))-CAST(MIN_F AS DECIMAL(20,4)))*100,1)  AS DECIMAL(20,4))    AS F,
        CAST(ROUND((CAST(M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))/ (CAST(MAX_M AS DECIMAL(20,4))-CAST(MIN_M AS DECIMAL(20,4)))*100,1) AS DECIMAL(20,4)) AS M
        FROM (
        SELECT  t1.Party_ID,  t1.R,  t1.F,  t1.M
        FROM  PT_TEMP_P.PTY_ANALYSIS t1
        INNER  JOIN   PT_TEMP_P.PTY_LANE  t2
        ON  t1.Party_Id  =  t2.Party_Id 
        WHERE t1.F>0   
        AND  t1.M<100000 AND  t1.M>50 ) A ,
        
        ( SELECT
        MAX(R) AS MAX_R, MIN(R) AS MIN_R,
        MAX(F) AS MAX_F, MIN(F) AS MIN_F,
        MAX(M) AS MAX_M, MIN(M) AS MIN_M
        FROM PT_TEMP_P.PTY_ANALYSIS
        WHERE AND F>0  AND  M>50 AND M<100000 ) B
/*再INNER JOIN 新建的表与 从外部导入的表*/
SELECT  t1.Party_ID, t1.R,t1.F,t1.M
,t2.labFROM  pt_temp_p.p01 t1 INNER JOIN   PT_TEMP_P.RFM_LABEL_TRANS t2
ON   t1.R=t2.R AND t1.F =t2.F AND t1.M=t2.M

 

无法INNER JOIN 的原因：可能是数据类型不匹配.  
但即使在where条件中将匹配变量通过cast... as...成相同的数据类型，仍然不能成功.

转载于:https://www.cnblogs.com/dream-flower/p/3955030.html