June 26, 2003 at 1:03 pm
Why does this fail (returns rows with NULL in field X as well as rows containing search string):
Select * from aView where X like 'test%'
while if I shove aView into a table,
select * into aTable
and then query it identically:
select * from aTable where X like 'test%'
I only get a single record (as I should, the one that starts with 'test')
X is a varchar(8000) field.
What's suspicious is that if I use a field other than X (X is the only giant varchar) - I receive the correct results.
I know I'm overlooking something simple,
thanks
June 26, 2003 at 3:01 pm
Can you post the definition of aView?
Cheers,
- Mark
June 26, 2003 at 3:07 pm
CREATE VIEW dbo.vw_datasets_Attributes AS
select ds.PK_datasetid, ds.datasetname, ds.dataformat,
ds.owningoffice_ID, ds.datasetnotes,ds.responsibilityOf, ds.status,
ds.display, ds.system_id, dsxa.pk_attributeID,
dsxa.attributedata_type1, dsxa.attributedata_type2, dsxa.attributedata_type3
From dataset ds, dataset_x_attribute dsxa
where ds.pk_datasetid *= dsxa.pk_datasetid
I had originally been defining this with the following where clause:
where ds.pk_datasetid = dsxa.pk_datasetid
and I was querying this view with a single like clause over one field. By changing the join to *= I'm including all the records from ds, since I'd like to query in one field only the matching records in dsxa and in a second field all the records in ds. Hunting down why I wasn't getting what I expected led me to this question about the behavior I'm reporting above.
June 26, 2003 at 3:37 pm
It MIGHT be an ambiguity arising from your outer join syntax. Possibly worth trying a LEFT OUTER JOIN instead of "*=". From BOL:
quote:
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins
Cheers,
- Mark
June 26, 2003 at 3:46 pm
Well I'll be - I thought about trying this but rejected it out of sheer laziness. But it appears to have worked.
many thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply