Solved

Coldfusion MINUS query ??

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

840 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