Mysql query return BLOB with Union Join

Question

When I run a Union query in mysql some values return as BLOB, is there a way to fix this? I union the query because i want the sum of specific columns to be at the bottom

select a.app_file_id,a.app_trn,a.app_fname,a.app_lname,l.computer_interest,l.loan_life,l.app_ln_amnt, l.commit_date,l.app_amnt_owed,r.amount_paid
from applicant a
left join loan l on   l.l_app_file_id=a.app_file_id
left join receipt r on r.r_app_file_id=l.l_app_file_id
WHERE l.app_loan_type=r.receipt_loan_type
AND l.app_loan_type='Computer Loan'
AND  MONTHNAME(commit_date) = 'April'
and YEAR(commit_date)=2012

Union
Select '', '','','','','',SUM(l.app_ln_amnt),'',SUM(l.app_amnt_owed),SUM(r.amount_paid)
from applicant a
left join loan l on   l.l_app_file_id=a.app_file_id
left join receipt r on r.r_app_file_id=l.l_app_file_id
WHERE l.app_loan_type=r.receipt_loan_type
AND l.app_loan_type='Computer Loan'
AND  MONTHNAME(commit_date) = 'April'
and YEAR(commit_date)=2012

The colums that are returning blob are; s_app_trn which is INT(9),computer_interest which is Double,loan_life which is int(11) and commit_date which is DATE — dames
– dames, Commented Apr 11, 2012 at 15:40

Scott Hunter · Accepted Answer · 2012-04-11 16:27:23Z

2

In the first query of the UNION, the second column is an INT, while in the second query, it is an empty string, which isn't a valid INT. Similarly for the 5th, 6th and 8th columns.

answered Apr 11, 2012 at 16:27

Scott Hunter

50k12 gold badges64 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dames Over a year ago

So you R saying I should put an INT in it? and what about the Date column

Scott Hunter Over a year ago

To avoid a BLOB, the types of the values in a column need to match. Dates can be specified as strings, but must be in year-month-day format.

Mateen Over a year ago

@ScottHunter thanks for the explanation based on you explanation i had provided an example below, if there is something that can be improvised please do it.

Mateen · Accepted Answer · 2015-04-10 11:53:39Z

0

one easy way to avoid blob in union is by using format. consider the following examples

EG:

select "string 1","string 2","string 3" from dual
union
select "string 1",format(sum(1+2),0),"string 3" from dual

but i wouldn't recommend so. Make the result column types exact identical across all union and avoid using format to avoid "BLOB" in result

select "string 1",0 "string 2","string 3" from dual
union
select "string 1",sum(1+2),"string 3" from dual

answered Apr 10, 2015 at 11:53

Mateen

1,6911 gold badge23 silver badges28 bronze badges

Collectives™ on Stack Overflow

Mysql query return BLOB with Union Join

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related