Where clause not properly excluding records in one-to-many join query

[mjmabee]

I am trying to write a query that will select records from a primary table when they meet two criteria conditions in that table and their associated records in the foreign key table match ONLY to one of the three values. The foreign key table can contain up to three records matching back to the one in the primary table. In many situations, one of these three records (in the foreign key table) does contain one of the values I'm looking for, but it also contains records with values that should exclude the record (across all tables) from my result set.

The query below is my best guess as to how to retrieve this result set. The part that's giving problems is in CAPS.




select i.amt, e.code

from (dbo.hExceptionitem ei join dbo.hItem i on (ei.batchnumber = i.batchnumber) and (ei.processingdate = i.processingdate) and (ei.itemrow = i.rownumber) join dbo.hException e on (ei.processingdate = e.processingdate) and (ei.batchnumber = e.batchnumber) and (ei.exceptionrow = e.rownumber))

where i.date='11/1/2006' and (substr(i.num,1,3)='123') and (e.code in ('18','1018','7018')) AND (E.CODE NOT BETWEEN '1' AND '17' AND E.CODE NOT BETWEEN '19' AND '75') AND (E.CODE NOT BETWEEN '1001' AND '1017' AND E.CODE NOT BETWEEN '1019' AND '1075') AND (E.CODE NOT BETWEEN '7001' AND '7017' AND E.CODE NOT BETWEEN '7019' AND '7075')




When I run this query without the part in caps, I do get some records that, legitimately, should not be there. The trouble is, it also returns some records that SHOULD be there that are not returned with the query shown above.... records, that is, that ONLY have an e.code field value of '1018'.

I have tried tons of different permutations of this query. Each time i either wind up missing some records that i know should be there, or i get records in the result set that should not be there.

If anyone has any suggestions, please let me know. Any help is greatly appreciated.

 

[datamodeler]

if you have, instead of "e.code in ....." the phrase "e.code = '18', do you only get what you expect?

if your answere is yes, try unioning three queries together

mike harris

Mike Harris
Data Modeling & Administration

 

[mjmabee]

Hmmm.. this is interesting. I tried using "e.code='1018'", and got no records at all. In fact, the DB2 UDB prompt i'm running the query from flies through all iterations of the query (i'm running a loop to retrieve variable values in the date and i.num fields) in under a second.

When i use "e.code='18'", the command prompt takes about 1-2 seconds for each iteration, and i do get results.

The thing is, i know that there are '1018' values out there, because that's what i'm missing when i run the query i originally posted (they show up when i exclude the NOT BETWEEN part of the where clause).

I don't see anything in my criteria that is causing a contradiction.

Ideas ? Thanks

 

[blom0344]

dbo indicates that you are acessing SQL server?
Are you confidant that the between operand works with strings like it would with numericals?
How about first converting E.code to a numerical before applying the between condition?

Ties Blom

 

[mjmabee]

dbo is the database owner's name. The database i am working with here is DB2 UDB (7.2 to be exact). The between operand does work with strings... i tested it in a separate query that only used those expressions as criteria, and it did return the records i expected.

 

[MarcLodge]

First off, I hate joins that are ONs and ANDs, because they complicate things so much. Legitimate as they might be, in cases like this they muddy the water.

1st thing to do is to remove the WHERE clauses from your query and check the results. Are all the rows you are expecting to be there, there? Are only the rows you are expecting to be there, there? If so, we can elimnate the ONs and ANDs from our reckoning and move onto the the WHEREs

Try this:
select i.amt, e.code
from (dbo.hExceptionitem ei join dbo.hItem i on (ei.batchnumber = i.batchnumber) and (ei.processingdate = i.processingdate) and (ei.itemrow = i.rownumber) join dbo.hException e on (ei.processingdate = e.processingdate) and (ei.batchnumber = e.batchnumber) and (ei.exceptionrow = e.rownumber))
where i.date='11/1/2006'
and substr(i.num,1,3)='123'
and e.code in ('18','1018','7018')

OK, I know all I've done is removed brackets, but in my opinion they were unnecessary and were clouding the issue.

