🎉 🎉 RAPIDMINER 9.10 IS OUT!!! 🎉🎉

Download the latest version helping analytics teams accelerate time-to-value for streaming and IIOT use cases.

CLICK HERE TO DOWNLOAD

OUTPUT clause in not enabled when using Read Database Operator

gLearngLearn Member Posts: 5 Newbie
edited August 30 in Help
Hi,

I am trying to execute MERGE statement using Read Database operator. Not able to use OUTPUT clause in insert statement throwing syntax error. MERGE statement without OUTPUT clause is working fine in Execute SQL operator. But it is throwing error with output clause and also it doesn't have output port. , Please check the below (Merge.PNG) for the same.



Facing below error:



Thanks in advance.
Tagged:

Answers

  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 712   Unicorn
    Hi @gLearn

    you should use Execute SQL for statements that change the database. Read Database ist just for reading. 

    Regards,
    Balázs
  • gLearngLearn Member Posts: 5 Newbie

    Okay, here we need Read database, since Execute SQL is not having output port to get results.
    Basically output clause is only having issue there inside Read base.
  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 712   Unicorn
    edited August 31
    Hi!

    Which database type are you using? The error message is coming from the database, so RapidMiner can't do a lot about it. 

    I just tested with Postgres, an insert statement wrapped in a common table expression worked and returned the new data in Read Database. So it is not completely impossible. You probably need to find a variant of the statement your database accepts in the "SELECT" context. If your database can wrap write statements in a common table expression, or you can create a function or procedure in the database that makes the changes, there is a chance to change the SQL.

    This was my example query, likely PostgreSQL specific:
    
    with insertselect as (
      insert into inserttest (data)
      values ('Test val from writable CTE')
      returning *
    )
    select *
    from insertselect;
    

    Regards,
    Balázs

Sign In or Register to comment.