create view `loanspaidregister` AS

select `pawnshop_tbl`.
`PawnTicketNo` AS `PT No`

, concat(`pawnshop_tbl`.`LastName`,_latin1' ',`pawnshop_tbl`.`FirstName`) AS `Name`

, `pawnshop_tbl`.`DateLoan` AS `Date Loan`

, if((`pawnshop_tbl`.`ServiceType` = _latin1'Renew')
  , `pawnshop_tbl`.`DateLoan`,'') AS `Date Renew`

, `pawnshop_tbl`.`DateRedeem` AS `Date Redeem`

, if(((to_days(`pawnshop_tbl`.`ExpiryDate`) - to_days((curdate() + 3))) < 0)
  ,`pawnshop_tbl`.`ExpiryDate`,'') AS `Date Auction`,'' AS `Date Sold`

, if((`pawnshop_tbl`.`Principal` between 100 and 500),`pawnshop_tbl`.`Principal`,'') AS `100 - 500`

, if((`pawnshop_tbl`.`Principal` between 501 and 1999),`pawnshop_tbl`.`Principal`,'') AS `501 - 1999`

, if((`pawnshop_tbl`.`Principal` >= 2000),`pawnshop_tbl`.`Principal`,'') AS `2000 - UP`

, if((`pawnshop_tbl`.`ServiceType` = _latin1'Redeem'),`pawnshop_tbl`.`Principal`,'') AS `Principal Paid`

, if(((`pawnshop_tbl`.`ServiceType` = _latin1'Redeem') or (`pawnshop_tbl`.`ServiceType` = _latin1'Renew'))
  ,format( (`pawnshop_tbl`.`Principal` * `pawnshop_tbl`.`Interest`) , 2),'') AS `Interest Paid`

, if((`pawnshop_tbl`.`ServiceType` = _latin1'Redeem')
   ,  (`pawnshop_tbl`.`Principal` +  format( (`pawnshop_tbl`.`Principal` * `pawnshop_tbl`.`Interest`),2 ) )
      , if((`pawnshop_tbl`.`ServiceType` = _latin1'Renew')
        , format( (`pawnshop_tbl`.`Principal` * `pawnshop_tbl`.`Interest`), 2) ,'')) AS `Cash Received`

, `pawnshop_tbl`.`Description` AS `Description`

, `pawnshop_tbl`.`Branch` AS `branch`
, '' AS `Gain/Loss`

from `pawnshop_tbl`

union

select
`sold_tbl`.`idpawnshop_tbl` AS `PT no`
, concat(`sold_tbl`.`LastName`,_latin1' ',`sold_tbl`.`FirstName`) AS `Name`

, `sold_tbl`.`DateLoan` AS `Date Loan`

, '' AS `Date Renew`

, '' AS `Date Redeem`
, '' AS `Date Auction`

, `sold_tbl`.`DateSold` AS `Date Sold`

, if((`sold_tbl`.`AuctionPrice` between 100 and 500),`sold_tbl`.`AuctionPrice`,'') AS `100 - 500`

, if((`sold_tbl`.`AuctionPrice` between 501 and 1999),`sold_tbl`.`AuctionPrice`,'') AS `501 - 1999`

, if((`sold_tbl`.`AuctionPrice` >= 2000),`sold_tbl`.`AuctionPrice`,'') AS `2000 - UP`

, `sold_tbl`.`Principal` AS `Principal Paid`

, '' AS `Interest Paid`

, `sold_tbl`.`AuctionPrice` AS `Cash Received`

, `sold_tbl`.`Description` AS `Description`

, `sold_tbl`.`Branch` AS `branch`

, (`sold_tbl`.`AuctionPrice` - `sold_tbl`.`Principal`) AS `Gain/Loss`

from `sold_tbl`;

Illegal mix of collations for operation 'UNION'