Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: MERGE statement must be terminated by a semi-colon (;).

  1. #1
    Join Date
    Dec 2011
    Posts
    124

    Default MERGE statement must be terminated by a semi-colon (;).

    Hi ,

    while we are trying to run below query in ms sql server using Kettle 3.2 version, getting error as MERGE statement must be terminated by a semi-colon (; but same query was excuted successfully in Oracle database using Kettle 3.2 version.

    can any one please suggest us how can we successfully run below query on ms sql server database using kettle 3.2 ?

    MERGE INTO lodowner.claim_fact_load CF
    USING (select distinct claim_key, COALESCE(EX.CONV_RATE,1) CRNCY_RATE
    from lodowner.claim_fact_load CFL
    LEFT join shwowner.CURRENCY CRNCY ON CFL.local_crncy_key = CRNCY.CRNCY_KEY
    LEFT join STGOWNER.WARRANTY_DATE D on D.DATE_KEY = CFL.CLM_APPR_DATE_KEY
    LEFT JOIN SHWOWNER.EXCHANGERATEVIEW EX ON COALESCE (D.DATE_VALUE,CURRENT_TIMESTAMP) BETWEEN EX.BGN_DT AND EX.END_DT
    AND EX.CRNCY_KEY=local_crncy_key
    AND EX.TARGET_CRNCY_CD = 'USD'
    ) CUR
    ON (CF.CLAIM_KEY = CUR.CLAIM_KEY)
    WHEN MATCHED THEN
    UPDATE SET
    PART_ITEM_UNITCST_CCRNCY_AMT =(PART_ITEM_UNITCST_NCRNCY_AMT * CUR.crncy_rate)
    ,OTH_ITEM_REQ_CCRNCY_AMT=(OTH_ITEM_REQ_NCRNCY_AMT * CUR.crncy_rate)
    ,PART_ITEM_ORG_CCRNCY_AMT =(PART_ITEM_ORG_NCRNCY_AMT * CUR.crncy_rate)
    ;



    Thanks

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    It could be that MS SQL server and Oracle use a different syntax.
    Try to execute that same statement on MS SQL server using some other software and see if it works.

    Mick

  3. #3

    Default

    What kind of step is used to execute? When using the "Execute SQL script" step, tick the "execute as single statement" box. Otherwise, pdi will split your script by the "; -> seperator" and execute it step by step, resulting that the required ; will not be submittet to the dbms.

    I think that are are differences between the used dbms systems - and oracle is more tollerant in this case.

  4. #4
    Join Date
    Dec 2011
    Posts
    124

    Default

    Thanks for your reply.

    we are using kettle 3.2 version, yes we are using Execute SQL script step, in this version we don't find "execute as single statement" box. can you please send snap shot or please send path where we can find ?.

    what is the alternate step or logic for "execute as single statement" in kettle 3.2 version. please suggest us.
    Last edited by raghavavundavalli; 09-18-2012 at 11:21 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.