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?
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
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
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.
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
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...
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/
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-^^
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.
I'm guessing that this code is in an application somewhere, yes? What I would recommend is that, just before you execute the SQL statement, you print it out / dump it into your application trace log files (you *do* have these, right?), or, hell, just spit it out into a messagebox so you can C&P it into SSMS to check that it actually works.
i added " where i think they should go...is this okay below? 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
question... I am getting an error with this query 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) please help me!!
ask it as a separate question, rather than come in to an old one?
2 People are following this question.
