with transform1 as (

     .....
   ),
     transform2 as (
select

           F_KS_SALEENTRYGUID,
           product_code,
           product_name,
           product_desc,
           before_material_code,
           before_material_desc,
           after_material_code,
           after_material_desc,
sum(before_product_formula_weight) as before_product_formula_weight,
sum(after_product_formula_weight) as after_product_formula_weight
from transform1
group by 
F_KS_SALEENTRYGUID,product_code,product_name,product_desc,before_material_code,before_material_desc,after_material_code,after_material_desc
    )
SELECT

t1.F_KS_SALEENTRYGUID
FROM transform2 t1


问题:
在cte中使用group by后,只查询group by分组字段报错Could not find function eq, arg Int8 return 
Nullable(UInt8),
导致我在后续对transform1 进行链接查询时 无法join 分组键 
left join product t3 on t3.F_ORA_TEXT = t1.F_KS_SALEENTRYGUID
会出现报错


使用

SELECT
t1.*
FROM transform2 t1
不报错
使用

SELECT
t1.F_KS_SALEENTRYGUID, t1.before_product_formula_weight
FROM transform2 t1
不报错
使用

SELECT
F_KS_SALEENTRYGUID,product_code,product_name,product_desc,before_material_code,before_material_desc,after_material_code,after_material_desc
FROM transform2 t1
报错Could not find function eq, arg Int8 return Nullable(UInt8)




FE报错内容:
[HY000][1105] errCode = 2, detailMessage = 
(xxx.xxx.xxx.xxx)[INTERNAL_ERROR]Could not find function eq, arg Int8 return 
Nullable(UInt8)


日志:
[query] 
|Client=172.168.9.138:18945|User=user_102748|Ctl=internal|Db=|CommandType=Query|State=ERR|ErrorCode=1105|ErrorMessage=errCode
 = 2, detailMessage = (192.168.1.59)[INTERNAL_ERROR]Could not find function eq, 
