BigQuery error: Query column 5 has type STRUCT<IndexAbbr STRING, RIC STRING, TradingCurr STRING, ...> which cannot be inserted into column RecappedWeight, which has type NUMERIC at SPCalcConsWgt

 

在使用BigQuery过程中，遇到一个error

CALL `hsbc-11864627-hsirnd-dev.POC_HK_DIPD_APP.SPCalcConsWgt`('H40-2', '');

 

 

 说的是结构体(STRUCT) 不能作为值 insert 到 数据类型为 NUMERIC 的字段 RecappedWeight 中

 

CREATE OR REPLACE PROCEDURE `hsbc-11864627-hsirnd-dev.POC_HK_DIPD_APP`.SP06_CalcConsWeight(pIndexAbbr STRING,
                                            pParam STRING)
BEGIN
    
    --- Create normal daily weights data set
    
    CREATE TEMPORARY TABLE Weight 
    (
        IndexAbbr STRING,
        RIC STRING,
        TradingCurr STRING,
        TradingDate DATE,
        Weight NUMERIC
    );
    
    INSERT INTO Weight
    SELECT r.Backtest_IndexAbbr AS IndexAbbr,
        r.RIC,
        r.TradingCurr,
        r.TradingDate,
        r.RecappedWeight * (t.ClosePriceSpinoffAdj / r.ClosePriceSpinoffAdj) / r.IndexMV AS Weight
    FROM ...(Assembly data)
    

    DELETE FROM `hsbc-11864627-hsirnd-dev.POC_HK_DIPD_IDM_DB01.idxBacktestDailyWeight` WHERE IndexAbbr = pIndexAbbr;
    
    INSERT INTO `hsbc-11864627-hsirnd-dev.POC_HK_DIPD_IDM_DB01.idxBacktestDailyWeight`
    SELECT  
        IndexAbbr,
        RIC,
        TradingCurr,
        TradingDate,
        Weight,
        CURRENT_TIMESTAMP(),
        'POC',
        CURRENT_TIMESTAMP(),
        'POC'
    FROM Weight;
    
END

 

问题：

　　column 5 对应的字段是Weight，Weight是一个 NUMERIC 类型的字段，为什么会莫名其妙的变成了结构体(STRUCT)类型？

原因：字段 Weight 与 临时表 Weight 同名，因此编辑器把 SELECT 语句中的 Weight 当成是table(我们想当然得认为SELECT语句中的都是字段)，所以 Weight 就莫名其妙的变成了结构体(STRUCT)

解决：将临时表的表名换个名字，或者用 “表名.字段” 的形式。

如：

    SELECT  
        IndexAbbr,
        RIC,
        TradingCurr,
        TradingDate,
        Weight.Weight
    FROM Weight;