troubleshooting Question

WITH CLAUSE use

Avatar of GoodName
GoodName asked on
Oracle Database
1 Comment1 Solution1496 ViewsLast Modified:
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.


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros