?
Solved

WITH CLAUSE use

Posted on 2007-07-20
1
Medium Priority
?
1,214 Views
Last Modified: 2013-12-19
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.


0
Comment
Question by:GoodName
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19534557
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question