Kayser22
asked on
Problems with "IN" clause
I wrote an sql sentence while working on a problem that doesn't work the way I expect it to. I was wondering if anyone can explain to me why this doesn't work.
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y")
does not return jobno=414335
However
select jobno from OEORD_T1 where jobno in (123456,456789,414335)
returns 414335 and
select jobno from UPSHH_T1 where OEUPDT_S="Y"
returns 414335
Furthermore
select oeord_t1.jobno from OEORD_T1 inner join UPSHH_t1
on oeord_t1.jobno=upshh_t1.jo
returns 414335. I used this last statement instead of the first one to get the data that I needed. I don't understand why the original one didn't return the data I expected it to. I'm wondering if I don't understand how to use the IN clause correctly. Can anyone see what I'm doing wrong? Thanks in advance.
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y")
does not return jobno=414335
However
select jobno from OEORD_T1 where jobno in (123456,456789,414335)
returns 414335 and
select jobno from UPSHH_T1 where OEUPDT_S="Y"
returns 414335
Furthermore
select oeord_t1.jobno from OEORD_T1 inner join UPSHH_t1
on oeord_t1.jobno=upshh_t1.jo
returns 414335. I used this last statement instead of the first one to get the data that I needed. I don't understand why the original one didn't return the data I expected it to. I'm wondering if I don't understand how to use the IN clause correctly. Can anyone see what I'm doing wrong? Thanks in advance.
ASKER
That returned an identical recordset as the one I'm questioning. 414335 wasn't returned.
Other than using single quotes instead of double, I don't see anything wrong, and it's very unlikely that you have a column named y on UPSHH_T1 table.
select jobno
from OEORD_T1
where jobno in (
select jobno
from UPSHH_T1
where jobno IS NOT NULL
AND OEUPDT_S='Y' )
select jobno
from OEORD_T1
where jobno in (
select jobno
from UPSHH_T1
where jobno IS NOT NULL
AND OEUPDT_S='Y' )
ASKER
Actually the single quote version is what I started with. That returns the same recordset as the other two.
I'm using WIN uSQLi32 by transoft to query a transoft database. I hate to blame all my problems on buggy software, but maybe... Anyway here is the query plan I get when I run the previous query.
(Query: 1) OEORD_T1 INDEXED
Index No. 0
Lower : Set OEORD_T1.JOBNO to the Lowest of Subquery 1.1
Upper : OEORD_T1.JOBNO <= the Highest of Subquery 1.1
With Filter : OEORD_T1.JOBNO =any Results of Subquery 1.1
(Query: 1.1) UPSHH_T1 SEQUENTIAL
FILTER : ((not UPSHH_T1.JOBNO = null) and UPSHH_T1.OEUPDT_S = 'Y')
I'm using WIN uSQLi32 by transoft to query a transoft database. I hate to blame all my problems on buggy software, but maybe... Anyway here is the query plan I get when I run the previous query.
(Query: 1) OEORD_T1 INDEXED
Index No. 0
Lower : Set OEORD_T1.JOBNO to the Lowest of Subquery 1.1
Upper : OEORD_T1.JOBNO <= the Highest of Subquery 1.1
With Filter : OEORD_T1.JOBNO =any Results of Subquery 1.1
(Query: 1.1) UPSHH_T1 SEQUENTIAL
FILTER : ((not UPSHH_T1.JOBNO = null) and UPSHH_T1.OEUPDT_S = 'Y')
Actually, buggy software is about the only explanation I can think of, either on the WIN uSQLi32 end or in SQL Server itself. Otherwise, I would expect that query to work.
Does the datatype ofjobno match in both the tables?
That's an EXCELLENT question, too.
first of all try this query and see if it returns something or not
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y" and jobno = 414335)
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y" and jobno = 414335)
ASKER
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y" and jobno = 414335)
does return 414335
The data types are the same, although in one table it is defined as a 3 byte field with a maximum of 999999 and the other table it is a 4 byte field with a maximum of 99999999.
does return 414335
The data types are the same, although in one table it is defined as a 3 byte field with a maximum of 999999 and the other table it is a 4 byte field with a maximum of 99999999.
?
What, specifically, are the two datatypes/column definitions?
Are they, for example, CHAR(3)/VARCHAR(3) and CHAR(4)/VARCHAR(4)?
What, specifically, are the two datatypes/column definitions?
Are they, for example, CHAR(3)/VARCHAR(3) and CHAR(4)/VARCHAR(4)?
In case it is of the type as told by Scott then u may try to do ltrim(rtrim(jobno) in all the places and run the query
ASKER
In transoft they are both defined as data type 2 which numeric.
Also, I didn't mean to imply anywhere that the problem sentence returns the null set. It does return 53 records; lowest= 406297 and highest =413256. I can't figure out why those get returned but 414335 does not.
Also, I didn't mean to imply anywhere that the problem sentence returns the null set. It does return 53 records; lowest= 406297 and highest =413256. I can't figure out why those get returned but 414335 does not.
Try this and see if the right rowcount is returned,
select count(*) from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S='Y')
Or Try
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y")
Select @@ROWCOUNT
Is it possible that row is filtered by your front end tool.
HTH
Namasi
select count(*) from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S='Y')
Or Try
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y")
Select @@ROWCOUNT
Is it possible that row is filtered by your front end tool.
HTH
Namasi
One thought..... What is the collation on the OEUPDT_S field ?
If it is a case sensative collation you will need to UPPER() your field before comparing.
If it is a case sensative collation you will need to UPPER() your field before comparing.
ignore last, ive just read the question properly :S
What about trying this
select jobno from OEORD_T1 a where exists (select 1 from UPSHH_T1 where OEUPDT_S="Y" and jobno=a.jobno)
?
What about trying this
select jobno from OEORD_T1 a where exists (select 1 from UPSHH_T1 where OEUPDT_S="Y" and jobno=a.jobno)
?
ASKER
Just catching up a bit. Due to normal operations, the original SQL sentence is now returning 11 records. 414335 is still in both tables.
select count(*) from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S='Y')
returns 11
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y")
returns the same recordset as the original sentence.
select jobno from OEORD_T1 a where exists (select 1 from UPSHH_T1 where OEUPDT_S="Y" and jobno=a.jobno)
Timed out before returning results
select count(*) from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S='Y')
returns 11
select jobno from OEORD_T1 where jobno in (select jobno from UPSHH_T1 where OEUPDT_S="Y")
returns the same recordset as the original sentence.
select jobno from OEORD_T1 a where exists (select 1 from UPSHH_T1 where OEUPDT_S="Y" and jobno=a.jobno)
Timed out before returning results
Just trying an elimination process here.....
Are you sure there is no corruption in your database ?
Are you sure there is no corruption in your database ?
ASKER
Am I sure? No. Ususally any corruption manifests itself in the indexes, and I've rebuilt those. Ive checked the records in questions directly and they don't look corrupted. I don't know how to check for any other sorts of corruption.
try running DBCC CHECKDB('MyDb')
In the past I once had a table with a badly linked index page this meant that depending on whether the index was used depended upon whether the query returned the results :(
In the past I once had a table with a badly linked index page this meant that depending on whether the index was used depended upon whether the query returned the results :(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Computer101:
3 days wasnt a very long time to post comments about this request given that a) the author seems to have gone silent since my last post. and b) 3 days included xmas day,boxing day and saturday :( :(
I am confused as to why the request was made. Did our commets not resolve the problem? If so why hasnt the author said so? Did the author find the problem?
3 days wasnt a very long time to post comments about this request given that a) the author seems to have gone silent since my last post. and b) 3 days included xmas day,boxing day and saturday :( :(
I am confused as to why the request was made. Did our commets not resolve the problem? If so why hasnt the author said so? Did the author find the problem?
ASKER
My apologies for not responding to your last entry. I started to and never finished. As I stated earlier, this isn't an SQL server. I believe the commands that you asked me to try are SQL server commands. I don't know of any equivalents in the transoft world. I originally asked this question to see if something was wrong with the SQL sentence I wrote, which I thought there probably was. Nobody found anything wrong with it. Unfortunately, I don't think that it is in the scope of my job, or my ability, to pursue this any further. Furthermore, the data changes on a daily basis, so my example is long gone. I don't want to waste any more of anybody's time, so I asked for the issue to be closed. I appreciate everybody's efforts at EE and don't want to treat anybody unfairly. If you can give me any feedback on how I could have handled this better, I'm all ears.
Ok. Sounds fair enough. I just was a bit mistified following a spell of silence and then a closure request.
select jobno
from OEORD_T1
where jobno in (
select jobno
from UPSHH_T1
where jobno IS NOT NULL
AND OEUPDT_S="Y" )
although the join method is generally preferred.