Solved

Coldfusion MINUS query ??

Posted on 2012-04-02
3
374 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

628 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