February 7, 2006 at 12:36 pm
This case statement below is getting a syntax error in my query:
RIGHT OUTER JOIN AmEx_Sampling.dbo.v_SY_QuestionsFormat qf
ON (qf.sy_templateid = c.sy_templateid and qf.qstid = r.qid )
AND CHARINDEX(CASE WHEN PATINDEX('%[0][1-9],%', r.datavalue) > 0
THEN CASE WHEN LEN(qf.formatvalue) > 1 THEN ''
ELSE '0'
END
ELSE ''
END
+ CAST(qf.formatvalue AS VARCHAR(2))
, r.datavalue)
WHERE c.SY_Active = 1
When I built it separately with variables it worked:
SET @sFound3 = CHARINDEX(CASE WHEN PATINDEX('%[0][1-9],%', @sMultiResp) > 0
THEN CASE WHEN LEN(@sResp) > 1 THEN ''
ELSE '0'
END
ELSE ''
END
+ CAST(@sResp AS VARCHAR(2))
, @sMultiResp)
Does anyone know why it would work with variables but not on a SELECT Query with joins? If you need more info let me know. I just cut out the actually pieces of code for the CASE. I narrowed it down to this statement.
The error message is "Incorrect syntax near the keyword 'WHERE'." The query works before I add the nested case statements to the CHARINDEX(). Thanks for the help.
February 7, 2006 at 1:54 pm
When you are creating join criteria it is formattted as.
join table on somefield = somefield
and SomotherField >= SomeotherField
etc.
Your join has
(qf.sy_templateid = c.sy_templateid and qf.qstid = r.qid )
which is fine.
But this section does hot have a valid expression.
it does hot have a comparrison. Expression should return a true.
AND CHARINDEX(CASE WHEN PATINDEX('%[0][1-9],%', r.datavalue) > 0
THEN CASE WHEN LEN(qf.formatvalue) > 1 THEN ''
ELSE '0'
END
ELSE ''
END
+ CAST(qf.formatvalue AS VARCHAR(2))
, r.datavalue) = what?
February 7, 2006 at 2:06 pm
Thank you Ray. You hit the nail on the head. I had forgotten to complete the evaluation portion of the AND clause on the join. The CHARINDEX() was returning an INT but I wasn't comparing it to anything. I needed to add "> 0" to it.
February 8, 2006 at 3:39 pm
Hi Joe,
Is this Joe Celko the author? Or are you just using his name for the forums? Either way I appreciate your response to my issue.
I would like to explain why I am doing this portion of the query the way I am. We have a table in a database, which I did not design, that has a column with comma delimited values. These values represent multiple responses to a single question. The table is built from flat files received from the application that is used to program a questionnaire. On multiple responses to one question it captures the responses and delimits them with a ",".
The portion of this query that I am using joins the responses table, w/ the column that includes the comma delimited values, to a table that has the valid responses to a question (tbQuestionFormat) . The valid responses in the tbQuestionFormat are INT datatype but the responses could have a leading 0 if there is a possible 2 digit response like 10.
Example: Q1 can have multiple responses and the response value could be 1,2,3,4,5,6,7,8,9,10,97. Because of this the single digit responses are zero filled.
In order to compare the values using the CHARINDEX() I have to convert the (formatvalue) which is an INT to a VARCHAR to add a leading 0 when necessary.
I agree with you that it requires extra formatting and CASE statements to correct. I need to separate the multiple responses that are one record in the original response table into a record for each response so I can summarize the data.
I hope that makes sense. I agree that I do need to do a little more reading on database design and programming. My programming background is from the web based side but I never really designed databases from scratch. They were usually already established. So, I have a large learning curve. I am definitely open to learning better practices. Please provide me with some recommended reading material on the subject.
Thanks again for the info. I look forward to your response and chatting further about the subject.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply