Why can't find column from original relation's alias unless its existence

Question

I have this relation :

S10 = FOREACH E1EEFRE GENERATE  

CD_SI AS CD_SI,
IDT_ETT_CTR AS IDT_ETT_CTR,
CD_EFS AS CD_EFS,
(BigDecimal) null AS MT_DSP,
(BigDecimal) null AS MT_NAL,
(BigDecimal) null AS MT_ENC_MOY,
(BigDecimal) null AS MT_UTI,
(BigDecimal) null AS MT_ITT_M,
 MT_ENMO  AS MT_ENMO;

and

S5 = FOREACH E1EEFAU  GENERATE  
    CD_SI             AS CD_SI,
    IDT_ETT_CTR       AS IDT_ETT_CTR,
    CD_EFS            AS CD_EFS,
    MT_DSP AS MT_DSP,
    MT_NAL AS MT_NAL,
    MT_ENC_MOY  AS MT_ENC_MOY,
    MT_ENC_FIN_PER AS MT_UTI,
   'EEFAU' AS CD_ETT_ORI,
   MT_DSP AS MT_DSP_CVE,
   MT_NAL AS MT_NAL_CVE,
  (BigDecimal) null AS MT_ENC_MOY_CVE,
   MT_IMP AS MT_IMP,
   MT_PROR AS MT_PROR,
   MT_DEM AS MT_DEM,
  (BigDecimal) null AS MT_ITT_M;

Now I want to generate the final entity where MT_ENC_EFF and MT_NAL_LIG depends on S5 and S10 :

S26 = UNION S19, S22, S21;

S27 = FOREACH S26 GENERATE 

    '$CD_TY_TT'                 AS CD_TY_TT,
    '$DA_TT'                    AS DA_TT,
    '$A_ARR'                    AS A_ARR,
    '$M_ARR'                    AS M_ARR,
    '$CD_ETS'                   AS CD_ETS,
        $0                                       AS CD_SI,  
        $1                                  AS IDT_ETT_CTR,
        $2                                       AS CD_EFS,
        $3                                       AS MT_DSP,
        $4                                       AS MT_NAL,
        $5                                       AS MT_ENC_MOY,
        S10::MT_ENMO + S5::MT_ENC_MOY              AS MT_ENC_EFF,
        $6                                      AS MT_IMP,
        $7                                      AS MT_PROR,
        $8                                      AS MT_DEM,
        $9                                      AS MT_ITT_M,
        (S6::IDT_ETT_CTR_LIG == '' ? (S6::MT_NAL_BIL + S6::MT_AUT) :99) AS MT_NAL_LIG;


STORE S27 INTO '$PathDataWorkingFile' USING CSVExcelStorage(',', 'YES_MULTILINE');

The error shown is:

Invalid field projection. Projected field [S10::MT_ENMO] does not exist.

But MT_ENMO exist in reality !

When I changed S10.MT_ENMO instead of S10::MT_ENMO

I get thi esrror in Hadoop Application manager :

xecException: ERROR 0: Scalar has more than one row in the output. 1st : (001,1708104234,01,,,,,,,,,,,,,,,,,,,,,,,,,,0.0), 2nd :(001,1715803812,01,,,,,,,,,,,,,,,,,,,,,,,,,,0.0) (common cause: "JOIN" then "FOREACH ... GENERATE foo.bar" should be "foo::bar" ) at org.apache.pig.impl.builtin.ReadScalars.exec(ReadScalars.java:122) at org.apache.pig.backend.hadoop.executionengine.physicalLayer.expressionOperators.POUserFunc.getNext(POUserFunc.java:326)

Edit: This is the output of S26

001,DQ0017751107,29,0.0,246327.35,0.0,,162234.16,0.0,0.0,0.0,,ECRFI,0.0,246327.35,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067947801,29,0.0,25217.33,0.0,,20433.19,0.0,0.0,0.0,,ECRFI,0.0,25217.33,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067947802,29,0.0,16666.67,0.0,,13496.64,0.0,0.0,0.0,,ECRFI,0.0,16666.67,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067947803,29,0.0,-16666.67,0.0,,-13496.64,0.0,0.0,0.0,,ECRFI,0.0,-16666.67,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067947804,29,0.0,25217.33,0.0,,21156.29,0.0,0.0,0.0,,ECRFI,0.0,25217.33,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067947805,29,0.0,16666.67,0.0,,13638.92,0.0,0.0,0.0,,ECRFI,0.0,16666.67,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067947806,29,0.0,-16666.67,0.0,,-13638.92,0.0,0.0,0.0,,ECRFI,0.0,-16666.67,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067947901,29,0.0,961900.0,0.0,,667228.77,0.0,0.0,0.0,,ECRFI,0.0,961900.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067948001,29,0.0,6250000.0,0.0,,4669082.64,0.0,0.0,0.0,,ECRFI,0.0,6250000.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,
001,DQ0067948101,29,0.0,1730000.0,0.0,,1314314.02,0.0,0.0,0.0,,ECRFI,0.0,1730000.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,,,,

Note

The full Pig script is here:

How can I resolve this please ?

Answer 1

The issue is that MT_ENMO is not projected in any of the relations besides S10 and your final relation S27.

• S26 consists of three relations S23, S22, and S20. None of these consist of MT_ENMO.
• S20 consists of six relations including S16. S16 also doesn't contain MT_ENMO.
• S16 is projecting data from S10, so you should add MT_ENMO here to start with. Then in S27, you should be able to get the field by projecting S16::MT_ENMO.

Example:

S16 = FOREACH S16_JOIN_LEFT GENERATE
    S10::CD_SI                      AS CD_SI,
    S10::IDT_ETT_CTR                AS IDT_ETT_CTR,
    S10::CD_EFS                     AS CD_EFS,
    S10::MT_DSP                     AS MT_DSP,
    S10::MT_NAL                     AS MT_NAL,
    S10::MT_ENC_MOY                 AS MT_ENC_MOY,
    S10::MT_UTI                     AS MT_UTI,
    S10::MT_CAP_RST_DU              AS MT_CAP_RST_DU,
    S10::MT_ITT_CRU                 AS MT_ITT_CRU,
    S10::MT_CAP_ECN_IMP             AS MT_CAP_ECN_IMP,
    S10::MT_ITT_IMP                 AS MT_ITT_IMP,
    S10::MT_DNR_ECN                 AS MT_DNR_ECN,
    S10::CD_ETT_ORI                 AS CD_ETT_ORI,
    S10::MT_DSP_CVE                 AS MT_DSP_CVE,
    S10::MT_NAL_CVE                 AS MT_NAL_CVE,
    S10::MT_ENC_MOY_CVE             AS MT_ENC_MOY_CVE,
    S10::MT_CAP_IMP_CVE             AS MT_CAP_IMP_CVE,
    S10::MT_ITT_IMP_CVE             AS MT_ITT_IMP_CVE,
    S10::MT_GLB_IMP                 AS MT_GLB_IMP,
    S10::MT_GLB_IMP_CVE             AS MT_GLB_IMP_CVE,
    S10::MT_BN_INST                 AS MT_BN_INST,
    S10::MT_BN_INST_CVE             AS MT_BN_INST_CVE,
    S10::MT_BN_NV                   AS MT_BN_NV,
    S10::MT_BN_NV_CVE               AS MT_BN_NV_CVE,
    S10::MT_IMP                     AS MT_IMP,
    S10::MT_PROR                    AS MT_PROR,
    S10::MT_DEM                     AS MT_DEM,
    S10::MT_ITT_M                   AS MT_ITT_M,
    S10::MT_ENMO                    AS MT_ENMO;