topic Re: MV using Incorta SQL in Data & Schema Discussions

MV using Incorta SQL

srini_ch — Wed, 13 Jul 2022 21:34:26 GMT

Hi,

I tried creating an MV using Incorta SQL as Data Source, but I keep getting below error as shown below. Can you help me understand when to use this particular type in creating MV's.

INC_04030340: Materializing table failed because 109152

Thanks,

Srini

Re: MV using Incorta SQL

amit_kothari — Thu, 14 Jul 2022 04:38:25 GMT

This option is for spark sql and it can refer to incorta physical schema tables (no business schema allowed here) . You can paste your sql here and I can check . Pls also log a ticket .

Re: MV using Incorta SQL

JoeM — Wed, 27 Jul 2022 15:35:29 GMT

@srini_ch - Not sure if this applies in your case, but a materialized view column cannot reference a column in an Incorta Analyzer table or Incorta SQL table that exists in the same physical schema as the materialized view.

Re: MV using Incorta SQL

KailaT — Thu, 04 Aug 2022 12:42:49 GMT

Hi @srini_ch,

I wanted to follow up here. Were you able to get this working as expected?

Re: MV using Incorta SQL

srini_ch — Mon, 08 Aug 2022 14:20:29 GMT

Hi,

Sorry for the delayed response!! My query follows a syntax as below,

SELECT
A.X1, A.X2, B.X4, B.X5
FROM
table1 A
LEFT OUTER table2 B ON A.X1 = (B.X2||'.'||B.X3)

Error: Invalid query due to A.X1 = (B.X2||'.'||B.X3) is not a valid constraint

Thanks,

Srini

Re: MV using Incorta SQL

dylanwan — Sat, 13 Aug 2022 05:16:28 GMT

Can you try use the CONCAT() SQL function?

SELECT A.X1, A.X2, B.X4, B.X5 FROM table1 A LEFT JOIN table2 B ON A.X1 = concat(B.X2,'.',B.X3)

Reference

Spark SQL functions - concat.

Re: MV using Incorta SQL

RADSr — Mon, 15 Aug 2022 14:54:08 GMT

Did you try using concat() or "+" for your concatenation operator?