May 27, 2010 at 6:23 am
I am trying to write one query as follows:
I am getting value of @ucf from first query. I am trying to use this value in query 2 but this is not getting parsed. The problem is at like '@ucf%'. Can anyone correct me?
Query1:
declare @ucf NVARCHAR(10)
SELECT @ucf= substring(Exam,1,3)
from EmployeeInfo ed
left outer join Skills sk
on ed.Exam = sk.level1name
where empid = 161522
Query2:
select Exam, substring(Exam,1,3),sk.level1name
from EmployeeInfo ed
left outer join Skills sk
on ed.Exam = sk.level1name
where empid = 161522
and sk.level1name like '@ucf%'
May 27, 2010 at 6:25 am
Change it to
select Exam, substring(Exam,1,3),sk.level1name
from EmployeeInfo ed
left outer join Skills sk
on ed.Exam = sk.level1name
where empid = 161522
and sk.level1name like @ucf + '%'
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 28, 2010 at 5:29 am
No Kingston. This didn't work. Even when I tried as follows:
and sk.level1name like LTRIM(RTRIM(@ucf)) +'%' :(This didn't work.
May 28, 2010 at 6:18 am
Do you get an error message, or id it just that it doesn't return the data you expect?
Maybe you could try
where charindex(MyFieldName,@variable) > 0
or you might want to check your collation settings for case sensitivity
Cheers, Iain
May 29, 2010 at 5:53 am
You have to be careful with OUTER JOINs and column references in the WHERE clause.
If you reference a column from the NULL-extended table, in a way that eliminates columns containing NULLs, SQL Server will transform the OUTER JOIN to the (logically equivalent) JOIN. You need to include all conditions that reference NULL-extended columns in the ON clause of the JOIN instead.
There are a number of design problems with the query as stated, but I present the following example to demonstrate the difference:
DECLARE @EmployeeInfo
TABLE (
emp_id INTEGER NOT NULL,
exam NVARCHAR(10) NOT NULL
);
DECLARE @Skill
TABLE (
level_name NVARCHAR(10) NOT NULL
);
INSERT @EmployeeInfo (emp_id, exam) VALUES (161522, N'UCF0001A');
INSERT @EmployeeInfo (emp_id, exam) VALUES (161522, N'UCF0001B');
INSERT @EmployeeInfo (emp_id, exam) VALUES (161523, N'UCF0001A');
INSERT @Skill (level_name) VALUES (N'UCF0001A');
INSERT @Skill (level_name) VALUES (N'UCF0001C');
SELECT E.exam,
LEFT(E.exam, 3) AS ucf,
S.level_name
FROM @EmployeeInfo E
LEFT
JOIN @Skill S
ON S.level_name = E.exam
AND S.level_name LIKE LEFT(E.exam, 3) + N'%'
WHERE E.emp_id = 161522;
Please provide a good clean solid example of what you are trying to achieve here (sample data and expected output).
If you can explain the requirements well, one of us is sure to provide a good solution, and some great code.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply