question

matrix388 avatar image
matrix388 asked

How to fix this SQL Query ?

I am using a StoredProcedure that was working fine until I added another CASE to it. When I added the second CASE to it, it was no longer working well and I don't why. The SQL Query: select * from ( SELECT TOP (100) PERCENT dbo.divisions.DivisionShortcut, dbo.employee.EmpOrgType, dbo.employee.Name, t1.Username, courses_2.CourseName, CASE WHEN dbo.employee_courses.courseid IS NULL THEN str(t1.courseid) + '', NO'' ELSE str(dbo.employee_courses.courseid) + '', Yes'' END AS CourseId, CASE WHEN dbo.employee.EmpOrgType 1 THEN ''Aramco'' ELSE ''SMP'' END AS Org FROM dbo.Divisions INNER JOIN dbo.courses AS courses_2 INNER JOIN (SELECT employee_1.Username, courses_1.CourseID FROM dbo.employee AS employee_1 CROSS JOIN dbo.courses AS courses_1) AS t1 ON courses_2.CourseID = t1.CourseID INNER JOIN dbo.employee ON t1.Username = dbo.employee.Username ON dbo.Divisions.SapCode = dbo.employee.DivisionCode LEFT OUTER JOIN dbo.employee_courses ON t1.Username = dbo.employee_courses.employeeId AND t1.CourseID = dbo.employee_courses.courseId ORDER BY t1.Username) DataTable When I added the following line to it: CASE WHEN dbo.employee.EmpOrgType 1 THEN ''Aramco'' ELSE ''SMP'' END AS Org I got the following error: > Msg 156, Level 15, State 1, Line 8 > Incorrect syntax near the keyword > 'WHEN'. Msg 156, Level 15, State 1, > Line 13 Incorrect syntax near the > keyword 'AS'. How to fix this error?
sql-server-2008sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
Why are you using '', NO'', '',YES'', ''Aramco'' ..... You should be using them like ',NO' ',YES' 'Aramco'. IF you want to quote them, then use QUOTENAME(field,''''). For e.g. SELECT CASE WHEN 1 = 1 THEN QUOTENAME('YES','''') ELSE QUOTENAME('NO','''') END EDIT: select * from ( SELECT TOP (100) PERCENT dbo.divisions.DivisionShortcut, dbo.employee.EmpOrgType, dbo.employee.Name, t1.Username, courses_2.CourseName, CASE WHEN dbo.employee_courses.courseid IS NULL THEN str(t1.courseid) + ', NO' ELSE str(dbo.employee_courses.courseid) + ', Yes' END AS CourseId, CASE WHEN dbo.employee.EmpOrgType = 1 THEN 'Aramco' ELSE 'SMP' END AS Org FROM dbo.Divisions INNER JOIN dbo.courses AS courses_2 INNER JOIN (SELECT employee_1.Username, courses_1.CourseID FROM dbo.employee AS employee_1 CROSS JOIN dbo.courses AS courses_1) AS t1 ON courses_2.CourseID = t1.CourseID INNER JOIN dbo.employee ON t1.Username = dbo.employee.Username ON dbo.Divisions.SapCode = dbo.employee.DivisionCode LEFT OUTER JOIN dbo.employee_courses ON t1.Username = dbo.employee_courses.employeeId AND t1.CourseID = dbo.employee_courses.courseId ORDER BY t1.Username) DataTable
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It does not matter if you are using SSMS. What is your objective? I have changed your script. Try that. BTW, the statement dbo.employee.EmpOrgType 1 is changed to dbo.employee.EmpOrgType = 1
1 Like 1 ·
I am using SQLServer Management Studio and this is why I am using this. The first CASE is OK but the problem now is with the second CASE.
0 Likes 0 ·
Thanks for your help but the second condition did not work. Still I have 1 and 2 in the retrieved results. What I want is to show CompanyName instead of 1 and SMP instead of 2 1 and 2 are float data type in the database
0 Likes 0 ·
Are you sure you are looking at the Org output column? You have dbo.employee.EmpOrgType in the SELECT list as well. If you do not want that then get rid of it in the SELECT list.
0 Likes 0 ·
Sorry, what I meant is I don't want to generate a new column. what I want is just replacing the value for the EmpOrgType column and rename it as Org. How to do that?
0 Likes 0 ·
Show more comments
Krzychokri avatar image
Krzychokri answered
what is wrong with this query? i keep getting an error.can anyone help? SELECT Pos.SupPos AS 'Parent Box ID',Pos.Pos AS 'Box ID',Pos.Dpt' AS 'Box Title', '' AS 'Record Type', '' AS 'ID', '' AS 'Last Name', '' AS 'First Name', '' AS 'Middle Name',Pos.Job AS 'Job ID',Pos.PosTitle AS 'Job Title', '' AS 'Blank', '' AS 'Box Sequence Number', '' AS 'Job Sequence Number', '' AS 'Position Sequence Number', '' AS Photo, '' AS Blank2, '' AS Blank3, '' AS 'Level Number', '' AS Blank4, Pos.SubRegCZ AS 'Sub-Region',Pos.Ent AS 'OpCo',Pos.Cmp AS 'Entity',Pos.Div AS 'Functional Area', Pos.Org AS 'Sub-Functional Area',Pos.Unt AS 'Manager',Pos.Loc AS 'Location', '' AS 'Work Place', '' AS 'Extension', '' AS 'Email Address', '' AS 'OpCo Cost Center', '' AS 'CIL Cost Center', '' AS 'Employment Status',Pos.WrkSts AS 'Work Status',Pos.PosSts AS 'Position Status' FROM POS WHERE PosSts = 'empty' AND (POS.POSEfdDt IS NULL) ORDER BY Pos.SupPos
10 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Try asking this as a new question, rather than as an answer to an old question - I've just spent five minutes looking at the old question...
0 Likes 0 ·
Take out the ' after Pos.Dpt.
0 Likes 0 ·
And then try reformatting your query so it looks more like SELECT Pos.SupPos AS 'Parent Box ID' , Pos.Pos AS 'Box ID' , Pos.Dpt AS 'Box Title' , .... That'll make it easier to see what's going on... http://www.format-sql.com is a starting point; [Red-Gate's SQLPrompt][1] is a more expensive better solution - it embeds within SSMS... ;-) [1]: http://www.red-gate.com/products/sql-development/sql-prompt/
0 Likes 0 ·
So i did that..removed the ' ..and i still get this error: Message= msg=Syntax msgtext=The data you have entered is incorrect. The following information will be used by Support to assist in troubleshooting. Data Error: Code=Syntax Object=NuIteratorRowDb p1=AS sql=SELECT Pos.SupPos AS 'Parent Box ID',Pos.Pos AS 'Box ID',Pos.Dpt AS 'Box Title', AS 'Record Type', AS ID, AS 'Last Name', AS 'First Name', AS 'Middle Name',Pos.Job AS 'Job ID',Pos.PosTitle AS 'Job Title', AS Blank, AS 'Box Sequence Number', AS 'Job Sequence Number', AS 'Position Sequence Number', AS Photo, AS Blank2, AS Blank3, AS 'Level Number', AS Blank4, Pos.SubRegCZ AS 'Sub-Region',Pos.Ent AS 'OpCo',Pos.Cmp AS 'Entity',Pos.Div AS 'Functional Area', Pos.Org AS 'Sub-Functional Area',Pos.Unt AS 'Manager',Pos.Loc AS 'Location', AS 'Work Place', AS 'Extension', AS 'Email Address', AS 'OpCo Cost Center', AS 'CIL Cost Center', AS 'Employment Status', Pos.WrkSts AS 'Work Status',Pos.PosSts AS 'Position Status' FROM POS WHERE PosSts = 'empty' AND (POS.POSEfdDt IS NULL) ORDER BY Pos.SupPos orig=Incorrect syntax near the keyword '[p1]'. msgtype=error^^-Delim-^^
0 Likes 0 ·
Yeah, see, what's happened is something is knocking out all your bits where you have SELECT ..., '' AS foo, '' AS bar, ... and converting them to SELECT ..., AS foo, AS bar, ... which makes no sense.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.