1

So I have a piece of very repetitive code which needs to be minimized because the LMS I am required to use requires max 2000 character input. This 2000 characters is the "CASE" statement, not the "FROM" statement which comes later.

Basically, I have a feedback form that users submit through the LMS, and I want to take that information and build a report from the multiple tables.

Here is the code to get the response:

case when 
        (select e.response_value 
        from t_qti_item_evaluation r INNER JOIN t_qti_response e 
            ON r.response_id = e.response_id INNER JOIN t_qti_evaluationflowmaterial f 
            ON f.subitem_id = r.subitem_id INNER JOIN [t_qti_evaluationmaterial] i 
            ON i.flowmaterial_id = f.flowmaterial_id 
                where i.flowmaterial_id = 1385628 
                    and i.position = 1 
                    and r.item_evaluation_id = b.item_evaluation_id) = 1 then 'Highly Satisfied' 
  when 
    (select e.response_value 
        from t_qti_item_evaluation r INNER JOIN t_qti_response e 
            ON r.response_id = e.response_id INNER JOIN t_qti_evaluationflowmaterial f 
            ON f.subitem_id = r.subitem_id INNER JOIN [t_qti_evaluationmaterial] i 
            ON i.flowmaterial_id = f.flowmaterial_id 
                where i.flowmaterial_id = 1385628 
                    and i.position = 1 
                    and r.item_evaluation_id = b.item_evaluation_id) = 2 then 'Satisfied' 
  when 
    (select e.response_value 
        from t_qti_item_evaluation r INNER JOIN t_qti_response e 
            ON r.response_id = e.response_id INNER JOIN t_qti_evaluationflowmaterial f 
            ON f.subitem_id = r.subitem_id INNER JOIN [t_qti_evaluationmaterial] i 
            ON i.flowmaterial_id = f.flowmaterial_id 
                where i.flowmaterial_id = 1385628 
                    and i.position = 1 
                    and r.item_evaluation_id = b.item_evaluation_id) = 3 then 'Disatisfied' 
  when 
    (select e.response_value 
        from t_qti_item_evaluation r INNER JOIN t_qti_response e 
            ON r.response_id = e.response_id INNER JOIN t_qti_evaluationflowmaterial f 
            ON f.subitem_id = r.subitem_id INNER JOIN [t_qti_evaluationmaterial] i 
            ON i.flowmaterial_id = f.flowmaterial_id 
                where i.flowmaterial_id = 1385628 
                    and i.position = 1 
                    and r.item_evaluation_id = b.item_evaluation_id) = 4 then 'Highly Disatisfied' 
  when 
    (select e.response_value 
        from t_qti_item_evaluation r INNER JOIN t_qti_response e 
            ON r.response_id = e.response_id INNER JOIN t_qti_evaluationflowmaterial f 
            ON f.subitem_id = r.subitem_id INNER JOIN [t_qti_evaluationmaterial] i 
            ON i.flowmaterial_id = f.flowmaterial_id 
                where i.flowmaterial_id = 1385628 
                and i.position = 1 
                and r.item_evaluation_id = b.item_evaluation_id) = 0 then 'Neither' else '' end

from 

t_qti_evaluation_status a
INNER JOIN t_qti_item_evaluation b
ON a.evaluation_id = b.item_evaluation_id 
INNER JOIN t_qti_evaluation c
ON a.content_id = c.content_id 
INNER JOIN t_qti_content d
ON a.content_id = d.content_id
INNER JOIN t_qti_response e
ON b.response_id = e.response_id
INNER JOIN t_qti_evaluationflowmaterial f
ON f.subitem_id = b.subitem_id
INNER JOIN t_qti_evaluationmaterial i
ON i.flowmaterial_id = f.flowmaterial_id
INNER JOIN person p
ON a.candidate_id = p.person_id

So the only thing that changes is whether the response is a 1, 2, 3, 4, or 0 (the values I associated to the response in the feedback form). Also, some alterations need to be made to include more "i.flowmaterial_id" which is why the code fails (too many characters)

As you can see, very repetitive, so I created this (which works in SQL but not in the LMS):

declare @test varchar(100) = (select e.response_value
    from 
        t_qti_item_evaluation r INNER JOIN t_qti_response e
            ON r.response_id = e.response_id INNER JOIN t_qti_evaluationflowmaterial f
            ON f.subitem_id = r.subitem_id INNER JOIN [t_qti_evaluationmaterial] i
            ON i.flowmaterial_id = f.flowmaterial_id
    where 
        i.position = 1 and i.flowmaterial_id = 1246978 
--connects to the outer query
--and r.item_evaluation_id = b.item_evaluation_id
        ), 

        @result varchar(100)

    select @result =     CASE  @test 
            WHEN  1 THEN 'Highly Satisfied'   
            WHEN  2 THEN 'Satisfied'
            WHEN  3 THEN 'Dissatisfied' 
            WHEN  4 THEN 'Highly Dissatisfied'
            WHEN  0 THEN 'Neither' 
        END    

        print @result

The problem with this is that the LMS will ONLY take a select statement as an input, it is not something that you can just dump code and it runs it.

So what I am asking is, is it possible to improve the code a lot so that I am able to add more IDs, or somehow get the 2nd code within a select statement so that it can be put in the LMS.

Not sure what SQL version the LMS is using, but I am testing in SQL 2014

Sorry for the extended question, it is just specific.

1
  • If you need to join it to the main query, you could use an apply. e.g. select case x.response_value when 1 then 'Highly Satisfied' when 2 then 'Satisfied' etc end from [all my other joins] outer apply (select e.response_value...) x Commented Dec 13, 2016 at 2:27

1 Answer 1

1

Use the other form of the case:

(case (select e.response_value 
       from t_qti_item_evaluation r INNER JOIN t_qti_response e 
            ON r.response_id = e.response_id INNER JOIN t_qti_evaluationflowmaterial f 
            ON f.subitem_id = r.subitem_id INNER JOIN [t_qti_evaluationmaterial] i 
            ON i.flowmaterial_id = f.flowmaterial_id 
       where i.flowmaterial_id = 1385628 and
             i.position = 1 and
             r.item_evaluation_id = b.item_evaluation_id
      )
      when 1 then 'Highly Satisfied' 
      when 2 then 'Satisfied' 
      when 3 then 'Disatisfied' 
      when 4 then 'Highly Disatisfied' 
      when 0 then 'Neither'
      else ''
 end)
Sign up to request clarification or add additional context in comments.

1 Comment

Perfect, thanks for that, works very well. (just removed the " =1 ")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.