ORA-00903: invalid table name error of multiple line spark sql string using triple-quotes

Question

I have a spark SQL string like this:

val sql_1= """SELECT TG.TG_ID,
                      |       TG.ORG_NO,
                      |       TO_CHAR(VC.DATA_DATE, 'yyyymmdd') AS DATA_DATE,
                      |       EDP.METER_ID,
                      |       EDP.CONS_SORT,
                      |       EDP.WIRING_MODE,
                      |       VC.PHASE_FLAG,
                      |       VC.U1,
                      |       VC.U2,
                      |       VC.U3,
                      |       VC.U4,
                      |       VC.U5,
                      |       VC.U6,
                      |       VC.U7,
                      |       VC.U8,
                      |       VC.U9,
                      |       VC.U10,
                      |       VC.U11,
                      |       VC.U12,
                      |       VC.U13,
                      |       VC.U14,
                      |       VC.U15,
                      |       VC.U16,
                      |       VC.U17,
                      |       VC.U18,
                      |       VC.U19,
                      |       VC.U20,
                      |       VC.U21,
                      |       VC.U22,
                      |       VC.U23,
                      |       VC.U24,
                      |       VC.U25,
                      |       VC.U26,
                      |       VC.U27,
                      |       VC.U28,
                      |       VC.U29,
                      |       VC.U30,
                      |       VC.U31,
                      |       VC.U32,
                      |       VC.U33,
                      |       VC.U34,
                      |       VC.U35,
                      |       VC.U36,
                      |       VC.U37,
                      |       VC.U38,
                      |       VC.U39,
                      |       VC.U40,
                      |       VC.U41,
                      |       VC.U42,
                      |       VC.U43,
                      |       VC.U44,
                      |       VC.U45,
                      |       VC.U46,
                      |       VC.U47,
                      |       VC.U48,
                      |       VC.U49,
                      |       VC.U50,
                      |       VC.U51,
                      |       VC.U52,
                      |       VC.U53,
                      |       VC.U54,
                      |       VC.U55,
                      |       VC.U56,
                      |       VC.U57,
                      |       VC.U58,
                      |       VC.U59,
                      |       VC.U60,
                      |       VC.U61,
                      |       VC.U62,
                      |       VC.U63,
                      |       VC.U64,
                      |       VC.U65,
                      |       VC.U66,
                      |       VC.U67,
                      |       VC.U68,
                      |       VC.U69,
                      |       VC.U70,
                      |       VC.U71,
                      |       VC.U72,
                      |       VC.U73,
                      |       VC.U74,
                      |       VC.U75,
                      |       VC.U76,
                      |       VC.U77,
                      |       VC.U78,
                      |       VC.U79,
                      |       VC.U80,
                      |       VC.U81,
                      |       VC.U82,
                      |       VC.U83,
                      |       VC.U84,
                      |       VC.U85,
                      |       VC.U86,
                      |       VC.U87,
                      |       VC.U88,
                      |       VC.U89,
                      |       VC.U90,
                      |       VC.U91,
                      |       VC.U92,
                      |       VC.U93,
                      |       VC.U94,
                      |       VC.U95,
                      |       VC.U96
                      |  FROM E_MP_VOL_CURVE VC
                      | INNER JOIN E_DATA_MP EDP
                      |    ON VC.ID = EDP.ID
                      | INNER JOIN G_TG TG
                      |    ON EDP.TG_ID = TG.TG_ID
                      | WHERE TG.PUB_PRIV_FLAG = '01'
                      |   AND VC.DATA_DATE >= TO_DATE('20200801', 'yyyymmdd')
                      |   AND VC.DATA_DATE <= TO_DATE('20200831', 'yyyymmdd')
                      |   AND EDP.CONS_SORT NOT LIKE '06'
                      |   AND VC.PHASE_FLAG IN (1, 2, 3)
                      |   AND TG.ORG_NO LIKE '134010902'
                      |   --AND TG.TG_ID IN (1646178, 1646179)
                      | ORDER BY TG.ORG_NO, TG.TG_ID, EDP.CONS_SORT, VC.DATA_DATE, VC.PHASE_FLAG""".stripMargin

, which will give me the "ORA-00903: invalid table name" error. However, the original SQL string, namely the following:

