Select null as xyz from sysibm.sysdummy1

[Rahul B]

Hi,

Why is it that this command doesn't work in DB2.
I have to create a view in which one of the columns has to be
null(Don't ask me why that should always be null, because the view in
Oracle and DB2 should have same columns and Oracle view has col as
null)
So i try ...
Create view......null as col.......

It fails in DB2, but works in Oracle.

Please tell what should be done to set the field as null.

Thanks a lot

Rahul

[aj]

Re: Select null as xyz from sysibm.sysdummy 1

Try something along the lines of:

CAST(NULL as CHAR)
CAST(NULL AS DATE)
CAST(NULL as INTEGER)

hth

aj

Rahul B wrote:

Hi,
>
Why is it that this command doesn't work in DB2.
I have to create a view in which one of the columns has to be
null(Don't ask me why that should always be null, because the view in
Oracle and DB2 should have same columns and Oracle view has col as
null)
So i try ...
Create view......null as col.......
>
It fails in DB2, but works in Oracle.
>
Please tell what should be done to set the field as null.
>
Thanks a lot
>
Rahul
>

[Brian Tkatch]

Re: Select null as xyz from sysibm.sysdummy 1

On Thu, 16 Aug 2007 12:25:06 -0000, Rahul B <rahul.babbar1@ gmail.com>
wrote:

>Hi,
>
>Why is it that this command doesn't work in DB2.
>I have to create a view in which one of the columns has to be
>null(Don't ask me why that should always be null, because the view in
>Oracle and DB2 should have same columns and Oracle view has col as
>null)
>So i try ...
>Create view......null as col.......
>
>It fails in DB2, but works in Oracle.
>
>Please tell what should be done to set the field as null.
>
>Thanks a lot
>
>Rahul

CAST the NULL. Or, being it has to match Oracle, and Oracle uses a
zero-length string for NULL, just use ''.

B.

[Serge Rielau]

Re: Select null as xyz from sysibm.sysdummy 1

Rahul B wrote:

It fails in DB2, but works in Oracle.

What's the type of such a NULL in Oracle?
If I do:
CREATE TABLE T(c1) AS (SELECT NULL FROM DUAL)

What is c1?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Rahul B]

Re: Select null as xyz from sysibm.sysdummy 1

On Aug 16, 7:04 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Rahul B wrote:

It fails in DB2, but works in Oracle.

>
What's the type of such a NULL in Oracle?
If I do:
CREATE TABLE T(c1) AS (SELECT NULL FROM DUAL)
>
What is c1?
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi,

It seems we cannot create a table in Oracle with null as a column but
only a view or a simple select statement like
select null as xyz from dual;

It gives the error saying zero length columns are not allowed.

Rahul

[Serge Rielau]

Re: Select null as xyz from sysibm.sysdummy 1

Rahul B wrote:

It gives the error saying zero length columns are not allowed.

Seems like they default to a CHAR(0)....

Anyway, to make a long story short DB2's unwillingness to type NULL more
aggressively is rooted in the strong typing.
NULls are allowed for:
1. Input to CAST (obviously)
2. Input to stored procs (because there is no overloading by type)
3. Right hand side of SET clause (since the column/variable type is known)
4. INSERT VALUES clause (again casting to the column)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab