asked on

how do i modify this mysql query

Hi Experts,

query:
select u.idnumber,IFNULL(CASE WHEN rs.choice_id IS NULL THEN rt.response ELSE bc.content END,'---') as content,q.name from mdl_user as u JOIN mdl_questionnaire_response as qr ON u.id= qr.username JOIN mdl_questionnaire_question as q ON qr.survey_id = q.survey_id LEFT JOIN mdl_questionnaire_response_text as rt ON rt.question_id = q.id AND rt.response_id = qr.id LEFT JOIN mdl_questionnaire_response_single as rs ON rs.response_id = qr.id AND rs.question_id = q.id LEFT JOIN mdl_questionnaire_question_choice as bc ON bc.id = rs.choice_id where q.required='Y' AND q.deleted='N' AND idnumber=1527858;

problem with:
IFNULL(CASE WHEN rs.choice_id IS NULL THEN rt.response ELSE bc.content END,'---') as content

i am not able to convert this peace of line into hibernate hql query
is it possible to convet the above sql query any other alternative way?

(CASE WHEN rs.choice_id IS NULL THEN rt.response ELSE bc.content END)
i am able to convert this peace of line into hql query
is the both lines are same or
is there any difference b/w

IFNULL(CASE WHEN rs.choice_id IS NULL THEN rt.response ELSE bc.content END,'---') as content

(CASE WHEN rs.choice_id IS NULL THEN rt.response ELSE bc.content END)as content

when i am executing the query with hql

(CASE WHEN rs.choice_id IS NULL THEN rt.response ELSE bc.content END)as content

content column i am getting the result like
 

if the same query when i am executing with sql i am getting the values like
Storage Display
...and so on

why i am getting the value like this 
can any one suggest me.

if both are not same then suggest me alternative sql query for
IFNULL(CASE WHEN rs.choice_id IS NULL THEN rt.response ELSE bc.content END,'---') as content

Thanks

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

srikotesh

ASKER

content column i am getting the result like
 
but when i execute sql query it is giving results
could you please verify the query

I am getting total 5records in both the cases I mean with sql and hql
but content column data is not coming with hql query
for all 5records it is showing .

srikotesh

ASKER

hql query:
select mdluser1_.idnumber as col_0_0_, 
case when (mdlquestio4_.choice_id is null) and (mdlquestio3_.response is null) and (mdlquestio5_.content is null) then '---'
when (mdlquestio4_.choice_id is null) and (mdlquestio3_.response is not null) then mdlquestio3_.response 
when (mdlquestio3_.response is null) and (mdlquestio4_.choice_id is not null) then mdlquestio4_.choice_id else mdlquestio5_.content end as col_1_0_, mdlquestio2_.name as col_2_0_, mdlquestio0_.username as col_3_0_ from mdl_questionnaire_response mdlquestio0_ inner join mdl_user mdluser1_ on mdlquestio0_.username=mdluser1_.id inner join mdl_questionnaire_question mdlquestio2_ on mdlquestio0_.survey_id=mdlquestio2_.survey_id left outer join mdl_questionnaire_response_text mdlquestio3_ on mdlquestio0_.id=mdlquestio3_.id left outer join mdl_questionnaire_response_single mdlquestio4_ on mdlquestio0_.id=mdlquestio4_.id left outer join mdl_questionnaire_question_choice mdlquestio5_ on mdlquestio4_.choice_id=mdlquestio5_.id where mdlquestio2_.required='Y' and mdlquestio2_.deleted='N' and mdluser1_.idnumber=445353;
+----------+----------+---------------------+----------+
| col_0_0_ | col_1_0_ | col_2_0_            | col_3_0_ |
+----------+----------+---------------------+----------+
| 445353   | <p></p>  | Turn around         | 7045     |
| 445353   | <p></p>  | Technical Knowledge | 7045     |
| 445353   | <p></p>  | Quality             | 7045     |
| 445353   | <p></p>  | OverallSupport      | 7045     |
| 445353   | <p></p>  | Resolution          | 7045     |
| 445353   | <p></p>  | Qualitiatives       | 7045     |
+----------+----------+---------------------+----------+


mysql> select u.idnumber,IFNULL(CASE WHEN rs.choice_id IS  NULL THEN rt.response ELSE bc.content END,'---') as content,q.name from mdl_user as u JOIN mdl_questionnaire_response as qr ON u.id= qr.username JOIN mdl_questionnaire_question as q ON qr.survey_id = q.survey_id LEFT JOIN mdl_questionnaire_response_text as rt ON rt.question_id = q.id AND rt.response_id = qr.id LEFT JOIN mdl_questionnaire_response_single as rs ON rs.response_id = qr.id AND rs.question_id = q.id LEFT JOIN mdl_questionnaire_question_choice as bc ON bc.id = rs.choice_id where q.required='Y' AND q.deleted='N' AND idnumber=445353;
+----------+-------------------+---------------------+
| idnumber | content           | name                |
+----------+-------------------+---------------------+
| 445353   | Strongly Disagree | Turn around         |
| 445353   | Strongly Disagree | Technical Knowledge |
| 445353   | Strongly Disagree | Quality             |
| 445353   | Strongly Disagree | OverallSupport      |
| 445353   | Strongly Disagree | Resolution          |
| 445353   | ---               | Qualitiatives       |
+----------+-------------------+---------------------+
6 rows in set (1.12 sec)

Open in new window

please verify the query.

srikotesh

ASKER

rs.choice_id IS NULL for last record and rt.response also null so we got '----' for the last record.
and rest of the cases rs.choice_id and rt.response is not null for both

PortletPaul

How do i access tables and data to "verify" the query?

Please provide a small sample of representative data sufficient for me to run the query. Thae DDL for the tables is welcome also.