WITH CLAUSE use

Hi,
Would you please help me out with WITH CLAUSE use in Oracle 9i?
I need to insert the result set from the query which uses WITH CLAUSE.

Sample:
-----------------------------------------------------
    with
    Credit as
    (
        select      sum( Amount )           as Credit_Amount
        from        tmpTransactionMaster
        where       ACCOUNT_TYPE = 'CREDIT'
    ),
    Debit as
    (
        select      sum( Amount )           as Debit_Amount
        from        tmpTransactionMaster
        where       ACCOUNT_TYPE = 'DEBIT'
    )
    insert into tmpReconciliationLog ----------- ?????????????????????
    select  'Total Credit - Total Debit)'       as Formula,
            Credit_Amount,
            Debit_Amount,
            Credit_Amount - Debit_Amount    as Balance
    from Credit, Debit;
---------------------------------------------------------------
I have tried to use the same logic as in SQL Server 2005 but Oracle generated an error:
15:43:04  [WITH - 0 row(s), 0.031 secs]  [Error Code: 928, SQL State: 42000]  ORA-00928: missing SELECT keyword
Thank you for any help.
Regards.


GoodNameAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in oracle indeed, you cannot using INSERT when using the WITH statement.
you will need to create a view with the WITH statement, and insert from that...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.