Coldfusion MINUS query ??

<CFQUERY DATASOURCE="MCA_Oracle" NAME="JONOListNoDB">
SELECT DAILY_SOMARDS.MIPR_NUMBER, DAILY_SOMARDS.JONO
FROM MCA.DAILY_SOMARDS

FULL OUTER JOIN
(SELECT MIPR_NUMBER, SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.A_COMMIT,0)) AS
      COMMITED,
      SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.AMOUNT_OBLIGATED,0)) AS OBLIGATED
      FROM MCA.BUDGET_MIPRS_TRANSACTIONS
      GROUP BY BUDGET_MIPRS_TRANSACTIONS.MIPR_NUMBER) SUBQUERY1A
      ON DAILY_SOMARDS.MIPR_NUMBER <> SUBQUERY1A.MIPR_NUMBER

WHERE DAILY_SOMARDS.MIPR_NUMBER IS NOT NULL
AND DAILY_SOMARDS.A_COMMIT <> '0.00'

MINUS

SELECT BUDGET_MIPRS_SENT.MIPR_NUMBER, BUDGET_MIPRS_SENT.JONO
FROM MCA.BUDGET_MIPRS_SENT 
FULL OUTER JOIN

(SELECT MIPR_NUMBER, SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.A_COMMIT,0)) AS
      COMMITED,
      SUM(COALESCE(BUDGET_MIPRS_TRANSACTIONS.AMOUNT_OBLIGATED,0)) AS OBLIGATED
      FROM MCA.BUDGET_MIPRS_TRANSACTIONS
      GROUP BY BUDGET_MIPRS_TRANSACTIONS.MIPR_NUMBER) SUBQUERY1
      ON BUDGET_MIPRS_SENT.MIPR_NUMBER = SUBQUERY1.MIPR_NUMBER

WHERE BUDGET_MIPRS_SENT.MIPR_NUMBER IS NOT NULL
AND BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR0'
AND BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'pending'
AND BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR0JCAR63737'
AND BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR0LLMRCARSN'
AND BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR1GFTBENGSA'
AND BUDGET_MIPRS_SENT.MIPR_NUMBER <> 'MIPR1GMCA58625'
ORDER BY 2 ASC

</CFQUERY>

Open in new window

Good Evening All!!! I was hoping to get a little help. I have a query that I'm trying to get records from a table that do not appear in another table, and I'm using the MINUS to do that. And it works to a point...and I'll try to explain what I'm doing. I have a table (DAILY_SOMARDS) where the MIPR_NUMBER is unique. There is no duplicates in the table. I have a second table called BUDGET_MIPRS_TRANSACTIONS) where the MIPR_NUMBER can be duplicated. I want the query to return the records where the MIPR_NUMBER in the DAILY_SOMARDS is not in the BUDGET_MIPRS_TRANSACTIONS table. And it works...until I add another field, like the DAILY_SOMARDS.A_COMMIT field. Since I also have to add the BUDGET_MIPRS_SENT.A_COMMIT field, or else I get the unequal columns error, I have to do a sum, so that I get all of the records for each MIPR_NUMBER in the BUDGET_MIPRS_TRANSACTIONS table. It works as designed I guess, but the query result of the MINUS will not only give me the MIPR_NUMBER that do not appear in the BUDGET_MIPRS_TRANSACTION table, but also returning values if the sum of the A_COMMIT does not equal the number in the DAILY_SOMARDS.A_COMMIT...and I don't want that...I need the A_COMMIT for the selection set, but I only want the MIPR_NUMBERS that do not appear in the BUDGET_MIPRS_TRANSACTION table...not the differences between the two. Can anyone guide me through this mess??
diecasthftAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
return the records where the MIPR_NUMBER in the DAILY_SOMARDS is not in the BUDGET_MIPRS_TRANSACTIONS table.

Disclaimer, I haven't read the query in detail but minus won't do the trick.  You need a NOT EXISTS subquery. I don't use Oracle much, but it should work the same.  Here's a basic example that would return the numbers that are in daily_somards but not in the transactions table.

    SELECT    DAILY_SOMARDS.MIPR_NUMBER, ....
    FROM     MCA.DAILY_SOMARDS
    WHERE   NOT EXISTS  (
                        <!--- isn't a matching number in the other table --->
                         SELECT  1
                         FROM    MCA.BUDGET_MIPRS_TRANSACTIONS
                         WHERE  BUDGET_MIPRS_TRANSACTIONS.MIPR_NUMBER = DAILY_SOMARDS.MIPR_NUMBER
                 )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
diecasthftAuthor Commented:
Thank You Expert!!!! That's what I needed, and it works perfectly. I was definitely going down the wrong path with this, and appreciate your help!!!
_agx_Commented:
Welcome :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Dreamweaver

From novice to tech pro — start learning today.