Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    74

    Unanswered: How can I select NULL values when I create view?

    Hello everyone!
    I want to create a view like this:

    create view a(f1, f2, f3, ...) as
    (select a.f1, b.f2, NULL, ...
    from table1 a, table2 b, ...)

    But DB2 raised the error:
    SQL0206N "NULL" is not valid in the context where it is used. SQLSTATE=42703

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    null

    why do you want to select a null value from a table
    select '' from table --- would also work
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    try:

    create view a(f1, f2, f3, ...) as
    (select a.f1, b.f2, NULLIF(1,1), ...
    from table1 a, table2 b, ...)

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by zhouhaiming
    (select a.f1, b.f2, NULL, ...
    from table1 a, table2 b, ...)
    The only reason that you cannot just SELECT NULL is that NULL has no datatype and every column must have a datatype.

    NULLIF(1,1) returns NULL of datatype INT (since 1 is an INT constant);
    likely, NULLIF('','') returns NULL of datatype VARCHAR.

    For a more generic way of having NULL of a certain datatype (like e.g. DATE) you will have to use CAST:
    Code:
    SELECT ..., Cast(NULL as DATE), ...
    FROM table1 ...
    (or replace "DATE" by any datatype you want).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jan 2003
    Posts
    74
    Yes, Peter.Vanroose is right! I just test, it's ok! Thank you for your help!

Posting Permissions

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