ERROR 3953: Missing FROM-clause entry for table

rvoorheesrvoorhees
in General Discussion

I have created a merge statement and am receiving the error message

 

ERROR 3953: Missing FROM-clause entry for table "AMAZON_PRODUCT_REVIEWS_TEST"

 

for the following merge

 

MERGE INTO SBM_WORKSPACE.AMAZON_PRODUCT_REVIEWS_TEST_V2

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 )

--WHERE AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE IS NOT NULL

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)

 

 

Comments

  • eli_revacheli_revach

    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 

  • rvoorheesrvoorhees

    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)

  • SruthiASruthiA Administrator

    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

  • rvoorheesrvoorhees

    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.

     

     

  • SruthiASruthiA Administrator

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

     

     

     AMAZON_PRODUCT_REVIEWS_TEST.AUDIT_INSERT_DATE  = SOURCE.AUDIT_INSERT_DATE

  • rvoorheesrvoorhees

    I just tried that and received the same error.

  • rvoorheesrvoorhees

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

  • SruthiASruthiA Administrator

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

  • rvoorheesrvoorhees

    I have the DDL files.

     

    Thank you!

  • rvoorheesrvoorhees

    Sruthia... any additional thoughts on the solution?

  • PraveshPravesh

    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);

     

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...