Case statement is changing bit to blob datatype in mysql.

Question

0.00/5 (No votes)

See more:

I have stored procedure in MySQL and its returns a field paidStatus. I am expecting the field to return a bit value. The field paidStatus is a combination of a derived field and column value. While executing the procedure the O/P is comming as BLOB object.

declare is_excelUpload bit;

SQL

set is_excelUpload  = (select 1 from hireRequestFileUpload where jobProfileHireRequestId = jobProfileHireRequest_Id limit 1);

SQL

case when is_excelUpload =1 then 1 else paidStatus end as paidStatus

Please suggest how to eliminate this issue, or why the datatype is getting changed.

Posted 13-May-15 1:06am

Tijo Joseph

Updated 16-May-15 5:44am

Herman<T>.Instance

v7

Add a Solution

Comments

Kornfeld Eliyahu Peter 13-May-15 7:38am

How paidStatus is declared? Is it a bit?

Tijo Joseph 13-May-15 8:06am

paidStatus is column having bit datatype

virusstorm 16-May-15 10:12am

Is paidStatus a nullable column?

Tijo Joseph 17-May-15 13:18pm

Yes

Herman<T>.Instance 16-May-15 11:45am

change
<pre lang="SQL">case when is_excelUpload =1 then 1 else paidStatus end as paidStatus </pre>
to <pre lang="SQL">case when is_excelUpload =1 then 1 else 0 end as paidStatus </pre>

Tijo Joseph 17-May-15 13:19pm

Can you explain this?

Herman<T>.Instance 17-May-15 14:02pm

what is the valuetype of paidStatus before the case? in the SET statement it is never filled when is_excelUpload =0. Init error/feature I think

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)