ScientiaEtVeritas ScientiaEtVeritas - 3 months ago 9
SQL Question

Invalid Column after Renaming

I have a nested SQL statement. In the inner SQL statement I rename a column like this:

b."MANDT" b_MANDT
. In the outer statement I try something similar:
a."b_MANDT" a_b_MANDT
.

But I'm getting the error message that this is an invalid column name, why?

SAP DBTech JDBC: [260]: invalid column name: A.b_MANDT: line 1 col 43 (at pos 42)


Original SQL Statement:

SELECT a."MANDT", a."VBELN", a."POSNR", a."b_MANDT" a_b_MANDT, a."b_VBELN" a_b_VBELN, a."VPOSN" a_VPOSN, b."MANDT" b_MANDT, b."VBELN" b_VBELN, b."VPOSN"
FROM (
SELECT a."MANDT", a."VBELN", a."POSNR", b."MANDT" b_MANDT, b."VBELN" b_VBELN, b."VPOSN"
FROM "SAP_ECC".VBAP a
LEFT JOIN "SAP_ECC".VEDA b ON a.MANDT = b.MANDT AND a.VBELN = b.VBELN AND a.POSNR = b.VPOSN
) a
LEFT JOIN "SAP_ECC".VEDA b ON a.MANDT = b.MANDT AND a.VBELN = b.VBELN AND a.VPOSN = b.VPOSN

Answer

Try changing b."MANDT" b_MANDT to b."MANDT" "b_MANDT". Not sure what DB you are using, but typically, unless quoted, the alias defaults to upper-case yet you are trying to reference it as mixed-case.