ERROR 3953: Missing FROM-clause entry for table

Hi ,

You need to replace all over the AMAZON_PRODUCT_REVIEWS_TEST_V2 with AMAZON_PRODUCT_REVIEWS_TEST .

I hope you will find helpful

Thanks 

So I changed it merge into AMAZON_PRODUCT_REVIEWS_TEST and get the same error.

Any thoughts?

MERGE INTO SBM_WORKSPACE.AMAZON_PRODUCT_REVIEWS_TEST

USING SBM_WORKSPACE.LATEST_AMAZON_REVIEWS_VW SOURCE

ON (AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_ID = SOURCE.Review_ID AND SOURCE.AUDIT_INSERT_DATE <> AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE )

WHEN MATCHED THEN UPDATE SET

AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_DATE = SOURCE.Review_Date
,AMAZON_PRODUCT_REVIEWS_TEST.PLATFORM = SOURCE.Platform
,AMAZON_PRODUCT_REVIEWS_TEST.PRODUCT_ID = SOURCE.Product_Id
,AMAZON_PRODUCT_REVIEWS_TEST.PRODUCT_TITLE = SOURCE.Product_Title
,AMAZON_PRODUCT_REVIEWS_TEST.RATING = SOURCE.Rating
,AMAZON_PRODUCT_REVIEWS_TEST.RATING_SCALE = SOURCE.Rating_Scale
,AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_ID = SOURCE.Review_Id
,AMAZON_PRODUCT_REVIEWS_TEST.REVIEWER = SOURCE.Reviewer
,AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_TEXT = SOURCE.Review_Text
,AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_TITLE = SOURCE.Title
,AMAZON_PRODUCT_REVIEWS_TEST.VERIFIED_PURCHASE_INDICATOR = SOURCE.Verified_Purchase_Indicator
,AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_LAST_UPDATE = SOURCE.AUDIT_INSERT_DATE

WHEN NOT MATCHED THEN INSERT
(
REVIEW_DATE
,PLATFORM
,PRODUCT_ID
,PRODUCT_TITLE
,RATING
,RATING_SCALE
,REVIEW_ID
,REVIEWER
,REVIEW_TEXT
,REVIEW_TITLE
,VERIFIED_PURCHASE_INDICATOR
, AUDIT_INSERT_DATE)

VALUES(
SOURCE.Review_Date,
SOURCE.Platform,
SOURCE."Product_ID",
SOURCE."Product_Title",
SOURCE.Rating,
SOURCE."Rating_Scale",
SOURCE."Review_ID",
SOURCE.Reviewer,
SOURCE.Review_Text,
SOURCE.Title,
SOURCE."Verified_Purchase_Indicator",
SOURCE.AUDIT_INSERT_DATE)

Hi,

    Can you change the condition in merge statement as follows and give a try

AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE<> SOURCE.AUDIT_INSERT_DATE 

Sruthi

Thanks for the reply Sruthi. I changed the condition to an = and received the same error.

ON (AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_ID = SOURCE.Review_ID AND SOURCE.AUDIT_INSERT_DATE = AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE )

I am perplexed by this.

try using destination table first and then source in second condition as follows

 AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE  = SOURCE.AUDIT_INSERT_DATE

I just tried that and received the same error.

By the way I tried alternate tables to perform a merge and received the same error message. Is this a bug?

can you share me DDL of the tables, I will try it out and let you know

I have the DDL files.

Thank you!

Sruthia... any additional thoughts on the solution?

You are getting this error because you haven't mentioned schema name inside join condition.

Change it to:

ON (SBM_WORKSPACE.AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_ID = SOURCE.Review_ID AND SOURCE.AUDIT_INSERT_DATE <> SBM_WORKSPACE.AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE )

After making this change, you should still get another error, complaining about column names in "WHEN MATCHED THEN UPDATE SET" block. Change fully qualified name access to only column name (for table AMAZON_PRODUCT_REVIEWS_TEST) and you should be fine. Documentation doesn't mention this (perhaps a doc bug), however Vertica doesn't seem to like Alias or table name for target columns in WHEN MATCHED THEN UPDATE SET block.

Statement given below should work fine:

MERGE INTO SBM_WORKSPACE.AMAZON_PRODUCT_REVIEWS_TEST
USING SBM_WORKSPACE.LATEST_AMAZON_REVIEWS_VW SOURCE
ON (SBM_WORKSPACE.AMAZON_PRODUCT_REVIEWS_TEST.REVIEW_ID = SOURCE.Review_ID AND SOURCE.AUDIT_INSERT_DATE <> SBM_WORKSPACE.AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE )

WHEN MATCHED THEN UPDATE SET

REVIEW_DATE = SOURCE.Review_Date
,PLATFORM = SOURCE.Platform
,PRODUCT_ID = SOURCE.Product_Id
,PRODUCT_TITLE = SOURCE.Product_Title
,RATING = SOURCE.Rating
,RATING_SCALE = SOURCE.Rating_Scale
,REVIEW_ID = SOURCE.Review_Id
,REVIEWER = SOURCE.Reviewer
,REVIEW_TEXT = SOURCE.Review_Text
,REVIEW_TITLE = SOURCE.Title
,VERIFIED_PURCHASE_INDICATOR = SOURCE.Verified_Purchase_Indicator
,AUDIT_LAST_UPDATE = SOURCE.AUDIT_INSERT_DATE

WHEN NOT MATCHED THEN INSERT
(
REVIEW_DATE
,PLATFORM
,PRODUCT_ID
,PRODUCT_TITLE
,RATING
,RATING_SCALE
,REVIEW_ID
,REVIEWER
,REVIEW_TEXT
,REVIEW_TITLE
,VERIFIED_PURCHASE_INDICATOR
, AUDIT_INSERT_DATE)
VALUES(
SOURCE.Review_Date,
SOURCE.Platform,
SOURCE."Product_ID",
SOURCE."Product_Title",
SOURCE.Rating,
SOURCE."Rating_Scale",
SOURCE."Review_ID",
SOURCE.Reviewer,
SOURCE.Review_Text,
SOURCE.Title,
SOURCE."Verified_Purchase_Indicator",
SOURCE.AUDIT_INSERT_DATE);