【DWS】SQL报错FULL JOIN is only supported with merge-joinable or has

举报
洋芋擦擦 发表于 2023/09/21 15:37:20 2023/09/21
【摘要】 sql报错FULL JOIN is only supported with merge-joinable or hash-joinable join conditions【情景一】--测试表1create table public.sales1( item varchar(10), year varchar(4), quantity int);insert into publi...

sql报错FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

【情景一】

--测试表1
create table public.sales1(
   item varchar(10),
   year varchar(4),
   quantity int
);

insert into  public.sales1 values('apple','0',800);
insert into  public.sales1 values('banana','0',1000);
insert into  public.sales1 values('apple','0',500);
insert into  public.sales1 values('banana','0',600);
insert into  public.sales1 values('apple','0',1200);


--测试表二
create table public.sales2(
   item varchar(10),
   year varchar(4),
   quantity int
);

insert into  public.sales2 values('apple','0',800);
insert into  public.sales2 values('banana','0',1000);
insert into  public.sales2 values('apple','0',500);
insert into  public.sales2 values('banana','0',600);
insert into  public.sales2 values('apple','0',1200);

--报错语句
select * from public.sales1 a1 left join public.sales2 t1 on a1.item = t1.item full join (select * from public.sales1) a2 on a1.item = a2.item or t1.item =a2.item;
--模拟DS下发
cursor cursor1 for select * from public.sales1 a1 left join public.sales2 t1 on a1.item = t1.item full join (select * from public.sales1) a2 on a1.item = a2.item or t1.item =a2.item;
select * from public.sales1 a1 left join public.sales2 t1 on a1.item = t1.item full join (select * from public.sales1) a2 on a1.item = a2.item or t1.item =a2.item;
--该语句在后台执行正常，DS工具执行报错

DS下发语句是通过游标的方式进行下发，通过模拟DS下发方式后台执行语句报错

cursor cursor1 for select * from public.sales1 a1 left join public.sales2 t1 on a1.item = t1.item full join (select * from public.sales1) a2 on a1.item = a2.item or t1.item =a2.item;

问题跟因：DWS带有游标的时候，不会进行FULL JOIN 优化重写，所以报错。 不带游标的时候，进行了FULL JOIN重写，语句中就没有FULL JOIN了

【情景二】

