Msg 207, Level 16, State 1, Line 2 in SQL Server

I have created the following on which in can perform query with out where clause clause but when I use the following query:

select * from teacherSub where teacher = "te123";

it generates an error:

Msg 207, Level 16, State 1, Line 2

Full code:

USE [SchoolRecord] GO /****** Object: View [dbo].[teacherSub] Script Date: 3/30/2015 8:32:56 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[teacherSub] AS SELECT sbt.teacherID AS teacher, sub.Name AS SubjectName, sub.GroupID AS groupName, sub.Description AS subdescription FROM Subjects sub, SubjectTeacher sbt, teacherInfo te WHERE sbt.teacherID = te.TeacherID AND sbt.SubjectID = sub.subjectID;

How can I apply where clause on above view?

-------------Problems Reply------------

Use Single quotes instead of double quotes. Double quotes are used to delimit identifiers

select * from teacherSub where teacher='te123'

As fireblade has explained you need to use Single Quotes for Literal Strings

Also you need to use the JOIN clause to join tables(good Practice).

View Definition

ALTER VIEW [dbo].[teacherSub]
AS
SELECT sbt.teacherID AS teacher,
sub.Name AS SubjectName,
sub.GroupID AS groupName,
sub.Description AS subdescription
FROM Subjects sub
INNER JOIN SubjectTeacher sbt ON sbt.SubjectID = sub.subjectID
INNER JOIN teacherInfo te ON sbt.teacherID = te.TeacherID;

Query

select * from teacherSub where teacher= 'te123';

Two improvements in the query

  1. Double quotes not allowed
  2. Instead of = , use 'like' keyword

select * from teacherSub where teacher LIKE 'te123'

Category:sql server Views:1 Time:2017-09-11

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.141 (s). 10 q(s)