tags:

views:

72

answers:

2

Using C#.Net and MYSQL

I want to use a CASE Statement like SQL

Query

SELECT vehicleno, 
       brandname, 
       CASE 
         WHEN inexpire < 0 THEN 'Expired' 
         ELSE inexpire 
       END AS inexpire 
FROM   (SELECT Concat(plateno, vehicleno)   AS vehicleno, 
               brandname, 
               Datediff(inedate, Curdate()) AS inexpire 
        FROM   tb_car 
        WHERE  inedate < DATE_ADD(Curdate(), INTERVAL 30 DAY) 
        GROUP  BY vehicleno) AS tab1 

When I run the Query it is showing a output as

System.Byte[] instead of Expired

In the above query inexpire is column values

How to solve this issue.....

Need Query Help

+1  A: 

IF(inexpire < 0, 'Expired',inexpire) AS inexpire

Mchl
+4  A: 

I think you would need to use the same datatype for both cases

e.g.

case when inexpire < 0 then 'Expired' else 'inexpired' end as inexpire

or

 case when inexpire < 0 then 1 else inexpire end as inexpire

Edit

Actually I just did a quick test on my machine this mixing of datatypes doesn't cause an error but the result just shows as Blob in MySQL Workbench

SELECT  
cast( CASE  WHEN inexpire < 0 THEN 'Expired' ELSE inexpire END as char) as inexpire

stops this issue as suggested here. This should thus avoid the Byte[] issue

Martin Smith
Nathan Ernst
@Nathan - Maybe it does just cast the number to a string actually. I'm not in a position to test this at the moment.
Martin Smith
MySQL's behaviour towards such expression can be configured. I think that by default it will issue a warning at most.
Mchl
@Mhcl - I just did a quick test and it doesn't raise an error but shows up as `blob` in MySQL Workbench
Martin Smith
inexpired is a table column values, no need to give in the single quotation, I tried your query when i use the else condition it is showing sytem.Byte error...., why it is coming, mysql why not taking the else statement
Gopal
@Gopal - Try the version in the edit I just made?
Martin Smith
It's Working, Thank You.....
Gopal