Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    42

    Unanswered: SELECT ..., NULL, ... FROM table ?

    Hi All,

    It seems it is not possible to select NULL from a table?
    For example:

    SELECT null FROM t

    produces

    SQL0206N "NULL" is not valid in the context where it is used. SQLSTATE=42703

    Can that be true? I can select any other constant but not NULL. I can workaround it
    by using the scalar function NULLIF(). Fx

    SELECT NULLIF(0,0) FROM T

    But that is very bad way of doing it and probably has performance impact.

    Is there any good explanantion for all this?

    Thanks in advance.

    Alex.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    cast(null as int)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2004
    Posts
    7

    Null

    Perhaps what you want to get is

    SELECT COL1, COL2,.... WHERE COLX IS NULL

  4. #4
    Join Date
    Nov 2003
    Posts
    42
    Quote Originally Posted by willyunger
    Perhaps what you want to get is

    SELECT COL1, COL2,.... WHERE COLX IS NULL

    I really wanted to get a constant value of NULL for one or more columns. That is useful
    for example when I use UNION. For example

    ( SELECT a.id, b.id, NULL FROM a JOIN b ON a.id = b.a_id )
    UNION ALL
    ( SELECT a.id, NULL, c.id FROM a JOIN c ON a.id = c.a_id)

    But instead of typing NULL directly I have to use CAST(NULL as INT) ...

  5. #5
    Join Date
    Jun 2006
    Posts
    11
    Why would you need a NULL value? In your example any 'default' would do. If it were an integer field for example, I'd use somthing like -1 as a default 'ID unknown'.

    Peter

  6. #6
    Join Date
    Nov 2003
    Posts
    42
    Quote Originally Posted by onemanifest
    Why would you need a NULL value? In your example any 'default' would do. If it were an integer field for example, I'd use somthing like -1 as a default 'ID unknown'.

    Peter
    This is good workaround Of course your default value has to be outside of the range of the normal values for that attribute. Also the application has to check for that default value instead of NULL . NULL is of course more natural choise, but is quite unpleasant to have to cast it to a specific type.

  7. #7
    Join Date
    Jun 2006
    Posts
    11
    Yes, you need to keep the ID range in mind. I normally use -1 for 'unknown' and -9 for 'deleted' and a valid ID range > 0 where ever possible when dealing with ID's, so I don't get confused or forget my choices.

    Another reason not to use NULL values is that some functions respond in unexpected ways when confronted with NULL values. Sometimes I'd end up converting NULLs into something else further down the line anyway.

    Peter

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    I sometimes use null as sathyaram_s suggested and sometimes 'default' value depending on sort I need. Null values are not sorted the same way as 'default' values. So don't ignore posibilities of using null in SQL.

Posting Permissions

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