arg Int8 return Nullable(UInt8) 
|Time(ms)=77|ScanBytes=0|ScanRows=0|ReturnRows=0|StmtId=2846829|QueryId=19743d799d814ac7-9781e78ee2f42355|IsQuery=true|IsNereids=true|FeIp=192.168.1.59|StmtType=SELECT|Stmt=/*
 ApplicationName=PyCharm 2024.3.5 */ WITH\r\n    relation1 AS (\r\n        
SELECT\r\n            t1.bomviewaltsuid bom_version,\r\n            t1.BOMUSAGE 
AS F_KS_SALEENTRYGUID,\r\n            t3.itemid AS parent_material_id,\r\n      
      t3.itemcode AS parent_material_code,\r\n            t3.itemname AS 
parent_material_name,\r\n            t3.itemdesc AS parent_material_desc,\r\n   
         t4.itemid AS child_material_id,\r\n            t4.itemcode AS 
child_material_code,\r\n            t4.itemname AS child_material_name,\r\n     
       t4.itemdesc AS child_material_desc,\r\n            t2.QTY AS qty,\r\n    
        t2.QTY_BASE AS qty_base,\r\n            t2.BADRATE AS loss\r\n        
FROM\r\n            CPCBASE.CPCBOMD t2\r\n            LEFT JOIN CPCBASE.CPCBOM 
t1 ON t1.bomid = t2.bomid\r\n            LEFT JOIN CPCBASE.CPCITEM t3 ON 
t1.asmid = t3.itemid\r\n            LEFT JOIN CPCBASE.CPCITEM t4 ON t2.itemid = 
t4.itemid\r\n        where\r\n            t2.QTY_BASE != 0\r\n            and 
t2.BADRATE != 100\r\n    ),\r\n    bom_path as (\r\n        SELECT\r\n          
  COALESCE(L6.bom_version, L5.bom_version, L4.bom_version, L3.bom_version, 
L2.bom_version, L1.bom_version, L0.bom_version) AS bom_version,\r\n            
COALESCE(\r\n                L6.F_KS_SALEENTRYGUID,\r\n                
L5.F_KS_SALEENTRYGUID,\r\n                L4.F_KS_SALEENTRYGUID,\r\n            
    L3.F_KS_SALEENTRYGUID,\r\n                L2.F_KS_SALEENTRYGUID,\r\n        
        L1.F_KS_SALEENTRYGUID,\r\n                L0.F_KS_SALEENTRYGUID\r\n     
       ) AS F_KS_SALEENTRYGUID,\r\n            COALESCE(\r\n                
L6.parent_material_id,\r\n                L5.parent_material_id,\r\n            
    L4.parent_material_id,\r\n                L3.parent_material_id,\r\n        
        L2.parent_material_id,\r\n                L1.parent_material_id,\r\n    
            L0.parent_material_id\r\n            ) AS parent_material_id,\r\n   
         COALESCE(\r\n                L5.parent_material_code,\r\n              
  L4.parent_material_code,\r\n                L3.parent_material_code,\r\n      
          L2.parent_material_code,\r\n                
L1.parent_material_code,\r\n                L0.parent_material_code\r\n         
   ) AS parent_material_code,\r\n            COALESCE(\r\n                
L5.parent_material_name,\r\n                L4.parent_material_name,\r\n        
        L3.parent_material_name,\r\n                
L2.parent_material_name,\r\n                L1.parent_material_name,\r\n        
        L0.parent_material_name\r\n            ) AS parent_material_name,\r\n   
         COALESCE(\r\n                L5.parent_material_desc,\r\n              
  L4.parent_material_desc,\r\n                L3.parent_material_desc,\r\n      
          L2.parent_material_desc,\r\n                
L1.parent_material_desc,\r\n                L0.parent_material_desc\r\n         
   ) AS parent_material_desc,\r\n            COALESCE(\r\n                
L5.child_material_id,\r\n                L4.child_material_id,\r\n              
  L3.child_material_id,\r\n                L2.child_material_id,\r\n            
    L1.child_material_id,\r\n                L0.child_material_id\r\n           
 ) AS child_material_id,\r\n            L0.child_material_code AS 
child_material_code,\r\n            L0.child_material_name AS 
child_material_name,\r\n            L0.child_material_desc AS 
child_material_desc,\r\n            ifnull (L0.product_formula_weight, 1) * 
ifnull (L1.product_formula_weight, 1) * ifnull (L2.product_formula_weight, 1) * 
ifnull (L3.product_formula_weight, 1) * ifnull (L4.product_formula_weight, 1) * 
ifnull (L5.product_formula_weight, 1) AS product_formula_weight,\r\n            
COALESCE(L5.cj, L4.cj, L3.cj, L2.cj, L1.cj, L0.cj) AS cj\r\n        FROM\r\n    
        (\r\n                /* 第0层 - 基础层 */\r\n                SELECT\r\n      
              bom_version,\r\n                    F_KS_SALEENTRYGUID,\r\n       
             parent_material_id,\r\n                    
parent_material_code,\r\n                    parent_material_name,\r\n          
          parent_material_desc,\r\n                    child_material_id,\r\n   
                 child_material_code,\r\n                    
child_material_name,\r\n              ... /* truncated 
audit_plugin_max_sql_length=4096 
*/|CpuTimeMS=0|ShuffleSendBytes=0|ShuffleSendRows=0|SqlHash=62503a731bd6a26c67efb6e5ab00a1bb|PeakMemoryBytes=1216|SqlDigest=|ComputeGroupName=UNKNOWN|WorkloadGroup=normal|FuzzyVariables=|ScanBytesFromLocalStorage=0|ScanBytesFromRemoteStorage=0
  





已尝试复现但未成功,全网找不到相关问题,期望得到回复解决

Reply via email to