CODEWITHSUNDEEP
mysqlblobunion
dames
dames

Reputation: 1481

Mysql query return BLOB with Union Join

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

Upvotes: 1

Views: 3416

Answers (2)

Mateen
Mateen

Reputation: 1671

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

Upvotes: 0

Scott Hunter
Scott Hunter

Reputation: 49803

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.

Upvotes: 2

Related Questions