Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Coldfusion MINUS query ??

Posted on 2012-04-02
3
Medium Priority
?
379 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 2000 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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

721 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