September 26, 2008 at 6:48 am
I have sql text that gets built on the fly as follows:
select univ from tblxxx where univ in('stanford', 'College of William and Mary')
The above works fine.
But the following fails:
select univ from tblxx where univ in('College of William and Mary').
It appears it's related to the 'and' in the text, which doesn't make sense since it's surrounded by quotes. But if I use any other item in the IN function (as shown in the first sql statement, it works fine.
Any help would be greatly appreciated.
September 26, 2008 at 6:55 am
In what way does it fail?
September 26, 2008 at 7:02 am
Thanks for responding.....it doesn't return any records at all. But if I add another item (that doesn't have the 'and' in the text), it returns records containing both items.
September 26, 2008 at 7:07 am
nmah (9/26/2008)
I have sql text that gets built on the fly as follows:select univ from tblxxx where univ in('stanford', 'College of William and Mary')
The above works fine.
But the following fails:
select univ from tblxx where univ in('College of William and Mary').
It appears it's related to the 'and' in the text, which doesn't make sense since it's surrounded by quotes. But if I use any other item in the IN function (as shown in the first sql statement, it works fine.
Any help would be greatly appreciated.
I dont see issues
declare @test-2 table(data varchar(100))
insert into @test-2
select 'stanford' union all
select 'College of William and Mary'
select * from @test-2
where data in ('stanford', 'College of William and Mary')
select * from @test-2
where data in ('College of William and Mary')
Failing to plan is Planning to fail
September 26, 2008 at 7:16 am
Well, as I said, I don't get any records returned at all (sqlserver 2000) if I only have one item in the IN statement and that item contains an 'and'. I do get records returned that include that item if I add another item in the IN statement.
by the way, my sample sql statement was incorrect. If I go after a table, it works fine. My real statement is going after a view.
September 26, 2008 at 7:22 am
You say the SQL gets "built on the fly".
Can you post an example of the exact SQL that gets built.
September 26, 2008 at 7:29 am
no problem...guess I should have been more distinct. Thanks for viewing.
this returns no records:
SELECT TOP 100 PERCENT LastName, FirstName, StaffAppID, Assigned_University
FROM dbo.qrySTAFFAPP1_alacarteFields
WHERE (Assigned_University IN ('College of William and Mary'))
GROUP BY LastName, FirstName, StaffAppID, Assigned_University
ORDER BY LastName, FirstName
this returns 2 records containing College of William and Mary:
SELECT TOP 100 PERCENT LastName, FirstName, StaffAppID, Assigned_University
FROM dbo.qrySTAFFAPP1_alacarteFields
WHERE (Assigned_University IN ('xx', 'College of William and Mary'))
GROUP BY LastName, FirstName, StaffAppID, Assigned_University
ORDER BY LastName, FirstName
by the way, if I use any other university that doesn't have 'and' in the text, the sqltext works fine:
SELECT TOP 100 PERCENT LastName, FirstName, StaffAppID, Assigned_University
FROM dbo.qrySTAFFAPP1_alacarteFields
WHERE (Assigned_University IN ('Stanford'))
GROUP BY LastName, FirstName, StaffAppID, Assigned_University
ORDER BY LastName, FirstName
September 26, 2008 at 7:48 am
I think you need to look at your view.
I've created tables and test data with your scenario, and everything works!
September 26, 2008 at 8:22 am
hmmm...don't understand then why it works for any other university or sets of universities, except for this one that contains 'and'.
September 26, 2008 at 9:56 am
Do you have another college name in your table that includes the word AND? Just trying to narrow down if your problem is with this one particular college name or if it is as big a problem as you think.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply