[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

iSeries DB2 SQL - Query Missing Value

Posted on 2016-09-24
10
Medium Priority
?
120 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 46

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 46

Accepted Solution

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

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

649 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