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
<p> </p>

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 <p> </p>
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
LVL 2
srikoteshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Because all three tables are LEFT JOINs (aliases: rs rt & bc) you have the potential for all 3 columns to be NULL and, the following 2 lines of SQL code are NOT the functionally equivalent

1.

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

2.

IFNULL(CASE WHEN rs.choice_id IS  NULL THEN rt.response ELSE bc.content END,'---')
code line 2 does something if ALL of these columns rs.choice_id & rt.response & bc.content are NULL

Try the following case expression (it's untested) but I think it replaces the IFNULL()

      CASE
            WHEN rs.choice_id IS NULL AND rt.response IS NULL AND bc.content IS NULL THEN '---'
            WHEN rs.choice_id IS NULL AND rt.response IS NOT NULL THEN rt.response
            WHEN rt.response IS NULL AND rs.choice_id IS NOT NULL THEN rs.choice_id
            ELSE bc.content
      END

But you should setup a test case and test it for yourself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srikoteshAuthor Commented:
content column i am getting the result like 
<p> </p>
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 <p></p>.
0
srikoteshAuthor Commented:
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.
0
srikoteshAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.