Case Function In Sqlyog

[jelanier]

I'm a relative newbie in msql. I would like to know how to construct a select statement using the case function?

I've tried different variations of the follwing & I get an error no matter what I try.

select provider_id,from patient_transactions

where

case(location_id) when '7' then sum(account_tot) as office,

when '46' then sum(account_tot) as inpt,

when '47' then sum(account_tot) as otpt,

when '48' then sum(account_tot) as emr end

and (date_of_service between '2007-01-01' and '2007-08-31'),

group by provider_id

Iwould appreciate any help. I have about 4 or 5 books on MSQL & they all gloss over the case function.

[peterlaursen]

You can use CASE as a 'Control FLow statement' (SELECT CASE …) or you can use CASE inside (between BEGIN .. END) a Stored Procedure, Function, Trigger or Event ('stored program') The syntax is slightly different.

Please refer to:

http://dev.mysql.com/doc/refman/5.0/en/con…-functions.html

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

(but note that 'stored programs are only available from MySQL 5.0)

control flow hint:

not SELECT … WHERE … CASE …

… but SELECT CASE WHEN ..THEN ..[WHEN ..THEN .. ][ELSE …] END

stored program hint:

BEGIN

CASE WHEN … THEN ..[WHEN THEN…][ELSE …] END CASE

END

[peterlaursen]

try those

SELECT CASE WHEN (select 9999) = 0 THEN 'new math arrived' WHEN (select (0<1)) THEN 'it's a hit' ELSE 'I give up' END;

SELECT CASE WHEN (select 9999) = 0 THEN 'new math arrived' WHEN (select (0>1)) THEN 'it's a hit' ELSE 'I give up' END;

SELECT CASE WHEN (select 5+2 = 7) THEN 'same old story' WHEN (select (0<1)) THEN 'it's a hit' ELSE 'I give up' END;

(note that both “(select 5+2 = 7)” and “(select (0<1))" are true - but the statement exits 1st time a condition is satisfied)

Also you can use 'case values' after CASE like

SELECT CASE 7 WHEN (2+5) THEN 'same old story' WHEN 4+3 THEN 'it's a hit' ELSE 'I give up' END;

SELECT CASE 7 WHEN (4+3) THEN 'it's a hit' WHEN (2+5) THEN 'same old story' ELSE 'I give up' END;

(and again the statement exists 1st time a condition is satisfied)

more:

SELECT CASE 2+6 WHEN 4+3 THEN 'it's a hit' WHEN (2+5) THEN 'same old story' ELSE 'I give up' END;

SELECT CASE (select 2+6) WHEN 4+3 THEN 'it's a hit' WHEN (2+5) THEN 'same old story' ELSE 'I give up' END; –identical

SELECT CASE select 2+6 WHEN 4+3 THEN 'it's a hit' WHEN (2+5) THEN 'same old story' ELSE 'I give up' END; –oops 🙂 can't resolve this!

SELECT CASE (select concat('a','b')) WHEN 'ab' THEN 'it's a hit' WHEN 'xy' THEN 'no .. you won't fool me' ELSE 'I give up' END; — case value need not be a number

SELECT CASE (select concat((select('a')),'b')) WHEN 'ab' THEN 'it's a hit' WHEN 'xy' THEN 'no .. you won't fool me' ELSE 'I give up' END; — not number here either

SELECT CASE (select concat(select('a'),'b')) WHEN 'ab' THEN 'it's a hit' WHEN 'xy' THEN 'no .. you won't fool me' ELSE 'I give up' END; — oops again!

but why “CASE (select 2+6) …” and “CASE (select concat((select('a')),'b') ..” works and not “CASE select 2+6 …” and “select concat(select('a'),'b') ..”

.. well, ask MySQL AB. Just to point out that your header “CASE in SQLyog” is wrong actually! CASE is implemented in the server – not the client, so correct would be “CASE in (My)SQL”

[peterlaursen]

select case

when

(select location_id from patient_transactions where location_id) = 7

then

select (sum(account_tot)) as office from patient_transactions where location_id = 7

when

(select location_id from patient_transactions …. ) = 46

then

select (sum(account_tot)) as inpt from patient_transactions where location_id = 46

when

…

then

[else …]

end

I think it must be something like this above you are looking for. But I do not fully understand!

In particular I am not able to understand what is the idea with the GROUP BY!

And basically I doubt that the CASE solution is the solution to your problem.

Why not simply

select (sum(account_tot)) from patient_transactions group by location_id;

— to me it seems as if the only reason for the CASE is that you want a different alias for every sum depending on the location_id.

Why not store this alias in the database itself for very value of 'location_id'?

Using a 'parent table' and including this with a JOIN type query seems to me a more logical design! It seems that you are trying to solve IN CODE what should be solved in DATABASE DESIGN.

But I do not claim to understand everything!

[jelanier]

What I was trying to accomplish was, to gather statistics about revenue generated by a new office. We are a group of Orthopedic Surgeons & our administrator lamented that he had reports that gave him an accurate picture of what our cost were from this new facility but the reporting system did not provide him with how much revenue had been generated.

Our system is a unix based system and the database is a collection of flat files that are synchronized with an MSQL Database.

I wanted to present by doctor revenue columns Office, In Patient, Out Patient, Emgergency Room, ie location codes.

Thank you for your help, I will try to absorb your suggestions, but at present I only have rights to inquire into the database, not add to it. I haven't tried to build temporary tables but I may be able to do that.

peterlaursen wrote on Sep 27 2007, 11:20 PM:

select case

when

(select location_id from patient_transactions where location_id) = 7

then

select (sum(account_tot)) as office from patient_transactions where location_id = 7

when

(select location_id from patient_transactions …. ) = 46

then

select (sum(account_tot)) as inpt from patient_transactions where location_id = 46

when

…

then

[else …]

end

I think it must be something like this above you are looking for. But I do not fully understand!

In particular I am not able to understand what is the idea with the GROUP BY!

And basically I doubt that the CASE solution is the solution to your problem.

Why not simply

select (sum(account_tot)) from patient_transactions group by location_id;

— to me it seems as if the only reason for the CASE is that you want a different alias for every sum depending on the location_id.

Why not store this alias in the database itself for very value of 'location_id'?

Using a 'parent table' and including this with a JOIN type query seems to me a more logical design! It seems that you are trying to solve IN CODE what should be solved in DATABASE DESIGN.

But I do not claim to understand everything!

[Author]

Posts