[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Oracle string comparison question

I have two variables in the procedure code:

f1 varchar2(60);
f2 number;

I wanted to write something under IF when NOT both of f1 and f2 are not NULL:

IF  ( !( (f1 = NULL) AND (f2 = NULL) ) AND (to_number(f1) != f2) ) THEN
.....//some stuff
END IF;


I get syntax error ... not sure where I am writing incorrectly..
0
toooki
Asked:
toooki
  • 2
  • 2
1 Solution
 
johanntagleCommented:
It should just be

IF f1 is not null and f2 is not null THEN
 ....
END IF;
0
 
toookiAuthor Commented:
Thank you...
But sorry I think I stated correctly...

Can I compare f1 and f2 that are of varchar2 and number respectively?
I go into the IF loop if f1 (converted to number) and f2 are different AND (if both are not null).

So cases:
f1        f2
-----------
10       5   //goes inside IF
4              //goes inside IF
                //does not go inside IF
           6   //goes inside IF  
8         8   //does not go inside IF

Thanks!!
0
 
johanntagleCommented:
You can either put all those case in separate OR clauses within the IF statement like

IF (f1 is not null and f2 is not null and to_number(f1)!=f2) OR (f1 is not null and f2 is null) OR (f1 is null and f2 is not null) ....


Or, if there's are a value that surely f1 and f2 will never have, you can do:

IF to_number(nvl(f1,99999999))!=nvl(f2,99999999) THEN
  ....
END IF;
0
 
toookiAuthor Commented:
Thanks a lot.
IF to_number(nvl(f1,99999999))!=nvl(f2,99999999) THEN
  ....
END IF;

This worked for me perfectly..
Thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now