The Netezza Community: Loading a Null value using NZload

Skip navigation

Netezza.comThinking.Netezza.comBookmark This Site

Up to Discussions in TwinFin Talk

13512 Views 6 Replies Latest reply: Feb 28, 2010 6:35 AM by sdennyk

ramkannan

4 posts since
Feb 19, 2010

Currently Being Moderated

Feb 25, 2010 4:04 AM

Loading a Null value using NZload

Hi,

Input file:

aaa| | |
bbb|mm||
ccc| |nnn

table

col1 col2 col3

I used nzload to load the data into this table datas are getting loaded.

After loading i am able to see the data.

But when i try to execute .. select * from table where col2 is NULL; Nothing is returned.

When i try to execute select * from table where col2 =' '; I am getting the results,

How can load the blank spaces from the flat file as NULL.

Please advice.

Cheers

Ramkannan.A

Like (0)

David Shuttleworth
270 posts since
May 14, 2008

Currently Being Moderated

1. Feb 25, 2010 4:57 AM (in response to ramkannan)
Re: Loading a Null value using NZload

nzload has a switch -nullvalue which specifies which value to treat as nulls - but I'd be careful if using spaces as nulls... the -nullvalue switch will affect ALL columns on the load and you may not want this. Probably better to load 'as-is' to a staging table 9which is good practice anyway) and then do any conversions you need from there explicitly

D.

Report Abuse

Like (0)
ramkannan
4 posts since
Feb 19, 2010

Currently Being Moderated

2. Feb 25, 2010 5:09 AM (in response to David Shuttleworth)
Re: Loading a Null value using NZload

Thanks for the reply.

what do you mean by 'as-is' .

Report Abuse

Like (0)
Superuser
90 posts since
Sep 19, 2008

Currently Being Moderated

3. Feb 25, 2010 5:17 AM (in response to ramkannan)
Re: Loading a Null value using NZload

Load the values from the flat file as it is into a stage table and then modify the values

Report Abuse

Like (0)
ramkannan
4 posts since
Feb 19, 2010

Currently Being Moderated

4. Feb 25, 2010 5:24 AM (in response to David Shuttleworth)
Re: Loading a Null value using NZload

All,

I got the answer. No need to go for as:is.

We can use the -nullvalue " it worked for me.

Cheers
Ramkannan.A

Report Abuse

Like (0)
Vijay
9 posts since
Jan 5, 2009

Currently Being Moderated

5. Feb 25, 2010 8:15 AM (in response to ramkannan)
Re: Loading a Null value using NZload

Can you please explain a bit more about -nullvalue swithc

Report Abuse

Like (0)
sdennyk
18 posts since
Nov 22, 2009

Currently Being Moderated

6. Feb 28, 2010 6:35 AM (in response to Vijay)
Re: Loading a Null value using NZload

Hi Vijay,

Please find below some details on the nullvalue switch.

The NullValue switch specifies the string to use for the null value, with a maximum 4-byte UTF-8 string. The default is ‘NULL’. You can specify a value such as a space (' ') or any string up to four characters.
Conceptually a field contains either a value or an indication that there is no value.The system provides some flexibility in how you indicate that a field contains no value through the nullvalue switch.

i,e, if you file contains the text 'NULL' to represent null value. you can use the -nullvalue 'NULL' in your nzload command to properly load the NULL value to your netezza table.

I hope this clarifies.

Thanks
Sebastian

Report Abuse

Like (0)

Go to original post

Legend

Correct Answers - 4 points
Helpful Answers - 2 points

Feb 25, 2010 4:04 AM

Loading a Null value using NZload

Actions

More Like This

Bookmarked By (0)

Legend