By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

Select null as xyz from sysibm.sysdummy1

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.