Select Returning Blank Rows
-
I have the following select statement
--#1
Select supplier, s.refnum, desc from supplier as S
Where s.refnum = '123456'
This brings back records for me. But when I use the field name instead like in #2, I get zero rows
--#2
Select supplier, s.refnum, desc from supplier as S
where s.refnum in (select refnum from RefSched)
'#2 does not give me any records
Note: RefSched has only one record and one field named refnum and has the value of '123456'
I'm not sure why the literal works, but the select field name does not. When I only run select refnum from RefSched, one record shows the value of '123456'
Does anyone know what I'm doing wrong? Please help.
JP
-
SELECT DISTINCT refnum FROM RefSched;
SELECT DISTINCT s.refnum FROM supplier;Check if values match. Maybe Data Type Mismatch? Since DESC is a reserved keyword in SQL Server, you should enclose it in square brackets: [DESC]. Check for leading or trailing whitespaces.
-
Yes, I checked refnum is int in both tables and updated the desc but still receiving blank rows.
-
I created the tables and results returned. Re-enter the refnum in both tables, you might have a space in there.
-
If refnum is int in both tables, why are you comparing it to a string ('123456')?
Pass it as an integer without quotes.
Are both tables in dbo schema? I recommend you always include schema-qualified object names to avoid ambiguity or issues with default schemas for users.
What do these return?
Select COUNT(*) from supplier WHERE refnum IS NULL;
Select COUNT(*) from supplier WHERE refnum IS NOT NULL;
Select COUNT(*) from RefSched WHERE refnum IS NULL;
Select COUNT(*) from RefSched WHERE refnum IS NOT NULL; -
Great. Both examples from SSC Veteran and ratbak helped me. Thank you both!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply