none
SQL CAST FUNCTION QUERY

    Question

  • Hi, the following query works:

    select TOP 100 min (add2) as minNum, max(add2) as maxNum, add3, add4

    from database

    where isnumeric(add2) = true group by add3, add4

     

    However the above query brings back that 10 < 9.

    I am trying to use the CAST function to ensure add2 in an INT to get the result 9 < 10.

    I have tried the following but errors occur.

     

    select TOP 100 min (CAST(add2, INT)) as minNum, max (CAST(add2,INT)) as maxNum, add3, add4

    from database

    where isnumeric(add2) = true group by add3, add4

     

    Any help would be appreciated, Cheers!

    Sunday, May 09, 2010 11:00 AM

Answers

  • This forum is for TSQL and if you need answers for MS access, pl post it to appropriate forum.
    • Marked as answer by KJian_ Friday, May 14, 2010 8:08 AM
    Tuesday, May 11, 2010 10:00 AM
  • Access SQL is not the same as T-SQL. You will get a more appropriate answer asking in the Access topic.
    George
    • Marked as answer by KJian_ Friday, May 14, 2010 8:08 AM
    Tuesday, May 11, 2010 11:57 AM
  • As many have answered, the syntax of Access queries and SQL Server queries can differ.

    Anyway, for now - would it work if you remove Top 100 or if in my code you remove () around 100 ? I added them as they required in latest versions of T-SQL.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, May 14, 2010 8:08 AM
    Tuesday, May 11, 2010 12:02 PM

All replies

  • Hello,

    You use a wrong syntax: CAST(value AS type) or CONVERT(type, value)

    SELECT CAST('10' AS int)

    SELECT CONVERT(int, '10')

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Sunday, May 09, 2010 11:15 AM
  • Hi! Afraid it is still not working!!!  :(

    Monday, May 10, 2010 2:59 PM
  • Hi! Afraid it is still not working!!!  :(


    Can you post the statement and the error?

     

    AMB

    Monday, May 10, 2010 3:01 PM
  • Try

    select top (100) min(case when IsNumeric(Add2) = 1 then cast(Add2 as decimal(20,3)) end) as MinNum, max(case when IsNumeric(Add2) = 1 then cast(Add2 as decimal(20,3)) end) as MaxNum, Add3, Add4 from Database where IsNumeric(Add2) = 1 group by Add3, Add4


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, May 10, 2010 3:12 PM
  • STATEMENT:select TOP 100 min CAST(add2 AS int) as minNum, max CAST(add2 AS int) as maxNum, add3, add4
    FROM database
    where isnumeric(add2) = true group by add3, add4

    ERROR:

    Error in SELECT clause: expression near 'CAST'

    Missing FROM clause

    Unable to parse query text.

     

    I've also tried adding quote to 'add2' with same error returning.

    Tuesday, May 11, 2010 8:41 AM
  • Hi,

    ERROR:

    Error in top expression.

     

    Is a stickler!!!!

    Tuesday, May 11, 2010 8:43 AM
  • Please try below. This should work

     

    select TOP 100 min (add2) as minNum, max(add2) as maxNum, add3, add4 
    from (	SELECT CAST(add2 AS INT) add2, add3, add4 
    	FROM database 
    	WHERE isnumeric(add2) = 1 ) AS t
    group by add3, add4
    
    Tuesday, May 11, 2010 9:11 AM
  • Thanks for trying!!! :)

     

    ERROR:

    Error in list of function arguments: 'AS' not recognized.

    Tuesday, May 11, 2010 9:24 AM
  • Which version of SQL server do you use? What is your table name?
    Tuesday, May 11, 2010 9:41 AM
  • Hey,

    I am creating the sql statement in ms visual studio 2008.

    Table name is dbo_2007_Table & is created in ms access 2007.

    More Info: Field add2 is defined as a text field in access which i cant change as it contains both text fields & numbers fields hence why i am trying to extract the fields with numbers only!

    Tuesday, May 11, 2010 9:56 AM
  • This forum is for TSQL and if you need answers for MS access, pl post it to appropriate forum.
    • Marked as answer by KJian_ Friday, May 14, 2010 8:08 AM
    Tuesday, May 11, 2010 10:00 AM
  • Hi, Its the SQL statement that is the problem though!

    Tuesday, May 11, 2010 10:23 AM
  • Access SQL is not the same as T-SQL. You will get a more appropriate answer asking in the Access topic.
    George
    • Marked as answer by KJian_ Friday, May 14, 2010 8:08 AM
    Tuesday, May 11, 2010 11:57 AM
  • As many have answered, the syntax of Access queries and SQL Server queries can differ.

    Anyway, for now - would it work if you remove Top 100 or if in my code you remove () around 100 ? I added them as they required in latest versions of T-SQL.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, May 14, 2010 8:08 AM
    Tuesday, May 11, 2010 12:02 PM