T-SQL View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

T-SQL View

Author

Topic

forefj
Starting Member

10 Posts

Posted - 2012-05-10 : 14:17:55

I have a T-SQL query below that runs fine. However, when I tried to create a View I get the following error:
The column prefix 'table2' does not match with a table name or alias name used in the query. Any ideas? Thanks for any feedback or assistance.

T-SQL Code:
SELECT Table1.PrimaryID, Table1.SecondaryID,
(CASE WHEN Table2.EmailAddress IS NULL AND SecondaryID <>'1'
THEN PrimaryEmailSub.EmailAddress
ELSE
table2.EmailAddress END),
(CASE WHEN MaxCode=1 THEN 'A' ELSE
CASE WHEN MaxCode=2 THEN 'B' ELSE
CASE WHEN MaxCode=3 THEN 'C' ELSE
CASE WHEN MaxCode=4 THEN 'D' ELSE
CASE WHEN MaxCode=5 THEN 'E' END END END END END) as CustomerLevel,

CustomerType,Table1.FirstName ,Table1.LastName
FROM Table1
LEFT JOIN
(SELECT Table1.PrimaryID, Table2.EmailAddress FROM Table1 JOIN Table2 ON Table1.IDkey1=Table2.IDkey1 WHERE Table1.SecondaryId='1')PrimaryEmailSub
ON Table1.PrimaryID=PrimaryEmailSub.PrimaryID
LEFT JOIN Table2 ON Table1.IDkey1=Table2.IDkey1
JOIN Table3 ON Table1.IDKey2=Table3.IDKey3
LEFT JOIN
(SELECT IDKey3,
MAX(CASE WHEN CustomerLevel='A' THEN 1 ELSE
CASE WHEN CustomerLevel='B' THEN 2 ELSE
CASE WHEN CustomerLevel='C' THEN 3 ELSE
CASE WHEN CustomerLevel='D' THEN 4 ELSE
CASE WHEN CustomerLevel='E' THEN 5 END END END END END)
as MaxCode FROM Table4
WHERE CustomerLevel <> 'C' GROUP BY IDKey3)CustomerSub
ON Table1.IDKey3=CustomerSub.IDKey3

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-10 : 14:38:47

If your create view statement is failing, show us that, not the code that works!

- Jeff
http://weblogs.sqlteam.com/JeffS

forefj
Starting Member

10 Posts

Posted - 2012-05-10 : 14:57:44

Sorry for the confusion. I don't have a lot of experience working with T-SQL.
I realized what the issue was. I was trying to insert the CODE into the GUI Window where the VIEWS are stored. Once I used the QUERY Window and added: CREATE VIEW Viewname AS and then inserted the T-SQL code it seems to work ok now. Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-10 : 14:59:50

Great! Nice job solving your issue.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources