Getting records returned that don't match criteria (Oracle)

  • Thread starter Thread starter joel2600
  • Start date Start date
J

joel2600

I am using access to query an Oracle DB... my query is as follows

SELECT a.DEFECT_ID, b.STATION_NO, b.CODE, b.DEFECT_STATUS, b.PO
FROM a INNER JOIN b ON a.DEFECT_SEQUENCE_NO = b.ID_DETAIL
WHERE (((b.STATION_NO)="BLDG12-MAINCAB") AND ((b.CODE)<>"B7" And
(b.CODE)<>"B18C") AND ((b.DEFECT_STATUS)="Recap") AND ((b.PO) Not Like
"1986" And (b.PO) Not Like "1982" And (b.PO) Not Like "2103" And (b.PO)
Not Like "1987"));

i renamed my table aliases a and b to make this more simple....

other than changing the table aliases to make this more readable, this
is all SQL that access had created from what i designed.

the problem is that even though i specified that b.defect_status needs
to equal "Recap", I still get records back that have something
completley different in that column like "Signed Off"

Does anyone know why I'm getting data that I'm not asking for? Any
help would be appreciated.

Joel
 
Rewriting this to use NOT IN insstead of NOt LIke and <> on the series. I
think this is what you were trying to do.

SELECT a.DEFECT_ID, b.STATION_NO, b.CODE, b.DEFECT_STATUS, b.PO
FROM a INNER JOIN b ON a.DEFECT_SEQUENCE_NO = b.ID_DETAIL
WHERE b.STATION_NO="BLDG12-MAINCAB"
AND b.CODE NOT IN ("B7","B18C")
AND b.DEFECT_STATUS="Recap"
AND b.PO NOT IN ("1986" ,"1982" ,"2103" ,"1987")

OH, Just saw the ORACLE DB. Well, try the above and see if it works.
 
ok, it's worse than i realized. Thanks for the advice John, I did what
you suggested with no result. From there I just started taking
everything out of my query one at a time to see what the criteria was
that would be causing this.

Here is my query simplified, one table, 2 criteria

SELECT a.STATION_NO, a.DEFECT_STATUS
FROM a
WHERE (((a.STATION_NO)="BLDG12-MAINCAB") AND
((a.DEFECT_STATUS)="Recap"));

seems pretty simple... although it's still pulling records who's
"Defect Status" is 'Signed off' when i'm clearly asking for just
records that are 'Recap'

Right now I can only assume this is a problem with the ODBC driver, or
how access is getting at this data because this just doesn't make much
sense. I'm assuming that the only way to fix this would be to find
another way to use some operators ('==' or something) to try and get a
result.

If anyone has any suggestions what I might need to try, or why I'm
seeing data come back like this, please let me know!!! Thanks.
 
i think it has to do with access being some microsoft backwash
grabtastic program that translates all this crap behind the scenes
leaving the user helpless and wondering how things ever got this bad.

enter the user, circa 1881

there's pot of coffe on the fire, everyone is restless, mother fu**ers
are jumping out of trees.
....microsoft access sits down at the table
"'eloo down there"
"what is it pa?"
"dammit bah i tol you to shut up"!!! .. "tha innnanets no place fo
fools"

modules, reports, tables, queries, macros ... whatever. all that really
matters is that you don't take your firearms to work when you're
expected to do complicated data analysis with microsoft office.
it's like taking tools away from cavemen and giving them dice, salmon,
sharpies, log tumbling videos, or anything else equally as worthless
for accomplishing tasks.

.:::. _,,,_ .:::.
::(\:::.-'"` `"'-.:::/):;
`:::\:` `:/:::'
`:: ::'
/ .:::. .:::. \
| ::::0} {0:::: |
| ::::/ \:::: |
| ''/ \'' |
| / \ |
\ ; . .---. . ; /
`. \'.( ).'/ .'
`-\. `-.-' '/-'
\'--'--'/ BEAR ATTACK
`-...-'

Man... i bet you wern't expecting that!!! it's ok, neither was i.
don't matter though, you died of dysentery.
 
also

when i run this query
SELECT a.DEFECT_STATUS
FROM a
WHERE ((a.DEFECT_STATUS)="Recap");

i still get records that are of another status

the wierd thing is that there are tons more records that are not recaps
as opposed to ones that are. So it's definitley doing some sort of
filtering, it's just doing a horrible inaccurate job.

seriously, wtf
 
ok, for those of you that are also on the edge of your seats trying to
figure out this paradox, i have news for you.... i solved the mystery

the problem lies in the fact that the field i'm using as a primary key
has duplicate entries, so this explains why and how this is happening.
i didn't build this database, so blame someone else. find the person
next to you and blame them. dial the pope and blame him. last but not
least forget you ever read any of this... douse your computer in
gasoline, light it on fire... then turn and run.

i never saw the pyramids, but i always wanted to.
 
Back
Top