Home » RDBMS Server » Server Utilities » using NULLIF statement for two conditions.
using NULLIF statement for two conditions. [message #161583] Mon, 06 March 2006 02:47 Go to next message
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.
Re: using NULLIF statement for two conditions. [message #161612 is a reply to message #161583] Mon, 06 March 2006 05:21 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


Make your problem more clear. Give some example to elaborate it.
Re: using NULLIF statement for two conditions. [message #161616 is a reply to message #161612] Mon, 06 March 2006 06:01 Go to previous messageGo to next message
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.

Re: using NULLIF statement for two conditions. [message #166979 is a reply to message #161583] Mon, 10 April 2006 12:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2914
Registered: January 2010
Location: Connecticut, USA
Senior Member
MONEY_IN_ACTIVE nullif(nullif(:MONEY_IN_ACTIVE,' '),'?')

SY.
Re: using NULLIF statement for two conditions. [message #529539 is a reply to message #529508] Tue, 01 November 2011 13:06 Go to previous message
joy_division
Messages: 4949
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,' '),'?')"

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


Current Time: Tue Feb 25 02:55:03 CST 2020