Let me know how you get on debuggin this SQL

Marc

 

[mjmabee]

Thanks Marc,
I'm at home right now, and I won't have a chance to test this until tomorrow when i'm at work again tomorrow. i'll check it out then, and i'll post back asap.

Jake

 

[mjmabee]

?? Did you want me to remove the entire where clause ? I'm not able to do that because of the size of the database (i risk crashing a production application with queries that are too demanding). i think you were asking just to eliminate some of the requirements...

so, i used the following statement:

select i.id, e.code

from (dbo.hExceptionitem ei join dbo.hItem i on (ei.batchnumber = i.batchnumber) and (ei.processingdate = i.processingdate) and (ei.itemrow = i.rownumber) join dbo.hException e on (ei.processingdate = e.processingdate) and (ei.batchnumber = e.batchnumber) and (ei.exceptionrow = e.rownumber))

where i.date='11/29/2006'
and substr(i.num,1,3)='750'

order by i.id desc"


>> c:\temp\ee\allRecs-11-29.txt

to retrieve a list of records not filtered on the e.code field. I then ran a separate statement including the e.code in ('18','1018','7018') requirement in the where clause, and compared its results against the first set. both queries returned the results i expected. the larger set did not have any more records matching to the i.id field than the smaller set for the id's included in the smaller set.

 

[kkitt]

I believe that you have a conflict on your where clause:

where i.date='11/1/2006'
 and (substr(i.num,1,3)='123') 
 and (e.code in ('18','1018','7018')) 
 AND ( E.CODE NOT BETWEEN '1' AND '17' 
       AND E.CODE NOT BETWEEN '19' AND '75') 
 AND ( E.CODE NOT BETWEEN '1001' AND '1017' 
       AND E.CODE NOT BETWEEN '1019' AND '1075') 
 AND ( E.CODE NOT BETWEEN '7001' AND '7017'
       AND E.CODE NOT BETWEEN '7019' AND '7075')

All conditions must be true in order for any data to be pulled back. If you you have the E.Code of '1018' then two of the between clause will be false, resulting in the where clause not being met.

Your "IN" clause is the limiting factor in this query, so no matter what the between clause bring back if it is not in the in clause then the where is that not going to be met and no data returned.

 

[MarcLodge]

Jake,
Let's go back to what you are trying to do because I'm not sure I understand it from your orignal post.

You have a primary table that you are excluding some records from. Of those that are not excluded, you want to join to a further table on a foreign key with a one to many relationship.

On the foreign key table, you are only interested in those rows where a particular column has one of three values.

Now this is the bit that I'm not sure about. In your original post you said:

"In many situations, one of these three records (in the foreign key table) does contain one of the values I'm looking for, but it also contains records with values that should exclude the record (across all tables) from my result set"

Can you explain that a little better as I don't understand what you are trying to achieve.

Marc

 

[mjmabee]

Hmm. I'm not dismissing what you're saying, but i also don't see the conflict... let's look strictly at the last couple parts of the where clause for a minute:

(e.code in ('18','1018','7018'))
AND ( E.CODE NOT BETWEEN '1' AND '17'
AND E.CODE NOT BETWEEN '19' AND '75')
AND ( E.CODE NOT BETWEEN '1001' AND '1017'
AND E.CODE NOT BETWEEN '1019' AND '1075')
AND ( E.CODE NOT BETWEEN '7001' AND '7017'
AND E.CODE NOT BETWEEN '7019' AND '7075')

A little background on what this data represents: the table represented by the alias "i" contains unique records for transaction items. the "e" table contains unique records for exceptions related to the items - the exceptions are reasons that a given item would be sent to our application to be processed. each item can have one to three exceptions associated with it.

The purpose of this query is to retrieve items that ONLY have one exception code... an '18', a '1018' or a '7018'. If the item has any other exception codes associated with it, it should not be included in the result set returned by this query. it is for this reason that i include this criteria in the where clause:

AND ( E.CODE NOT BETWEEN '1' AND '17'
AND E.CODE NOT BETWEEN '19' AND '75')
AND ( E.CODE NOT BETWEEN '1001' AND '1017'
AND E.CODE NOT BETWEEN '1019' AND '1075')
AND ( E.CODE NOT BETWEEN '7001' AND '7017'
AND E.CODE NOT BETWEEN '7019' AND '7075')

some more background: the exception codes 1-75 are used for paper items. 1001-1075 are used for electronic, and 7001-7075 are used for what are called 'ecp' items.

so, if an item has an exception code of '18' and has other associated exception codes, they can only be in the ranges 1-17 or 19-75.

Does that make sense ? Does my query logic make sense ? Do you still see a conflict in the criteria ?

Thanks again for the help

 

[mjmabee]

Hi Marc, does that clarify, or do you need more explanation ? Thanks !

 

[MarcLodge]

Jake,

Howabout something like:

select i.amt, e.code

from dbo.hExceptionitem ei
    ,dbo.hItem i 
    ,dbo.hException e

Where ei.batchnumber    = i.batchnumber
  and ei.processingdate = i.processingdate
  and ei.itemrow        = i.rownumber
 
  and ei.processingdate = e.processingdate
  and ei.batchnumber    = e.batchnumber
  and ei.exceptionrow   = e.rownumber

  and i.date            = '11/1/2006' 
  and substr(i.num,1,3) = '123' 
  and e.code in ('18','1018','7018')
 
  and 1 = (select count(*)
           from dbo.hException Z
           where z.processingdate = e.processingdate
             and z.batchnumber    = e.batchnumber
             and z.exceptionrow   = e.rownumber
             and z.code in ('18','1018','7018') )

Obviously haven't been able to test this.

Marc

 

[kkitt]

Guess I am confused why you care about any of the other exception code other then '18' '1018' and '7018'?

When the query is run only the records that have these exception codes are going to be in the result set. The query is probably getting confused by bing told to only items with these three exception code, but, it can also be any others.

Are you trying to get a list of all items that have and exception code of 18 and also any other exception code 1-17 and 19-75 only:


item exception included

12301 18 yes
12301 15 yes
12301 90 no
12301 70 yes

what would the results be if the item has an exception code of '1018' would it be listed it mets some of the criteria as stated based off you last post of having to be in the range.

Am I totally confused !!!!!!

 

[mjmabee]

Oh, man, i could scream ! I really thought you had nailed it when i first looked at the result set. Unfortunately, your statement is giving the same results as this one:

select i.amt, e.code

from (dbo.hExceptionitem ei join dbo.hItem i on (ei.batchnumber = i.batchnumber) and (ei.processingdate = i.processingdate) and (ei.itemrow = i.rownumber) join dbo.hException e on (ei.processingdate = e.processingdate) and (ei.batchnumber = e.batchnumber) and (ei.exceptionrow = e.rownumber))

where i.processingdate='11/1/2006'
and substr(i.accountnumber,1,3)='610'
and (e.exceptiontypecode in ('1018','18','7018'))

order by i.accountnumber, i.amount

i checked the records in the output, one of them had an associated exception code that should have excluded it from the result set.

if i add the not between requirements to your subquery criteria, like so:


select i.id
, e.code

from dbo.hExceptionitem ei, dbo.hItem i, dbo.hException e

where ei.batchnumber = i.batchnumber and ei.processingdate = i.processingdate and ei.itemrow = i.rownumber and ei.processingdate = e.processingdate and ei.batchnumber = e.batchnumber and ei.exceptionrow = e.rownumber

and i.processingdate='11/1/2006'
and substr(i.accountnumber,1,3)='610'
and e.exceptiontypecode in ('18','1018','7018')

and 1 = (select count(*)
from dbo.hException z where z.processingdate = e.processingdate and z.batchnumber = e.batchnumber and z.rownumber = e.rownumber

and z.exceptiontypecode in ('18','1018','7018') and ((z.exceptiontypecode not between '1' and '17' and z.exceptiontypecode not between '19' and '75') and (z.exceptiontypecode not between '1001' and '1017' and z.exceptiontypecode not between '1019' and '1075') and (z.exceptiontypecode not between '7001' and '7017' and z.exceptiontypecode not between '7019' and '7075'

...all of the '1018' values are excluded.

Let me say though - GREAT idea. Any others ?

Ha ! This one's a tough cookie, huh ?

 

[mjmabee]

The reason i care about other exception codes is:

I'll be using a separate, simpler query to grab a bunch of items, including those that have codes '18', '1018', or '7018' as well as any other codes.

The query we're working on here has to select only those items that have only an exception code of '18' or '1018' or '7018'

 

[mjmabee]

Wow, I don't know... i wrote up this query (which is an extension on Marc's idea), and thought for sure it would do the trick. It returned no records, but i the logic makes so much sense to me !!! this is driving me crazy.

select i.id
, e.code

from dbo.hExceptionitem ei
,dbo.hItem i
, dbo.hException e

Where ei.batchnumber = i.batchnumber
and ei.processingdate = i.processingdate
and ei.itemrow = i.rownumber

and ei.processingdate = e.processingdate
and ei.batchnumber = e.batchnumber
and ei.exceptionrow = e.rownumber

and i.date='11/1/2006'
and substr(i.num,1,3)='610'
and e.code in ('18','1018','7018')

and 1 = (select count(a.exceptionrow)

from dbo.hExceptionitem a
, dbo.hItem b

Where a.batchnumber = b.batchnumber
and a.processingdate = b.processingdate
and a.itemrow = b.rownumber)

Anybody have any other ideas ?

 

[mjmabee]

OK.. note to self mostly, but here's what i'm going to try on Monday...

select i.id
, e.code

from dbo.hExceptionitem ei
,dbo.hItem i
, dbo.hException e

Where ei.batchnumber = i.batchnumber
and ei.processingdate = i.processingdate
and ei.itemrow = i.rownumber

and ei.processingdate = e.processingdate
and ei.batchnumber = e.batchnumber
and ei.exceptionrow = e.rownumber

and i.date='11/1/2006'
and substr(i.num,1,3)='610'
and e.code in ('18','1018','7018')

and 1 = (select count(a.exceptionrow)

from dbo.hExceptionitem a
, dbo.hItem b

Where a.batchnumber = b.batchnumber
and a.processingdate = b.processingdate
and a.itemrow = b.rownumber
and a.id = i.id)

I'll post back with the results !

 

[mjmabee]

I mean...

and b.id = i.id)

 

[MarcLodge]

Jake,

I don't think your latest offering is going to work as, in my opinion, you need to tie the main query in to the subquery, and there's no link that I can see that does so.

Could you run:

select i.id, i.amt, i.batchnumber, i.processingdate, i.rownumber, substr(i.num,1,3) , e.code

from dbo.hExceptionitem ei
,dbo.hItem i
,dbo.hException e

Where ei.batchnumber = i.batchnumber
and ei.processingdate = i.processingdate
and ei.itemrow = i.rownumber

and ei.processingdate = e.processingdate
and ei.batchnumber = e.batchnumber
and ei.exceptionrow = e.rownumber

and i.date = '11/1/2006'
and substr(i.num,1,3) = '123'
and e.code in ('18','1018','7018')

Then could you run:

select i.id, i.amt, i.batchnumber, i.processingdate, i.rownumber, substr(i.num,1,3) , e.code

from dbo.hExceptionitem ei
,dbo.hItem i
,dbo.hException e

Where ei.batchnumber = i.batchnumber
and ei.processingdate = i.processingdate
and ei.itemrow = i.rownumber

and ei.processingdate = e.processingdate
and ei.batchnumber = e.batchnumber
and ei.exceptionrow = e.rownumber

and i.date = '11/1/2006'
and substr(i.num,1,3) = '123'
and e.code in ('18','1018','7018')

and 1 = (select count(*)
from dbo.hException Z
where z.processingdate = e.processingdate
and z.batchnumber = e.batchnumber
and z.exceptionrow = e.rownumber
and z.code in ('18','1018','7018') )

Could you post the results (or subsection of the results) from each query.

Marc