#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Angry Error Code: 1292. Truncated incorrect INTEGER value: '201303100000068716450-001'


    Hi,

    I'm having trouble with a simple MySQL query. I'm doing a count case :
    create table newtable as
    select
    count(case when field1='value1' and field2='value2' then field3 else null end) as result1,
    count(case when field1='value1' and field2='value3' then field3 else null end) as result2
    from
    table;

    and I keep getting "Error Code: 1292. Truncated incorrect INTEGER value: '201303100000068716450-001'". The field that I'm counting (field3) is a varchar.

    If I run the query without creating a table from the results, I don't get an error. If I create a view, I don't get an error. Its only when I create a table.

    Please help me figure this out !!!!

    Thanks,

    Suwbacca
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,024
    Rep Power
    4210
    Originally Posted by suwbacca
    Its only when I create a table.
    you should explicitly provide the column definitions as part of the CREATE SELECT statement

    for example...
    Code:
    CREATE TABLE newtable 
    ( result1 INTEGER NOT NULL
    , result2 INTEGER NOT NULL
    )
    SELECT COUNT(CASE WHEN field1='value1' 
                       AND field2='value2' 
                      THEN field3 ELSE NULL END) AS result1
         , COUNT(CASE WHEN field1='value1' 
                       AND field2='value3' 
                      THEN field3 ELSE NULL END) AS result2
     FROM table
    note i don't think the AS keyword is allowed
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Unhappy didn't work :(


    Hi,

    Thank you very much but it still doesn't work. I get the same error message. Interesting fact: if I do a count distinct instead of a count, it works.



    Any other option ? Thanks in advance .

    Suwbacca
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,024
    Rep Power
    4210
    Originally Posted by suwbacca
    Any other option ?
    instead of counting field3, count the number 0
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Thumbs up


    It works, I can the same results and I can output to a table.

    Thank you so much !

IMN logo majestic logo threadwatch logo seochat tools logo