OUTPUT clause in not enabled when using Read Database Operator

User: "gLearn"
gLearn
New Altair Community Member
Updated by Jocelyn
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.

Find more posts tagged with

AI Studio
SQL
Read Database
Sort by:
1 - 3 of 31
    User: "BalazsBaranyRM"
    BalazsBaranyRM
    New Altair Community Member
    Hi @gLearn, 

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

    Regards,
    Balázs
    User: "gLearn"
    gLearn
    New Altair Community Member
    OP

    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.
    User: "BalazsBaranyRM"
    BalazsBaranyRM
    New Altair Community Member
    Updated by BalazsBaranyRM
    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

Quick Links