Truncated incorrect INTEGER value

homer.favenir · #1 (**permalink**) 03-05-09, 02:25

hi,
im getting this error

Code:

Truncated incorrect INTEGER value

my query is

Code:

SELECT
  old.PawnTicketNo as 'PT No'
  ,concat(old.LastName, ' ' ,old.FirstName) as 'Name'
  , if( old.ServiceType = 'Renew', `new`.DateLoan, old.DateLoan ) as 'Date Loan'
  , if( old.ServiceType = 'Renew',  old.DateLoan, `new`.Dateloan ) as 'Date Renew'
  , old.DateRedeem as 'Date Redeem'
  , '' as 'Date Auction'
  ,'' as 'Date Sold'
  , if(old.principal between 100 and 500, old.principal, '' ) as '100 - 500'
  , if(old.principal between 501 and 1999, old.principal, '' ) as '501 - 1999'
  , if(old.principal >= 2000, old.principal, '' ) as '2000 - UP'
  ,format( if( old.ServiceType = 'Redeem', old.Principal,
      if(old.ServiceType = 'Renew', case when old.Principal != `new`.Principal THEN ( `new`.Principal - old.Principal )
      ELSE 0 END, 0  ) ), 2 ) as 'Principal Paid'
  , format( if(old.ServiceType = 'Renew',  ( `new`.Principal * `new`.Interest ),
         if( old.ServiceType = 'Redeem',( old.Principal * old.Interest ), 0 ) ), 2 ) 'Interest Paid'

  ,  if( old.ServiceType = 'Redeem',  old.Principal + ( old.Principal * old.Interest ),
      if( old.ServiceType = 'Renew' AND old.Principal != `new`.Principal,
         ( `new`.Principal - old.Principal ) +  ( `new`.Principal * `new`.Interest )
        , ( `new`.Principal * `new`.Interest ) ) )  as 'Cash Received'
  , old.description as 'Description'
  ,old.branch
  ,'' as  'Gain/Loss'
FROM
pawnshop_tbl as old
left join pawnshop_tbl as `new` on (`new`.pawnticketno = old.OldPT )

and my 'Interest Paid', 'Principal Paid' and 'Cash Received'
has turn into VARCHAR instead of INTEGER
so what i did is
i made a VIEW named 'loanspaidregister' out of the query above
and make a query

Code:

select
`PT No`
, Name
, `Date Loan`
, CAST( `Date Renew` AS DATE ) as 'Date Renew'
, CAST( `Date Redeem` AS DATE ) as 'Date Redeem'
, CAST( `Date Auction` AS DATE ) as 'Date Auction'
, CAST( `Date Sold` AS DATE ) as 'Date Sold'
, CAST(`100 - 500` AS unsigned integer ) as '100 - 500'
, CAST(`501 - 1999` AS unsigned integer ) as '501 - 1999'
, CAST(`2000 - UP` AS unsigned integer ) as '2000 - UP'
, CAST(`Principal Paid` AS unsigned integer ) as 'Principal Paid'
, CAST(`Interest Paid` AS unsigned integer ) as 'Interest Paid'
, CAST(`Cash Received` AS unsigned integer ) as 'Cash Received'
, Description
, Branch
from loanspaidregister

but im getting an error
'Truncated incorrect INTEGER value'

will anyone please kindly tell me why and how?

thanks in advance...

mike_bike_kite · #2 (**permalink**) 03-05-09, 04:22

Quote:

Originally Posted by homer.favenir

will anyone please kindly tell me why and how?

Why not go through your first select statement removing one line at a time, then running it to see if the error still occurs. Repeat this process until the error stops happening. You'll then know that the last line you removed contained the error. Double check this by putting the line back in and rerunning it. Then have a look at how you've defined the fields that are in this line and correct matters.

This is a useful skill to know and will allow you to fix a lot of your own problems.

homer.favenir · #3 (**permalink**) 03-05-09, 06:49

thanks for the reply
i solved it
what i did is cast it as decimal and then cast it as integer.

Code:

CAST( CAST( `Principal Paid` AS DECIMAL(8,0)) AS UNSIGNED INTEGER ) as 'Principal Paid'
, CAST( CAST( `Interest Paid` AS DECIMAL(8,0)) AS UNSIGNED INTEGER ) as 'Interest Paid'
, CAST( CAST( `Cash Received` AS DECIMAL(8,0)) AS UNSIGNED INTEGER ) as 'Cash Received'

thank

r937 · #4 (**permalink**) 03-05-09, 08:44

Quote:

Originally Posted by homer.favenir

and my 'Interest Paid', 'Principal Paid' and 'Cash Received'
has turn into VARCHAR instead of INTEGER

you did this to yourself, a self-inflicted wound

Code:

if(old.principal between 100 and 500, old.principal, '' ) as '100 - 500'

see where you assign an empty string there?

an empty string is a VARCHAR

hence the result of the IF is a VARCHAR

once you did that, you are then forced into using that CAST nonsense to get it back to a numeric value

why don't you start over and use this instead --

Code:

if(old.principal between 100 and 500, old.principal, 0 ) as '100 - 500'

or better yet, use standard SQL ----

Code:

CASE WHEN old.principal between 100 and 500 
     THEN old.principal 
     ELSE 0 END as '100 - 500'

homer.favenir · #5 (**permalink**) 03-05-09, 20:29

but the field 'Principal Paid' , 'Interest Paid' , 'Cash Received'
has no '' value.
but still it converted into VARCHAR

Code:

,format( if( old.ServiceType = 'Redeem', old.Principal,
      if(old.ServiceType = 'Renew', case when old.Principal != `new`.Principal THEN ( `new`.Principal - old.Principal )
      ELSE 0 END, 0  ) ), 2 ) as 'Principal Paid'
  , format( if(old.ServiceType = 'Renew',  ( `new`.Principal * `new`.Interest ),
         if( old.ServiceType = 'Redeem',( old.Principal * old.Interest ), 0 ) ), 2 ) 'Interest Paid'

  ,  if( old.ServiceType = 'Redeem',  old.Principal + ( old.Principal * old.Interest ),
      if( old.ServiceType = 'Renew' AND old.Principal != `new`.Principal,
         ( `new`.Principal - old.Principal ) +  ( `new`.Principal * `new`.Interest )
        , ( `new`.Principal * `new`.Interest ) ) )  as 'Cash Received'

Internet Services

Web Development

Digital Marketing

Consumer Tech