simple if statemnet

[mystuff]

I have a statement

Code: Select all

if (new_date <= extract_date) And (other logic)
then 

statement A

else 

statement B

new_date being null,
extract_date being todays date,
other logic resulting in 1

Statement A is getting executed

I checked

Code: Select all

new_date <= extract_date

is also resulting in NULL, then why is the code going into StatementA

Shouldnt it go to StatmentB (Null And 1, should result in false isnt it?)

[DSguru2B]

You sure its null and not just a space or nothing? Is your source a flat file?

[mystuff]

source is a flat file, to avoid the confusion I deliberately added @NULL to the source field

Code: Select all

if trim(inputfield,'B') or isnull(inputfield) then
@NULL 
else
trim(inputfield,'B')

If its a space how to I deal with this?

[DSguru2B]

Are'nt you suppose to specify the string to trim along with B option. Also you are not comparing it with anything.
Use the following

Code: Select all

if trim(inputfield," ","B")="" or isnull(inputfield) then 
@NULL 
else 
trim(inputfield," ","B")

[mystuff]

sorry posted it in hurry, I have the code like this, Do I need to put double quotes?

Code: Select all

if Trim(inputfield, ' ', 'B')='' or isnull(inputfield) then 
@NULL 
else 
Trim(inputfield, ' ', 'B')

[DSguru2B]

No, the double/single quotes would not matter.
Then its really your second condition that might not be coming true.
Try the following

Code: Select all

if (new_date <= extract_date) And (@TRUE) 
then 

statement A 

else 

statement B

[ray.wurlod]

NULL and 1 can only return NULL - such is the nature of NULL

Try putting the IsNull() test first.

Trim(" ") can never be "". If there are spaces, one space is always left. Prefer the Len() function.

Is this an expression, or code in a routine? If it's an expression it must be all on one line.

Code: Select all

If IsNull(inputfield) Then @NULL Else If Len(inputfield) = 0 Then @NULL Else Trim(inputfield)

[rogue]

I'm often facing similar problems. Trim can't handle NULL, concatenating NULL with an empty string does not result in an implicite type cast, etc...

For that I wrote an user defined routine which can all the things I need so often.

It returnes '' when given NULL and performs a TRIM and removes CRLFs.

So I only need one call for checking values.

In addition to this I use this function to unify values for loading lookup tables.

Code: Select all

  If IsNull(Value) = @True 
    Then 
      lString = ""
    End
    Else 
      lString = UpCase(Value)
  End

  lString = Ereplace(lString," ","",0,1)
  lString = Ereplace(lString,Char(10),"",0,1)
  lString = Ereplace(lString,Char(13),"",0,1)

  Ans = lString

[ray.wurlod]

That could be done with a single expression; you could store it as a reusable component (as a Transform).

Code: Select all

If IsNull(Arg1) Then "" Else Upcase(Convert(" ":Char(10):Char(13),"",Arg1))