SELECT
        SYS_GUID()                       AS DATA_ID,              
        COALESCE(A1.ORG_NO,COALESCE(A2.ORG_NO,A3.ORG_NO),A1.ORG_NO)
                                         AS ORG_NO,               
        COALESCE(A1.RECIVE_DATE,COALESCE(A2.RECIVE_DATE,A3.RECIVE_DATE),A1.RECIVE_DATE)                                
                                         AS RECIVE_DATE,          
        NVL(A1.CORP_NORAML_AM1,0)        AS CORP_NORAML_AM1,      
        NVL(A1.CORP_ATTENTION_AM1,0)     AS CORP_ATTENTION_AM1,   
        NVL(A1.CORP_SECONDARY_AM1,0)     AS CORP_SECONDARY_AM1,   
        NVL(A1.CORP_SUSPICIOUS_AM1,0)    AS CORP_SUSPICIOUS_AM1,  
        NVL(A1.CORP_LOSE_AM1,0)          AS CORP_LOSE_AM1,        
        NVL(A1.CORP_NORAML_AM2,0)        AS CORP_NORAML_AM2,      
        NVL(A1.CORP_ATTENTION_AM2,0)     AS CORP_ATTENTION_AM2,   
        NVL(A1.CORP_SECONDARY_AM2,0)     AS CORP_SECONDARY_AM2,   
        NVL(A1.CORP_SUSPICIOUS_AM2,0)    AS CORP_SUSPICIOUS_AM2,  
        NVL(A1.CORP_LOSE_AM2,0)          AS CORP_LOSE_AM2,        
        NVL(A1.CORP_NORAML_AM3,0)        AS CORP_NORAML_AM3,      
        NVL(A1.CORP_ATTENTION_AM3,0)     AS CORP_ATTENTION_AM3,   
        NVL(A1.CORP_SECONDARY_AM3,0)     AS CORP_SECONDARY_AM3,   
        NVL(A1.CORP_SUSPICIOUS_AM3,0)    AS CORP_SUSPICIOUS_AM3,  
        NVL(A1.CORP_LOSE_AM3,0)          AS CORP_LOSE_AM3,        
        NVL(A2.CORP_NORAML_AM4,0)        AS CORP_NORAML_AM4,      
        NVL(A2.CORP_ATTENTION_AM4,0)     AS CORP_ATTENTION_AM4,   
        NVL(A2.CORP_SECONDARY_AM4,0)     AS CORP_SECONDARY_AM4,   
        NVL(A2.CORP_SUSPICIOUS_AM4,0)    AS CORP_SUSPICIOUS_AM4,  
        NVL(A2.CORP_LOSE_AM4,0)          AS CORP_LOSE_AM4,        
        NVL(A2.CORP_NORAML_AM5,0)        AS CORP_NORAML_AM5,      
        NVL(A2.CORP_ATTENTION_AM5,0)     AS CORP_ATTENTION_AM5,   
        NVL(A2.CORP_SECONDARY_AM5,0)     AS CORP_SECONDARY_AM5,   
        NVL(A2.CORP_SUSPICIOUS_AM5,0)    AS CORP_SUSPICIOUS_AM5,  
        NVL(A2.CORP_LOSE_AM5,0)          AS CORP_LOSE_AM5,        
        NVL(A1.PRIV_NORAML_AM1,0)        AS PRIV_NORAML_AM1,      
        NVL(A1.PRIV_ATTENTION_AM1,0)     AS PRIV_ATTENTION_AM1,   
        NVL(A1.PRIV_SECONDARY_AM1,0)     AS PRIV_SECONDARY_AM1,   
        NVL(A1.PRIV_SUSPICIOUS_AM1,0)    AS PRIV_SUSPICIOUS_AM1,  
        NVL(A1.PRIV_LOSE_AM1,0)          AS PRIV_LOSE_AM1,        
        NVL(A1.PRIV_NORAML_AM2,0)        AS PRIV_NORAML_AM2,      
        NVL(A1.PRIV_ATTENTION_AM2,0)     AS PRIV_ATTENTION_AM2,   
        NVL(A1.PRIV_SECONDARY_AM2,0)     AS PRIV_SECONDARY_AM2,   
        NVL(A1.PRIV_SUSPICIOUS_AM2,0)    AS PRIV_SUSPICIOUS_AM2,  
        NVL(A1.PRIV_LOSE_AM2,0)          AS PRIV_LOSE_AM2,        
        NVL(A1.PRIV_NORAML_AM3,0)        AS PRIV_NORAML_AM3,      
        NVL(A1.PRIV_ATTENTION_AM3,0)     AS PRIV_ATTENTION_AM3,   
        NVL(A1.PRIV_SECONDARY_AM3,0)     AS PRIV_SECONDARY_AM3,   
        NVL(A1.PRIV_SUSPICIOUS_AM3,0)    AS PRIV_SUSPICIOUS_AM3,  
        NVL(A1.PRIV_LOSE_AM3,0)          AS PRIV_LOSE_AM3,        
        NVL(A3.ASSET_HANDL_PRICE,0)      AS ASSET_HANDL_PRICE,    
        'M_FACT_BAD_RECIVE_DETAIL'      AS SYS_DATA_SRC,          
        'DWMART_SAI'                     AS SYS_ORISYS,           
        CURRENT_TIMESTAMP                AS SYS_CREATE_TIME,      
        CURRENT_TIMESTAMP                AS SYS_UPDATE_TIME       
    FROM (
    
    SELECT 
        Q1.MANAGE_ORG_NO                                                     AS ORG_NO,               
        Q1.RECIVE_DATE                                                       AS RECIVE_DATE,          
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') = '' AND Q3.TRANSFER_TYPE = '10' AND Q1.CLASSIFY_CODE = '10' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                             AS CORP_NORAML_AM1,      
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') = '' AND Q3.TRANSFER_TYPE = '10' AND Q1.CLASSIFY_CODE = '20' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                             AS CORP_ATTENTION_AM1,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') = '' AND Q3.TRANSFER_TYPE = '10' AND Q1.CLASSIFY_CODE = '30' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                             AS CORP_SECONDARY_AM1,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') = '' AND Q3.TRANSFER_TYPE = '10' AND Q1.CLASSIFY_CODE = '40' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                             AS CORP_SUSPICIOUS_AM1,  
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') = '' AND Q3.TRANSFER_TYPE = '10' AND Q1.CLASSIFY_CODE = '50' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                             AS CORP_LOSE_AM1,        
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '10' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                             AS CORP_NORAML_AM2,      
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '20' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_ATTENTION_AM2,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '30' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_SECONDARY_AM2,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '40' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_SUSPICIOUS_AM2,  
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '50' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_LOSE_AM2,        
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '10' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_NORAML_AM3,      
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '20' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_ATTENTION_AM3,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '30' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_SECONDARY_AM3,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '40' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_SUSPICIOUS_AM3,  
        SUM(CASE WHEN Q1.BUSI_TYPE = '10' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '50' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS CORP_LOSE_AM3,        
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') = ''AND Q3.TRANSFER_TYPE IN ('1001','1002','1003') AND Q1.CLASSIFY_CODE = '10' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)   
				                                                            AS PRIV_NORAML_AM1,      
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') = ''AND Q3.TRANSFER_TYPE IN ('1001','1002','1003') AND Q1.CLASSIFY_CODE = '20' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)   
				                                                            AS PRIV_ATTENTION_AM1,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') = ''AND Q3.TRANSFER_TYPE IN ('1001','1002','1003') AND Q1.CLASSIFY_CODE = '30' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)   
				                                                            AS PRIV_SECONDARY_AM1,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') = ''AND Q3.TRANSFER_TYPE IN ('1001','1002','1003') AND Q1.CLASSIFY_CODE = '40' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)   
				                                                            AS PRIV_SUSPICIOUS_AM1,  
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') = ''AND Q3.TRANSFER_TYPE IN ('1001','1002','1003') AND Q1.CLASSIFY_CODE = '50' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)   
				                                                            AS PRIV_LOSE_AM1,        
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '10' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_NORAML_AM2,      
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '20' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_ATTENTION_AM2,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '30' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_SECONDARY_AM2,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '40' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_SUSPICIOUS_AM2,  
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND Q3.TRANSFER_TYPE = '20' AND Q1.CLASSIFY_CODE = '50' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_LOSE_AM2,        
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '10' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)   
				                                                            AS PRIV_NORAML_AM3,      
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '20' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_ATTENTION_AM3,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '30' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_SECONDARY_AM3,   
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '40' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_SUSPICIOUS_AM3,  
        SUM(CASE WHEN Q1.BUSI_TYPE = '20' AND NVL(Q4.CONTRACT_NO,'') <> '' AND Q1.CLASSIFY_CODE = '50' 
		         THEN NVL(Q1.RECIVE_NUM,0) * COALESCE(T994.Cash_Fx_Buy_Price,100)/100 ELSE 0 END)    
				                                                            AS PRIV_LOSE_AM3         
    FROM DWMART_SAI.M_FACT_BAD_RECIVE_DETAIL Q1                     
   INNER JOIN (SELECT A.CONTRACT_NO,
	                  A.BUSI_TYPE,
                      A.TRANSFER_TYPE,
	                  A.TRANSFER_APPROVE_DATE,
	                  A.RETURN_APPROVE_DATE,
	                  ROW_NUMBER() OVER(PARTITION BY A.ORISYS_TRANSFER_SERIALNO,A.CONTRACT_NO ORDER BY A.TRANSFER_APPROVE_DATE DESC) RN
	             FROM DWMART_SAI.M_FACT_TRANSFER_DUE_BILL A
	            WHERE A.TRANSFER_STATE ='20'                                
			 ) Q3                                                       
       ON Q1.CONTRACT_NO = Q3.CONTRACT_NO                                     
	  AND Q1.RECIVE_DATE >= Q3.TRANSFER_APPROVE_DATE
	  AND Q1.RECIVE_DATE < Q3.RETURN_APPROVE_DATE
	  AND Q1.BUSI_TYPE = Q3.BUSI_TYPE
	  AND Q3.RN=1	
    LEFT JOIN (SELECT 
                   T1.CONTRACT_NO AS CONTRACT_NO, 
                   T1.BUSI_TYPE   AS BUSI_TYPE      
               FROM DWMART_SAI.M_FACT_WRITEOFF_DETAIL T1
               GROUP BY T1.CONTRACT_NO,T1.BUSI_TYPE) Q4            
           ON Q1.CONTRACT_NO = Q4.CONTRACT_NO
          AND Q1.BUSI_TYPE = Q4.BUSI_TYPE
    LEFT JOIN DWMART_SAI.T99_EXCHANGE_RATE_PARAM_H T994             
           ON Q1.CURRENCY=T994.CURRENCY_CD
          AND T994.DAILY_LAST_EX_RATE_IND = '1'      
          AND T994.EFFECTIVE_DT = Q1.RECIVE_DATE
    WHERE  Q1.HANDLE_TYPE IN ('01','02')   
    GROUP BY Q1.MANAGE_ORG_NO,Q1.RECIVE_DATE) A1
    FULL JOIN(
    
    SELECT 
        B.MANAGE_ORG_NO              AS ORG_NO,                  
        C.RECIVE_DATE               AS RECIVE_DATE,          
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='01' AND B.CLASSIFY_CODE='10' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_NORAML_AM4,     
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='01' AND B.CLASSIFY_CODE='20' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_ATTENTION_AM4,  
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='01' AND B.CLASSIFY_CODE='30' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_SECONDARY_AM4,  
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='01' AND B.CLASSIFY_CODE='40' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_SUSPICIOUS_AM4, 
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='01' AND B.CLASSIFY_CODE='50' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_LOSE_AM4,       
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='02' AND B.CLASSIFY_CODE='10' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_NORAML_AM5,     
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='02' AND B.CLASSIFY_CODE='20' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_ATTENTION_AM5,  
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='02' AND B.CLASSIFY_CODE='30' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_SECONDARY_AM5,  
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='02' AND B.CLASSIFY_CODE='40' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_SUSPICIOUS_AM5, 
        SUM(CASE WHEN B.BUSI_TYPE='10' AND B.STRP_CONTRACT_FLAG='02' AND B.CLASSIFY_CODE='50' THEN COALESCE(C.RECIVE_BALANCE_CNY,0)+COALESCE(C.RECIVE_IN_INTEREST,0)+COALESCE(C.RECIVE_OUT_INTEREST,0) ELSE 0 END) AS CORP_LOSE_AM5                   
    FROM DWMART_SAI.M_FACT_STRP_CONTRACT_S B 
    LEFT JOIN DWMART_SAI.M_FACT_STRP_RECIVE_DETAIL C  
           ON B.STRP_CONTRACT_NO = C.CONTRACT_NO  
    WHERE B.DATA_DATE = TO_DATE('20230828','YYYYMMDD')
    GROUP BY B.MANAGE_ORG_NO,C.RECIVE_DATE) A2
          ON A1.ORG_NO = A2.ORG_NO
         AND A1.RECIVE_DATE = A2.RECIVE_DATE
    FULL JOIN (
    
    SELECT 
        W2.ASSET_MANAGE_ORG_NO                    AS ORG_NO,               
        W1.DISPOSAL_DATE                          AS RECIVE_DATE,          
        SUM(NVL(W1.REALIZED_BALANCE_CNY,0))       AS ASSET_HANDL_PRICE     
    FROM DWMART_SAI.M_FACT_ASSET_DISPOSAL_INFO W1        
    LEFT JOIN DWMART_SAI.M_FACT_ASSET_INFO_S W2          
           ON W1.ASSET_NO = W2.ASSET_NO
          AND W1.BUSI_TYPE = W2.BUSI_TYPE
          AND W2.DATA_DATE = TO_DATE('20230828','YYYYMMDD')
    GROUP BY W2.ASSET_MANAGE_ORG_NO,W1.DISPOSAL_DATE) A3
         ON (A1.ORG_NO = A3.ORG_NO AND A1.RECIVE_DATE = A3.RECIVE_DATE)
         OR (A2.ORG_NO = A3.ORG_NO AND A2.RECIVE_DATE = A3.RECIVE_DATE);

问题根因：sys_guid()为colatiel函数  813版本后在对full join执行重写check时，会在contain_volatile_functions中对整个查询树做检查，若发现volatile函数的存在则会返回不支持重写的标志

【规避方法】

1 .拆分full join or的形式改成left join+right join 

2 针对情景二的问题可以采用临时表的方式进行查询，对sys_guid和full join 进行分割例如

with temp1 as (
select * from public.sales1 a1 left join public.sales2 t1 on a1.item = t1.item full join (select * from public.sales1) a2 on a1.item = a2.item or t1.item =a2.item
) select sys_guid() as id, * from temp1;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入