Help needed converting SQL Server script

[Ertan Küçükoglu]

Hello,

I got help from a forum building the SQL Server script below.

begin transaction;

begin try
    select &marketplace as marketplace, orderno, orderitemid as orderitemno, uuid, oid, invoicedate, year(current_timestamp) "year", totalprice, invoiceemail, sendername, xml, efatura, earsiv, ordercancelledafterinvoicing as ordercancelled, current_timestamp "archivedate"
    into #itemstobearchived
    from orders1
    where invoiced = '1' and uploaded = '1';

    delete o
    output a.marketplace, a.orderno, a.orderitemno, a.uuid, a.oid, a.invoicedate, a.year, deleted.totalprice, deleted.invoiceemail, deleted.sendername, :entegrator, a.efatura, a.earsiv, deleted.xml, '0', a.ordercancelled, a.archivedate into einvoicearchive
    from orders1 o
    join #itemstobearchived a on a.orderno = o.orderno and a.orderitemno = o.orderitemid;
end try
begin catch
    rollback transaction;
end catch

if @@TRANCOUNT > 0
    commit transaction;

begin try
    drop table #itemstobearchived;
end try
begin catch
end catch

Now, I need to convert this script to do the same on a FirebirdSQL v4.0.

What I need to do is basically select "invoiced" and "uploaded" records from the "ORDERS" table, insert them into the "ARCHIVE" table and delete only those inserted records from the "ORDERS" table. This operation is manually run by the user from time to time. Since there are more than one orders table (one for each marketplace application supports) I did not keep the temporary table created because column names are changing in SQL Server version.

I am thinking of using CTE for selecting records for insertion. But, I am not sure if I can use the same CTE for deleting records to get rid of creating a temporary table.

I appreciate any help.

Thanks & Regards,

Ertan

[Karol Bieniaszewski]

Hi

 

Delete, insert and update, support returning clause, so this should be simply possible

I have never tried mix them in one sql, if not working, you should look on execute block instead.

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4ycrk1F9HKMUK8xR6GeR4PiDrx19wrZwJcbM5emc30WPBA%40mail.gmail.com.