SELECT TG.TG_ID,
       TG.ORG_NO,
       TO_CHAR(VC.DATA_DATE, 'yyyymmdd') AS DATA_DATE,
       EDP.METER_ID,
       EDP.CONS_SORT,
       EDP.WIRING_MODE,
       VC.PHASE_FLAG,
       VC.U1,
       VC.U2,
       VC.U3,
       VC.U4,
       VC.U5,
       VC.U6,
       VC.U7,
       VC.U8,
       VC.U9,
       VC.U10,
       VC.U11,
       VC.U12,
       VC.U13,
       VC.U14,
       VC.U15,
       VC.U16,
       VC.U17,
       VC.U18,
       VC.U19,
       VC.U20,
       VC.U21,
       VC.U22,
       VC.U23,
       VC.U24,
       VC.U25,
       VC.U26,
       VC.U27,
       VC.U28,
       VC.U29,
       VC.U30,
       VC.U31,
       VC.U32,
       VC.U33,
       VC.U34,
       VC.U35,
       VC.U36,
       VC.U37,
       VC.U38,
       VC.U39,
       VC.U40,
       VC.U41,
       VC.U42,
       VC.U43,
       VC.U44,
       VC.U45,
       VC.U46,
       VC.U47,
       VC.U48,
       VC.U49,
       VC.U50,
       VC.U51,
       VC.U52,
       VC.U53,
       VC.U54,
       VC.U55,
       VC.U56,
       VC.U57,
       VC.U58,
       VC.U59,
       VC.U60,
       VC.U61,
       VC.U62,
       VC.U63,
       VC.U64,
       VC.U65,
       VC.U66,
       VC.U67,
       VC.U68,
       VC.U69,
       VC.U70,
       VC.U71,
       VC.U72,
       VC.U73,
       VC.U74,
       VC.U75,
       VC.U76,
       VC.U77,
       VC.U78,
       VC.U79,
       VC.U80,
       VC.U81,
       VC.U82,
       VC.U83,
       VC.U84,
       VC.U85,
       VC.U86,
       VC.U87,
       VC.U88,
       VC.U89,
       VC.U90,
       VC.U91,
       VC.U92,
       VC.U93,
       VC.U94,
       VC.U95,
       VC.U96
  FROM E_MP_VOL_CURVE VC
 INNER JOIN E_DATA_MP EDP
    ON VC.ID = EDP.ID
 INNER JOIN G_TG TG
    ON EDP.TG_ID = TG.TG_ID
 WHERE TG.PUB_PRIV_FLAG = '01'
   AND VC.DATA_DATE >= TO_DATE('20200801', 'yyyymmdd')
   AND VC.DATA_DATE <= TO_DATE('20200831', 'yyyymmdd')
   AND EDP.CONS_SORT NOT LIKE '06'
   AND VC.PHASE_FLAG IN (1, 2, 3)
   AND TG.ORG_NO LIKE '134010902'
   --AND TG.TG_ID IN (1646178, 1646179)
 ORDER BY TG.ORG_NO, TG.TG_ID, EDP.CONS_SORT, VC.DATA_DATE, VC.PHASE_FLAG

works fine in Navicat. The triple-quotes syntax comes from this question(How to execute multi line sql in spark sql). Any suggestion is appreciated!

Does this problem not occur with a smaller SQL? What if you only select one field from one table? Or two tables? — Scratte
– Scratte, Commented Jan 15, 2021 at 3:45
No, "select * from E_MP_VOL_CURVE" doesn't give any error, and select only one field also works. But business logic requires me to select multiple fields from multiple tables. — user9875189
– user9875189, Commented Jan 15, 2021 at 5:40
I think if would probably be best if you can identify the smallest SQL that still gives you the error. — Scratte
– Scratte, Commented Jan 15, 2021 at 5:43
now I use the + sign to concatenate the SQL string and it works — user9875189
– user9875189, Commented Jan 15, 2021 at 7:01

user9875189 · Accepted Answer · 2021-01-15 07:03:07Z

Now use the following syntax and it works:

val sql_user= s"(SELECT TG.TG_ID, TG.ORG_NO, TO_CHAR(VC.DATA_DATE, 'yyyymmdd') AS DATA_DATE, EDP.METER_ID, EDP.CONS_SORT, EDP.WIRING_MODE," +
      s"VC.PHASE_FLAG,VC.U1, VC.U2, VC.U3, VC.U4, VC.U5, VC.U6, VC.U7, VC.U8, VC.U9, VC.U10, VC.U11, VC.U12,VC.U13, VC.U14, VC.U15, VC.U16, VC.U17," +
      s"VC.U18, VC.U19, VC.U20, VC.U21, VC.U22, VC.U23, VC.U24, VC.U25, VC.U26, VC.U27, VC.U28, VC.U29, VC.U30, VC.U31, VC.U32, VC.U33,VC.U34, VC.U35, " +
      s"VC.U36, VC.U37, VC.U38, VC.U39, VC.U40, VC.U41, VC.U42, VC.U43, VC.U44, VC.U45, VC.U46, VC.U47, VC.U48, VC.U49, VC.U50, VC.U51, VC.U52, VC.U53, " +
      s"VC.U54,VC.U55, VC.U56, VC.U57, VC.U58, VC.U59, VC.U60, VC.U61, VC.U62, VC.U63, VC.U64, VC.U65, VC.U66, VC.U67, VC.U68, VC.U69, VC.U70, VC.U71, " +
      s"VC.U72, VC.U73, VC.U74, VC.U75,VC.U76, VC.U77, VC.U78, VC.U79, VC.U80, VC.U81, VC.U82, VC.U83, VC.U84, VC.U85, VC.U86, VC.U87, VC.U88, VC.U89, " +
      s"VC.U90, VC.U91, VC.U92, VC.U93, VC.U94, VC.U95, VC.U96 FROM E_MP_VOL_CURVE VC INNER JOIN E_DATA_MP EDP ON VC.ID = EDP.ID INNER JOIN G_TG TG " +
      s"ON EDP.TG_ID = TG.TG_ID WHERE TG.PUB_PRIV_FLAG = '01'AND VC.DATA_DATE >= TO_DATE('20200801', 'yyyymmdd') AND VC.DATA_DATE <= TO_DATE('20200831', 'yyyymmdd')" +
      s"AND EDP.CONS_SORT NOT LIKE '06' AND VC.PHASE_FLAG IN (1, 2, 3) AND TG.ORG_NO LIKE '134010902' ORDER BY TG.ORG_NO, TG.TG_ID, EDP.CONS_SORT, VC.DATA_DATE, VC.PHASE_FLAG)"

Collectives™ on Stack Overflow

ORA-00903: invalid table name error of multiple line spark sql string using triple-quotes

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related