Solved

iSeries DB2 SQL - Query Missing Value

Posted on 2016-09-24
10
100 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
[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
  • 6
  • 3
10 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
LVL 45

Accepted Solution

by:
Kent Olsen 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:Kent Olsen
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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

690 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