OraFAQ Forum: Server Utilities » using NULLIF statement for two conditions.

Home » RDBMS Server » Server Utilities » using NULLIF statement for two conditions.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using NULLIF statement for two conditions. [message #161583]

Mon, 06 March 2006 02:47

miltony
Messages: 5
Registered: March 2006

Junior Member

Can anyone give me the way to give two conditions under NULLIF statement when we are using it in a sql script to load data into a table.

Report message to a moderator

Re: using NULLIF statement for two conditions. [message #161612 is a reply to message #161583]

Mon, 06 March 2006 05:21

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

Make your problem more clear. Give some example to elaborate it.

Report message to a moderator

Re: using NULLIF statement for two conditions. [message #161616 is a reply to message #161612]

Mon, 06 March 2006 06:01

miltony
Messages: 5
Registered: March 2006

Junior Member

Eg:-
dte_exp position(294:301) date "YYYYMMDD" NULLIF dte_exp = BLANKS,

from a data file, the values from 294 to 301 columns to be moved to a column dte_exp of a table.

The above NULLIF statement assigns null, if the columns from 294 to 301 is blanks.
But my data file have "all zeroes" also. So I want to check for both "all zeroes" and blanks and assign both of them as NULL.

Please anyone give a solution.

Report message to a moderator

Re: using NULLIF statement for two conditions. [message #166979 is a reply to message #161583]

Mon, 10 April 2006 12:06

doubleganger
Messages: 1
Registered: April 2006

Junior Member

I'm just studying Oracle and in fact wound up on this site looking for just what nullif is good for. Your question answered that. I haven't tried it but from my sybase experience i'd say nest two nullif statements like:
nullif(nullif(columnname,' '),'000000')
This should return null if columnname is either ' ' or '000000'

[Updated on: Mon, 10 April 2006 12:13]

Report message to a moderator

Re: using NULLIF statement for two conditions. [message #529505 is a reply to message #166979]

Tue, 01 November 2011 10:13

illectronic
Messages: 1
Registered: November 2011
Location: NYC

Junior Member

I also would like to know how to specify two "nullif" conditions. For example I have columns that have "?" or " " that signifies NULL data.

The post above gave me an error.
SQL*Loader-350: Syntax error at line 12.
Expecting positive integer or column name, found keyword nullif.
MONEY_IN_ACTIVE nullif(nullif(MONEY_IN_ACTIVE,' '),'? '),

Thanks

[Updated on: Tue, 01 November 2011 10:14]

Report message to a moderator

Re: using NULLIF statement for two conditions. [message #529508 is a reply to message #529505]

Tue, 01 November 2011 10:43

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

MONEY_IN_ACTIVE nullif(nullif(:MONEY_IN_ACTIVE,' '),'?')

SY.

Report message to a moderator

Re: using NULLIF statement for two conditions. [message #529539 is a reply to message #529508]

Tue, 01 November 2011 13:06

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

syakobson wrote on Tue, 01 November 2011 11:43

MONEY_IN_ACTIVE nullif(nullif(:MONEY_IN_ACTIVE,' '),'?')

SY.

If this is being used in SQL*Loader, which it looks like it is, then it would be:

MONEY_IN_ACTIVE "nullif(nullif(:MONEY_IN_ACTIVE,' '),'?')"

Report message to a moderator

Previous Topic:	import/ export
Next Topic:	Check the INSERT statements

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 17:35:31 CST 2025