467,189 Members | 1,305 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

Select null as xyz from sysibm.sysdummy1

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

Aug 16 '07 #1
  • viewed: 6988
Share:
5 Replies
aj
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
Aug 16 '07 #2
On Thu, 16 Aug 2007 12:25:06 -0000, Rahul B <ra***********@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.
Aug 16 '07 #3
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
Aug 16 '07 #4
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

Aug 16 '07 #5
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
Aug 16 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Mike Gemmell | last post: by
3 posts views Thread by bughunter@ru | last post: by
1 post views Thread by alex.mcshane@btinternet.com | last post: by
2 posts views Thread by gimme_this_gimme_that@yahoo.com | last post: by
1 post views Thread by kropinek@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.