iSeries DB2 SQL - Query Missing Value

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.
LVL 1
JeffDeveloperAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
JeffDeveloperAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
JeffDeveloperAuthor Commented:
I am attempting to use the NOT EXISTS predicate, but no joy.
0
 
JeffDeveloperAuthor Commented:
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
 
JeffDeveloperAuthor Commented:
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
 
JeffDeveloperAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
JeffDeveloperAuthor Commented:
Kent.

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

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.