Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: Truncated incorrect INTEGER value

    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...
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    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
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    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'
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •