Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: SQL Statement based on Data Type

    Hi there,

    I have a situation in which I need to do one thing if the values of a field ("ServiceCode") fall within the range of 100-299, and do something else if the value is outside this range. The problem is that ServiceCode field has a datatype of char(6), and there are some non-numeric values in that field.

    So the logic needs to be:

    If ServiceCode is a number datatype (int or decimal) AND ServiceCode is between 100 and 299, then <Do Something>
    Else NULL


    Additionally, this statement needs to be within a larger Select statement.

    I.E.

    SELECT
    FIELD1,
    FIELD2,
    <LOGIC FROM ABOVE>,
    FIELD4
    FROM TABLE_X
    WHERE <BLAH>


    Any suggestions?



    Thanks,
    NWChowd

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    I do not understand what u mean by "then <Do Something>" and how u want to do it. However if u want a set of values if the logic passes then something like below can be used

    declare @ServiceCode varchar(10)
    set @ServiceCode='442'
    select
    case when isnumeric(@ServiceCode)=1 then
    (case when (cast(@ServiceCode as decimal(8,3)) > 100) then 'number and greater than 100' else 'not of my interest' end)
    else 'not of my interest' end
    Last edited by upalsen; 11-23-05 at 01:16.

  3. #3
    Join Date
    Nov 2005
    Posts
    2
    My guess may be correct if I say that by <Do Something> would mean error message if required value is not inputted. While inputting values in the ServiceCode field, first of all the values should be numeric and has no effect whether it is a char datatype.

    You can proceed by the following SQL statement:

    SELECT IF(ASCII(servicecode)=servicecode AND ASCII(servicecode)=>100 AND
    ASCII(servicecode)<=299,servicecode,"Out of range") FROM table_name

    I've tried my best to give you the solution. Try this one.

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    anitad25,

    firstly, ACSII() function returns the ascii value of the left most character the remaining characters are simply ignored.

    secondly, you cannot use IF in select the way you have.

  5. #5
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    If 11a not in range


    select
    case when isnumeric(serv_cd) = 1 and cast (serv_cd as decimal) between 100 and 299 then
    'Hello'
    else SERV_CD end
    from table1

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Serv_CD varchar(6))
    GO
    
    INSERT INTO myTable99(Serv_CD)
    SELECT '11a' UNION ALL SELECT '150' UNION ALL SELECT '999'
    GO
    
    SELECT CASE WHEN CONVERT(int, Serv_CD) BETWEEN 100 AND 299 THEN Serv_CD ELSE null END AS Serv_CD
      FROM myTable99
     WHERE ISNUMERIC(Serv_CD) = 1
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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