Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: convert varchar to integer

    hi,
    how can i convert a varchar into integer using query?
    Code:
    cast(format((
      (coalesce(q.ManHrs,0) -
        coalesce(q.OT,0)) -
          (coalesce(q.RegularBreak,0) +
            coalesce(q.RegularAdjustment,0))
          ),4) as binary) as 'Total Reg Hrs',
    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be:
    Code:
      Cast(
       Coalesce(q.ManHrs, 0)
    -  Coalesce(q.OT, 0)
    -  Coalesce(q.RegularBreak, 0)
    -  Coalesce(q.RegularAdjustment, 0)
       AS INT) AS 'Total Reg Hrs'
    ,
    -PatP

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    how about the format?
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How much sense does format() make for an integer?

    -PatP

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    ok, i can remove the format, but
    Code:
    Cast(
       Coalesce(q.ManHrs, 0)
    -  Coalesce(q.OT, 0)
    -  Coalesce(q.RegularBreak, 0)
    -  Coalesce(q.RegularAdjustment, 0)
       AS INT) AS 'Total Reg Hrs'
    doesnt work.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Really? What error message does it give?

    -PatP

  7. #7
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) AS 'Total Reg Hrs'
    from keyentry_indi_table as q

    Group By
    q.`Date`,
    q' at line 36
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please post the entire SQL statement that you're using. The code snippet that I wrote looks good to me "as is", but it may conflict with other parts of your SQL statement. The only way I can debug the code in context is to see it in context... I need to see the whole tamale in order to be able to form an opinion of that tamale!

    -PatP

  9. #9
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Code:
    select
    q.`date` as 'Date',
    q.last_name as 'Last Name',
    q.tl as 'Team Leader',
    q.batch as 'Total Batches',
    q.recordskeyed as 'Total Keyed',
    q.RegularBreak as 'Total Reg Break',
    coalesce(q.RegularAdjustment,0) as 'Total Reg Adj',
    format((
      (coalesce(q.ManHrs,0) -
        coalesce(q.OT,0)) -
          (coalesce(q.RegularBreak,0) +
            coalesce(q.RegularAdjustment,0))
    ),4) as 'Total Reg Hrs',
    q.OTBreak as 'Total OT Break',
    q.OTAdjustment as 'Total OT Adj',
    format(
    (coalesce(q.OT,0) -
      coalesce(q.OTBreak,0) -
        coalesce(q.OTAdjustment,0))
            ,4)  as 'Total OT Hrs',
    q.ManHrs as 'Total Man Hrs',
    format(
          coalesce(q.recordskeyed,0) /
            (coalesce(q.ManHrs,0) -
              coalesce(q.RegularBreak,0) -
                coalesce(q.RegularAdjustment,0) -
                  coalesce(q.OTBreak,0) -
                    coalesce(q.OTAdjustment))
            ,4) as 'Keyed/Hr',
    Cast(
       Coalesce(q.ManHrs, 0)
    -  Coalesce(q.OT, 0)
    -  Coalesce(q.RegularBreak, 0)
    -  Coalesce(q.RegularAdjustment, 0)
       AS INT) AS 'Total Reg Hrs'
    
    from keyentry_indi_table as q
    
    Group By
    q.`Date`,
    q.Last_Name,
    q.tl
    
    Order By
    q.`Date` DESC,
    q.tl,
    q.Last_Name
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the giggle factor, give this a shot:
    Code:
    SELECT
       q.`date` as 'Date'
    ,  q.last_name as 'Last Name'
    ,  q.tl as 'Team Leader'
    ,  q.batch as 'Total Batches'
    ,  q.recordskeyed as 'Total Keyed'
    ,  q.RegularBreak as 'Total Reg Break'
    ,  Coalesce(q.RegularAdjustment, 0) as 'Total Reg Adj'
    ,  Format(
          (
             (
                Coalesce(q.ManHrs, 0) 
    -           Coalesce(q.OT, 0)
             ) 
    -        (
                Coalesce(q.RegularBreak, 0) 
    +           Coalesce(q.RegularAdjustment, 0)
             )
          )
    ,     4) as 'Total Reg Hrs'
    ,  q.OTBreak as 'Total OT Break'
    ,  q.OTAdjustment as 'Total OT Adj'
    ,  Format(
          (
             Coalesce(q.OT, 0) 
    -        Coalesce(q.OTBreak, 0) 
    -        Coalesce(q.OTAdjustment, 0)
          )
    ,     4)  as 'Total OT Hrs'
    ,  q.ManHrs as 'Total Man Hrs'
    ,  Format(
          Coalesce(q.recordskeyed, 0)
    /     (
             Coalesce(q.ManHrs, 0) 
    -        Coalesce(q.RegularBreak, 0) 
    -        Coalesce(q.RegularAdjustment, 0) 
    -        Coalesce(q.OTBreak, 0) 
    -        Coalesce(q.OTAdjustment, 0)
          )
    ,     4) as 'Keyed/Hr'
    ,  Cast(
          Coalesce(q.ManHrs, 0)
    -     Coalesce(q.OT, 0)
    -     Coalesce(q.RegularBreak, 0)
    -     Coalesce(q.RegularAdjustment, 0)
       AS INT) AS 'Total Reg Hrs'
       FROM keyentry_indi_table AS q
       GROUP BY
       q.`Date`
    ,  q.Last_Name
    ,  q.tl
       ORDER BY
       q.`Date` DESC
    ,  q.tl
    ,  q.Last_Name
    -PatP

  11. #11
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    still error
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) AS 'Total Reg Hrs'
       FROM keyentry_indi_table AS q
       GROUP BY
       q.`Da' at line 46
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, let's try to remove the questionable expression and see what that does. Can you run:
    Code:
    SELECT
       q.`date` as 'Date'
    ,  q.last_name as 'Last Name'
    ,  q.tl as 'Team Leader'
    ,  q.batch as 'Total Batches'
    ,  q.recordskeyed as 'Total Keyed'
    ,  q.RegularBreak as 'Total Reg Break'
    ,  Coalesce(q.RegularAdjustment, 0) as 'Total Reg Adj'
    ,  Format(
          (
             (
                Coalesce(q.ManHrs, 0) 
    -           Coalesce(q.OT, 0)
             ) 
    -        (
                Coalesce(q.RegularBreak, 0) 
    +           Coalesce(q.RegularAdjustment, 0)
             )
          )
    ,     4) as 'Total Reg Hrs'
    ,  q.OTBreak as 'Total OT Break'
    ,  q.OTAdjustment as 'Total OT Adj'
    ,  Format(
          (
             Coalesce(q.OT, 0) 
    -        Coalesce(q.OTBreak, 0) 
    -        Coalesce(q.OTAdjustment, 0)
          )
    ,     4)  as 'Total OT Hrs'
    ,  q.ManHrs as 'Total Man Hrs'
    ,  Format(
          Coalesce(q.recordskeyed, 0)
    /     (
             Coalesce(q.ManHrs, 0) 
    -        Coalesce(q.RegularBreak, 0) 
    -        Coalesce(q.RegularAdjustment, 0) 
    -        Coalesce(q.OTBreak, 0) 
    -        Coalesce(q.OTAdjustment, 0)
          )
    ,     4) as 'Keyed/Hr'
    ,  0 AS 'Total Reg Hrs'
       FROM keyentry_indi_table AS q
       GROUP BY
       q.`Date`
    ,  q.Last_Name
    ,  q.tl
       ORDER BY
       q.`Date` DESC
    ,  q.tl
    ,  q.Last_Name
    -PatP

  13. #13
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    ok it now works with that script
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh great bundles of radioactive bat guano! How did I miss that?!?!? Uff-da, use:
    Code:
    SELECT
       q.`date` as 'Date'
    ,  q.last_name as 'Last Name'
    ,  q.tl as 'Team Leader'
    ,  q.batch as 'Total Batches'
    ,  q.recordskeyed as 'Total Keyed'
    ,  q.RegularBreak as 'Total Reg Break'
    ,  Coalesce(q.RegularAdjustment, 0) as 'Total Reg Adj'
    ,  Format(
          (
             (
                Coalesce(q.ManHrs, 0) 
    -           Coalesce(q.OT, 0)
             ) 
    -        (
                Coalesce(q.RegularBreak, 0) 
    +           Coalesce(q.RegularAdjustment, 0)
             )
          )
    ,     4) as 'Total Reg Hrs'
    ,  q.OTBreak as 'Total OT Break'
    ,  q.OTAdjustment as 'Total OT Adj'
    ,  Format(
          (
             Coalesce(q.OT, 0) 
    -        Coalesce(q.OTBreak, 0) 
    -        Coalesce(q.OTAdjustment, 0)
          )
    ,     4)  as 'Total OT Hrs'
    ,  q.ManHrs as 'Total Man Hrs'
    ,  Format(
          Coalesce(q.recordskeyed, 0)
    /     (
             Coalesce(q.ManHrs, 0) 
    -        Coalesce(q.RegularBreak, 0) 
    -        Coalesce(q.RegularAdjustment, 0) 
    -        Coalesce(q.OTBreak, 0) 
    -        Coalesce(q.OTAdjustment, 0)
          )
    ,     4) as 'Keyed/Hr'
    ,  Cast(
          Coalesce(q.ManHrs, 0)
    -     Coalesce(q.OT, 0)
    -     Coalesce(q.RegularBreak, 0)
    -     Coalesce(q.RegularAdjustment, 0)
       AS INTEGER) AS 'Total Reg Hrs'
       FROM keyentry_indi_table AS q
       GROUP BY
       q.`Date`
    ,  q.Last_Name
    ,  q.tl
       ORDER BY
       q.`Date` DESC
    ,  q.tl
    ,  q.Last_Name
    -PatP

  15. #15
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    no luck, but
    Code:
    as SIGNED INTEGER
    works!
    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
  •