Oracle select - multiple parameters one column

Question

I have a table with columns like this:

ID number
Adress varchar2
Message varchar2

Address can have email or phone

And I have a stored procedure like this:

PROCEDURE get_messages (
  i_email                   IN     varchar2,
  i_phone                   IN     varchar2
  o_messages    OUT     messages)
BEGIN

Which gets email, phone or both.

And in this procedure I want to select messages like this:

If i_email and i_phone both are nulls I want to select all messages
If i_email is not null and i_phone is null I want to select all messages sent to that email
If i_phone is not null and i_email is null I want to select all messages sent to that phone
- If i_email and i_phone both are not nulls I want to select all messages sent to i_email and i_phone

I have a difficult time to write select like like this.

  SELECT * FROM MESSAGES
  WHERE (Address = i_email OR i_email IS NULL)
         OR
        (Address = i_phone OR i_phone IS NULL)

But it works only if both values are not nulls

Pரதீப் · Accepted Answer · 2015-01-09 18:04:14Z

1

Try this.

SELECT *
FROM   MESSAGES
WHERE  ( i_email IS NULL
         AND i_phone IS NULL )
        OR ( i_email IS NOT NULL
             AND i_phone IS NULL
             AND Address = i_email )
        OR ( i_phone IS NOT NULL
             AND i_email IS NULL
             AND Address = i_phone )
        OR ( Address = i_email
             OR i_phone = Address )

edited Jan 9, 2015 at 18:04

answered Jan 9, 2015 at 17:05

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2412672 Over a year ago

Thank you it works but why did you added 1=1, I removed it and it seems that it works anyway

Pரதீப் Over a year ago

@user2412672 pass both values as NULL then it won't work without 1=1

Alex Poole Over a year ago

Not sure you need either of the 'IS NOT NULL' clauses; and the last OR clause can't ever be matches, as Address can't have two values simultaneously?

user2412672 Over a year ago

I needed to rewrite last OR to this OR ( Address = i_email OR i_phone = Address )

Collectives™ on Stack Overflow

Oracle select - multiple parameters one column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related