Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: need help. need to check a value inside a sql statement

    What i need to do is check a field inside a sql statement to see if it is null and if it is then change the value to a 0.

    for example

    select Field1,field2,field3(if null then =0)
    from table1

    does this make sense. I think in oracle PL/SQL is uses decode(field3, null... or something like that

    any help would be appreciated

    thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can use either the coalesce function which takes the first non-null value in a list, or the isnull function. I understand coalesce is more ansi-sql friendly, and I believe is implemented in Oracle as well.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Example:

    select Field1, field2, isnull(field3, 0) from table1

    or

    select Field1, field2, coalesce(field3, 0) from table1

    blindman

  4. #4
    Join Date
    Sep 2003
    Posts
    5

    additional help

    I am trying to put this in a MS access query

    My statement look like this

    Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
    from Table, Table
    where.....

    I get an error message saying

    " wrong number of arguments used with function in query expression
    ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"

    thanks

  5. #5
    Join Date
    Sep 2003
    Posts
    5

    additional help

    I am trying to put this in a MS access query

    My statement look like this

    Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
    from Table, Table
    where.....

    I get an error message saying

    " wrong number of arguments used with function in query expression
    ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"

    thanks

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    MS Access does not use the same exact same function list as SQL Server. The ISNULL function in MS Access takes a single parameter and returns a boolean value indicating whether the parameter was null.

    The MSAccess function you are looking for is "NZ", short for Null to Zero.

    Select Table_Name.Field1, Table_name.Field2, NZ(Table_Name.Field3,0),Table_Name.field4
    from Table, Table

    MS Access SQL is hybridized with VB.

    blindman

Posting Permissions

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