Solved

iSeries DB2 SQL - Query Missing Value

Posted on 2016-09-24
10
67 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
ID: 41814167
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
ID: 41814710
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
ID: 41814751
I am attempting to use the NOT EXISTS predicate, but no joy.
0
 
LVL 1

Author Comment

by:Jeff
ID: 41814780
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 41814814
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
ID: 41814847
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
ID: 41814852
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
ID: 41814853
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
ID: 41814858
Kent.

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

Jeff
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

863 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

18 Experts available now in Live!

Get 1:1 Help Now