Create View Backend

Hi Douglas,

we use the same logic so far with no issues.

Have you tried to make use of UNSEGMENTED ALL NODES as described here:

https://docs.celonis.com/en/sql-troubleshooting.html#UUID-c15ca78f-b40c-735a-3ce0-2d74e9609b92_section-idm45897328432592342416797104

BR

Dennis

Hello Dennis, I tried to do it this way, I don't know if it's correct, because it still shows an error

CREATE VIEW "BKPF_UNION" AS (

  SELECT * FROM "BKPF_BSAK"

  UNION ALL

  SELECT * FROM "BKPF_BSIK"

);

DROP TABLE IF EXISTS "BKPF" CASCADE;

CREATE TABLE "BKPF" AS (

  SELECT

    ROW_NUMBER() OVER(PARTITION BY "MANDT", "BUKRS", "BELNR", "GJAHR"

          ORDER BY "MANDT", "BUKRS", "BELNR", "GJAHR", "_CELONIS_CHANGE_DATE" DESC) AS NUM,

    "BKPF".*

  FROM

    "BKPF_UNION" AS BKPF

) UNSEGMENTED ALL NODES;

DELETE FROM "BKPF" WHERE NUM > 1;

ALTER TABLE "BKPF" DROP COLUMN NUM;

DROP VIEW IF EXISTS "BKPF_UNION" CASCADE;

error : Execution error: ROLLBACK: DROP failed due to dependencies Detail: Projection "5cd18efc-518d-45db-a460-69694e102092_52bc3424-cada-4219-82cc-dadc6db9d1cc".BKPF_super has column NUM as part of its segmentation expression

Hi douglas,

have you tried to execute the queries one by one.

During which step exactly the error message comes up?

BR

Dennis

oops sorry I forgot to mention, I ran it line by line and the error shows this:Execution error:

ALTER TABLE "BKPF" DROP COLUMN NUM;

ROLLBACK: DROP failed due to dependencies Detail: Projection "5cd18efc-518d-45db-a460-69694e102092_52bc3424-cada-4219-82cc-dadc6db9d1cc".BKPF_super has column NUM as part of its segmentation expression

Please try it with adding CASCADE also to your ALTER TABLE statement:

ALTER TABLE "BKPF" DROP COLUMN NUM CASCADE;

See: Dropping Table Columns (vertica.com)

If that is not working please try to change order of:

  ROW_NUMBER() OVER(PARTITION BY "MANDT", "BUKRS", "BELNR", "GJAHR"

     ORDER BY "MANDT", "BUKRS", "BELNR", "GJAHR", "_CELONIS_CHANGE_DATE" DESC) AS NUM,

"BKPF".*

So that it looks like:

CREATE TABLE "BKPF" AS (

 SELECT

  "BKPF".*

  ,ROW_NUMBER() OVER(PARTITION BY "MANDT", "BUKRS", "BELNR", "GJAHR"

     ORDER BY "MANDT", "BUKRS", "BELNR", "GJAHR", "_CELONIS_CHANGE_DATE" DESC) AS NUM

 FROM

  "BKPF_UNION" AS BKPF

) 

That is just an idea as I read that Vertica will use the first 8 columns for creation of its super projection.

BR

Dennis

INCREDIBLE !!! Dennis, thank you again very much. I'm learning a lot from you. Your second suggestion worked.

Have a nice day !

Great to hear :)

Have a nice weekend.

BR

Dennis