April 29, 2008 at 1:21 pm
I had to create a filter for a specific table and I can get it to work in a query window but when I create the view it returns NULL. I'm new to t-sql and I'm sure there is a better way to code this but this is what I have. Any suggestions would be much appreciated
SELECT TEAM_MEMBER_ROLE
FROM dbo.R_KeyMembers
WHERE (TEAM_MEMBER_ROLE = 'project manager') OR
(TEAM_MEMBER_ROLE = 'project manager 1') OR
(TEAM_MEMBER_ROLE = 'superintendent') OR
(TEAM_MEMBER_ROLE = 'Senior Project Manager') OR
(TEAM_MEMBER_ROLE = 'Construction Executive') OR
(TEAM_MEMBER_ROLE = 'Joint Venture')
April 29, 2008 at 1:32 pm
That looks like a valid select statement to me. What's your create statement for the view?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 1:39 pm
I changed the where clause a bit but it still returns nothing and there are about 1000 valid rows in this table
USE [EventManagement]
GO
/****** Object: View [dbo].[vwR_KeyMembersFilter] Script Date: 04/29/2008 14:34:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwR_KeyMembersFilter]
AS
SELECT TEAM_MEMBER_ROLE
FROM dbo.R_KeyMembers
WHERE (TEAM_MEMBER_ROLE IN ('project manager', 'superintendent', 'project manager 1', 'Senior Project Manager', 'Construction Executive',
'Joint Venture'))
April 29, 2008 at 3:40 pm
Could you tell us what the data type of TEAM_MEMBER_ROLE is? If it's CHAR or NCHAR - there might be some extra space at the end that is messing up the criteria...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 3:54 pm
I don't see how that view could return NULL. Do you really mean that it is not returning any rows?
You might want to look in detail at the content of the rows that you think it should be returning to make sure there are no extra characters, like tabs, line feeds, or carriage returns that could be causing the test to fail. Converting the column to varbinary is a good way to examine the contents.
Also, you should make sure that you are using the correct collation in the view for that column or database. If the column is case-sensitive, that could be causing the problem.
select
TEAM_MEMBER_ROLE,
convert(varbinary(50),TEAM_MEMBER_ROLE)
from
dbo.R_KeyMembers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply