Solved

iSeries DB2 SQL - Query Missing Value

Posted on 2016-09-24
10
48 Views
Last Modified: 2016-09-26
I have 2 tables, tbINVOICE and tbFEE. tbFee contains fees that get populated at the end of an invoice. As a result of a setup error, I have some invoices that are missing a fee that should have been applied. The tables join together on the invoice number so if I query invoice number 012457 in tbFee I get 3 records

INVNUM    FEE       CHARGE
012457 | FUEL       | 3.58
012457 | CASE       | 2.47
012457 | SRVCHG | 4.58

What is missing on 3 weeks worth of invoices is the SRVCHG. There is a field in the tbINVOICE call ORDTYPE and if ORDTYPE = 100 then it gets the SRVCHG fee.

I need a query that selects all INVNUM with an ORDTYPE = 100 that is missing the SRVCHG.
0
Comment
Question by:Jeff
  • 6
  • 3
10 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Jeff,

From your description, it sounds like you just need to outer join the tables select the desired rows.

SELECT
FROM tbINVOICE
LEFT JOIN tbFEE
  on tbINVOICE.INVNUM = tbFEE.INVNUM
 and FEE = 'SRVCHG'
WHERE FEE is NULL

Good Luck!
Kent
0
 
LVL 1

Author Comment

by:Jeff
Comment Utility
Thank you for the response and I believe that would work if the record existed with a null SRVCHG value. In this case, however, if the fee doesn't exist then a record is not created. The Invoice does exist, but only with the fuel and case charges.

If I query 2 invoices, the first broken then the second working the result is like this:

INVNUM    FEE       CHARGE
013562 | FUEL       | 2.77
013562 | CASE       | 4.54
012457 | FUEL       | 3.58
012457 | CASE       | 2.47
012457 | SRVCHG | 4.58

If there was a record

013562 | SRVCHG | '             '

I would probably be all set.
0
 
LVL 1

Author Comment

by:Jeff
Comment Utility
I am attempting to use the NOT EXISTS predicate, but no joy.
0
 
LVL 1

Author Comment

by:Jeff
Comment Utility
Figured it out.

select tbINVOICE.INVNUM
from tbINVOICE
where  NOT EXISTS (
select tbFEE.INVNUM
from tbFEE
where tbINVOICE.INVNUM = tbFEE.INVNUM and tbFEE.FEE = 'SRVCHG')
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
Hi Jeff,

I'm glad you found another solution, but the query that I first offered does work.  

The rows in tbFEE are outer joined to tbINVOICE, not inner joined, so all of the invoice numbers will be in the joined results.  The join filter ( on tbINVOICE.INVNUM = tbFEE.INVNUM and FEE = 'SRVCHG') states that the join will select the rows in tbFEE with 'SRVCHG' in the FEE column and join them to the rows selected from tbINVOICE on the invoice number.  When there is no match, the INVNUM from tbINVOICE is still known, but all of the associated values from tbFEE will be null.  The query filter ( WHERE FEE is NULL ) selects only those rows where a match failed.

Give this one a try.  In fact, check the explain plans for both.  I think you'll find them to be almost identical.

SELECT tbINVOICE.INVNUM
FROM tbINVOICE
LEFT JOIN tbFEE
  on tbINVOICE.INVNUM = tbFEE.INVNUM
 and FEE = 'SRVCHG'
WHERE FEE is NULL

SELECT * will give a better picture of the results.


Good Luck!
Kent
0
 
LVL 1

Author Comment

by:Jeff
Comment Utility
After further review of Kent's solution, he did offer a working result that is actually easier than my own. Unfortunately, I do not know what I was doing wrong that prevented the solution from working in the first place.

Thank you, Kent!
0
 
LVL 1

Author Comment

by:Jeff
Comment Utility
Kent.

At the moment, the system is preventing me from selecting your solution as the best and awarding you the points. You will get the points as soon as possible.

Thank you for help... Again...  

Jeff
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Jeff,

In my first example, I didn't include a column list.  The query was an example of what to do and not the complete query.  Apologies....


The databases of just a few years ago would generally produce better performance from your query, using EXISTS or NOT EXISTS than by performing the join.  But the folks that maintain the database engines have done a masterful job of improving the optimizers.  Both should run in about the same time on current releases.


Kent
0
 
LVL 1

Author Closing Comment

by:Jeff
Comment Utility
Kent.

Thank you for sticking with this and providing me with more depth to your solution. It is appreciated!

Jeff
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now