April 7, 2009 at 4:36 am
The following code is give me an error when run:
select distinct b.entity_id, c.profile_user_id ,
d.account, d.lastname, d.firstname, d.email
from profile_entity_tbl a
left join profile_entity_line_tbl b on a.profile_entity_id = b.profile_entity_id
left join profile_table c on c.profile_id = a.profile_id
left join saleslogix.london_remote.sysdba.contact d on c.profile_user_id
= d.contactid
left join profile_paper_preferences_table e on e.profile_id = a.profile_id
where
d.contactid in (select * from saleslogix.london_remote.sysdba.contact
where accountid in
('AH4LIA80009Q'
,'AH4LIA6023IX'
,'AH4LIA80002T'
,'AH4LIA80004X'
,'AH4LIA8000J2'
,'ALJRDA0002QZ'
,'ALJRDA0002OF'
,'ALJRDA0000V6'
,'ALJRDA0000UZ'
,'ALJRDA0002F0
,'AH4LIA6023I0'
,'ALJRDA0002F1'
,'AH4LIA601R1L'
,'ALJRDA0002QL'
,'AH4LIA8000NV'
,'ALJRDA0000UR'
,'ALJRDA0002FB'
,'LJRDA0002F3'
,'AH4LIA8000RO'))
and c.profile_active = 1 and d.account not like 'null%' and d.account not like 'archive%'
ERROR
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
April 7, 2009 at 4:43 am
where
d.contactid in (select * from saleslogix.london_remote.sysdba.contact
Change the above to
where
d.contactid in (select ContactID from saleslogix.london_remote.sysdba.contact
Cheers,
Arun
April 7, 2009 at 4:48 am
Thanks!!!!!!!!!
Works! 😀
April 7, 2009 at 4:50 am
A Little Help Please (4/7/2009)
Thanks!!!!!!!!!Works! 😀
and d.account not like 'null%' Are you sure this works?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 5:30 am
Chris Morris (4/7/2009)
A Little Help Please (4/7/2009)
Thanks!!!!!!!!!Works! 😀
and d.account not like 'null%'Are you sure this works?
surprisingly, for me it does seem to work.
i just did a simple select statement on my database saying 'where col1 not like 'null%'
and all the nulls didn't show.
run the same select statement without the where clause and all the null value columns get returned.
'where col1 like 'null%' doesn't work though.
April 7, 2009 at 6:17 am
davidandrews13 (4/7/2009)
Chris Morris (4/7/2009)
A Little Help Please (4/7/2009)
Thanks!!!!!!!!!Works! 😀
and d.account not like 'null%'Are you sure this works?surprisingly, for me it does seem to work.
i just did a simple select statement on my database saying 'where col1 not like 'null%'
and all the nulls didn't show.
run the same select statement without the where clause and all the null value columns get returned.
'where col1 like 'null%' doesn't work though.
The NULL values don't show because they don't equal NULL%. They don't equal anything. Here's some sample code that shows, including showing the bad data that you can get:
CREATE TABLE #NullTest
(ID INT IDENTITY(1,1),NullableString NVARCHAR(50) NULL)
--I put the - next the string NULL so you can see which one it is.
INSERT INTO #NullTest (
NullableString)
SELECT 'Some Value'
UNION
SELECT 'NULL-'
UNION
SELECT NULL
UNION
SELECT 'NOT NULL'
SELECT * FROM #NullTest AS nt
SELECT * FROM #NullTest AS nt
WHERE nt.NullableString NOT LIKE 'null%'
SELECT * FROM #NullTest AS nt
WHERE nt.NullableString LIKE 'NULL%'
-- the real values that are not NULL
SELECT * FROM #NullTest AS nt
WHERE NullableString IS NOT NULL
DROP TABLE #NullTest
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2009 at 6:51 am
Grant Fritchey (4/7/2009)
davidandrews13 (4/7/2009)
Chris Morris (4/7/2009)
A Little Help Please (4/7/2009)
Thanks!!!!!!!!!Works! 😀
and d.account not like 'null%'Are you sure this works?surprisingly, for me it does seem to work.
i just did a simple select statement on my database saying 'where col1 not like 'null%'
and all the nulls didn't show.
run the same select statement without the where clause and all the null value columns get returned.
'where col1 like 'null%' doesn't work though.
The NULL values don't show because they don't equal NULL%. They don't equal anything. Here's some sample code that shows, including showing the bad data that you can get:
CREATE TABLE #NullTest
(ID INT IDENTITY(1,1),NullableString NVARCHAR(50) NULL)
--I put the - next the string NULL so you can see which one it is.
INSERT INTO #NullTest (
NullableString)
SELECT 'Some Value'
UNION
SELECT 'NULL-'
UNION
SELECT NULL
UNION
SELECT 'NOT NULL'
SELECT * FROM #NullTest AS nt
SELECT * FROM #NullTest AS nt
WHERE nt.NullableString NOT LIKE 'null%'
SELECT * FROM #NullTest AS nt
WHERE nt.NullableString LIKE 'NULL%'
-- the real values that are not NULL
SELECT * FROM #NullTest AS nt
WHERE NullableString IS NOT NULL
DROP TABLE #NullTest
oh i understand why 'where col1 like 'null%' doesn't work .:) presuming thats the bit your replying to?
i was just shocked that 'where col1 not like 'null%' does seem to work.
April 7, 2009 at 6:57 am
davidandrews13 (4/7/2009)
oh i understand why 'where col1 like 'null%' doesn't work .:) presuming thats the bit your replying to?
i was just shocked that 'where col1 not like 'null%' does seem to work.
'Seems to work' is the key word and tricky phrase. If you look at the code I posted you'll note that it's filtering both NULL values and values that start with the string 'NULL' which are decidedly NOT NULL. Since NULL doesn't equal anything, it doesn't NOT equal anything either. So you were getting a filter on the NOT LIKE. But you could have said NOT LIKE 'Broccoli%' and that would have eliminated the NULL values as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2009 at 7:30 am
Grant Fritchey (4/7/2009)
davidandrews13 (4/7/2009)
oh i understand why 'where col1 like 'null%' doesn't work .:) presuming thats the bit your replying to?
i was just shocked that 'where col1 not like 'null%' does seem to work.
'Seems to work' is the key word and tricky phrase. If you look at the code I posted you'll note that it's filtering both NULL values and values that start with the string 'NULL' which are decidedly NOT NULL. Since NULL doesn't equal anything, it doesn't NOT equal anything either. So you were getting a filter on the NOT LIKE. But you could have said NOT LIKE 'Broccoli%' and that would have eliminated the NULL values as well.
ahaa i get what your saying now. just as i think i understand just exactly what nulls are/aren't ,something else comes up to remind me i dont!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply