Case condition is not working

Question

What I need:

I need to check if eventvisitor.published is 1 then increment by zero else eventvisitor is equal to 0.

mysql query

            SUM(
            CASE
            WHEN eventvisitor.published =1
            THEN eventvisitor.published=eventvisitor.published+0 
            ELSE eventvisitor.published=0
            END
            ) AS eventvsisitorpublished

sql query works

$qb->select('
    eve.id,
    SUBSTRING(ed.value,1,150) des,
    eve.membership,
    eve.name,
    eve.abbrName abbr_name,
    eve.membership paid,
    eve.wrapper event_wrapper,
    (case  when attach.cdnUrl is null then attach.value else attach.cdnUrl end) event_samll_wrapper,
    eve.url event_url,
    eve.website,
    eve.eventType,
    venue.name venue_name,
    e.startDate,
    e.endDate,
    ct.name city,
    c.name country,
    c.url country_url,
    c.shortname country_shortname,
    category.id industry_id,
    category.name industry_name,
    category.url industry_url,
    eve.status event_status,
    count(distinct eventvisitor.user) PeopleAttending
')
->add('from', $from);
->innerJoin('Entities\City','ct','with','eve.city = ct.id')
->innerJoin('Entities\Country','c','with','eve.country = c.id')
->innerJoin('Entities\EventEdition','e','with','eve.eventEdition = e.id')
->innerjoin('Entities\EventCategory','cat','with','e.event = cat.event')
->innerjoin('Entities\Category','category','with','cat.category = category.id')
->leftJoin('Entities\Venue','venue','with','e.venue=venue.id')
->leftJoin('Entities\EventVisitor','eventvisitor','with','eve.id=eventvisitor.event')   
//->leftJoin('Entities\Venue','v','with','v.id = e.venue')
->leftJoin('Entities\EventData','ed','with','eve.eventEdition = ed.eventEdition')
->leftJoin('Entities\Attachment','attach','with','eve.wrapperSmall = attach.id')
->Where("ed.title ='desc' or ed.title is null")
->andWhere("eve.id in (".$res.")")
->andWhere("eventvisitor.published =1")

works check in where condition.

problem I'm facing: I'm getting 500 internal error

where I have done wrong any suggestion are most welcome.

MySQL doesn't throw 500 internal errors. So what programming language are you using to interface with MySQL and what do the Apache error logs say about the 500 error? Finally, what is your code at and around the line that causes the 500 internal error? — sjagr, Dec 09 '14 at 05:11
and by the way, you can omit the case for when `eventvisitor.published =1` since it doesn't really do anything (increment by zero). — Yohanes Khosiawan 许先汉, Dec 09 '14 at 05:13
So now you've dumped QueryBuilder code that completely doesn't match up with your original "SQL" example. Something is fishy here... — sjagr, Dec 09 '14 at 05:20

score 0 · Answer 1 · answered Dec 09 '14 at 05:15

0

use this

           SUM(
           IF(eventvisitor.published ==1, eventvisitor.published , 0)
            ) AS eventvsisitorpublished

answered Dec 09 '14 at 05:15

A.B

20,110
3
37
71

query is working SUM( CASE WHEN eventvisitor.published =1 THEN 1 ELSE 0 END ) AS eventpublished but not giving disered result – user2818060 Dec 09 '14 at 05:20

Case condition is not working

1 Answers1