Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new here so be kind to me. I have the following scenario:

I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value in this view representing if it is one kind of event or another (a simple boolean), which I tried to achieve with:

`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement` AS `IsStopingEvent`

The result is represented as int, and so is read by Entity Framework. The problem is that I really need a boolean return value, which I tried to achieve with:

CAST((`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement`) as boolean) AS `IsStopingEvent` 

This resulted in an error, one that is not displayed to me in MySQL Workbench (I only receive that annoying "You have an error in ...").

Can you guys please help me out?

Tried to solve it in my application, but I really preffer this solved in the database, since it will be used by other software later.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Try using the IF function:

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`,TRUE,FALSE) FROM ...

or

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`,1,0) FROM ...

Even without the IF function, running

mysql> select ('rolando' = 'rolando') str_compare;
+-------------+
| str_compare |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>

yield 0 or 1 using the mysql client

The problem is this: CAST() and CONVERT() can only accept and return the following types:

  • BINARY[(N)]
  • CHAR[(N)]
  • DATE
  • DATETIME
  • DECIMAL[(M[,D])]
  • SIGNED [INTEGER]
  • TIME
  • UNSIGNED [INTEGER]

Since BOOLEAN is not in this list, it can never be returned by CAST or CONVERT

You could use the IF function to generate strings

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`,'TRUE','FALSE') FROM ...
share|improve this answer
    
if(gu.StoppingUnitEventME = ese.MonitoringElement, true, false) AS IsStopingEvent returns 0 and 1. –  Bruno Feb 9 '12 at 19:07
    
The problem is that it is somehow yelded to Int, where it should at least be bit, which I could use to do the mapping. The cast function doesn't intend to do that? Sorry if the question sounds dumb, I just don't have that much of a DB knowledge. –  Bruno Feb 9 '12 at 19:15
    
Basically what you're saying is that in a View it is impossible to have a boolean value based on a calculation? –  Bruno Feb 9 '12 at 19:26
    
Could it return 2 strings, instead of 0 and 1? –  Bruno Feb 9 '12 at 19:28
    
Yes, you can. You need to use the IF function. –  RolandoMySQLDBA Feb 9 '12 at 19:29
add comment

You can do very simple way, without use of redundant IF() statement:

... `YourField` IS NOT NULL AS `YourField` ...
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.