Solved

Coldfusion MINUS query ??

Posted on 2012-04-02
3
371 Views
Last Modified: 2012-06-27
<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??
0
Comment
Question by:diecasthft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37798882
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
                 )
0
 

Author Closing Comment

by:diecasthft
ID: 37800579
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!!!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37801622
Welcome :)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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