SQL SELECT - where date is null or greater than 2

[Livia8]

Hi again,

I'm back with another basic question I'm afraid... (This forum is great for training, I must say!)

I'm trying to select a list of user names, with start date, end date, last user date, etc. The problems start when I try to add a filter by date - I'm trying to get a list of all active Oracle users on the system, i.e. those who don't have an end date or those who have a future end date. I'm also trying to exclude some usernames.

So far, this part works very well:

SELECT USER_ID, USER_NAME, START_DATE, END_DATE, LAST_LOGON_DATE, DESCRIPTION, EMAIL_ADDRESS FROM FND_USER WHERE USER_NAME NOT IN ('GUEST', 'TEST');

However, the problems start when I try to add AND END_DATE is null or end_date is >= 01/07/2022;

I've tried different combinations but none seem to work.

Could someone suggest how I can fix this, please?

Many thanks.

 

[Andrzejek]

Did you try:
[tt][BLUE]
AND (END_DATE IS NULL OR END_DATE >= TO_DATE('01/07/2022', 'MM/DD/YYYY'))[/BLUE]
[/tt]
You may want to use your date format, MM/DD/YYYY is US (crazy) date format

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

Andy's post points up that you should enclose separate clauses in parentheses if you are mixing ANDs and ORs. Not only does this explicitly force processing of the conjunctions in the way you intended, but also tells anybody maintaining your code in the future that you took the order of precedence into consideration.

In this case you could also try

... AND NVL(end_date, '31-DEC-4072') > '7-JAN-2022'

 

[Andrzejek]

I see when date is used in the format of '7-[red]JAN[/red]-2022' it is unambiguous enough for Oracle to understand it as a Date without converting a String to a Date. Great! But somewhere the date of '7-[red]01[/red]-2022' needs to be converted into '7-[red]JAN[/red]-2022' anyway.

Many people frown down upon using hard-coded values, even if it is 2000 years into the future. :-(


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

@Andy,

Thanks, that almost works, it's picking up users with no end date, but no users with end date greater than the 1st of July (in non-crazy format ).

I've tried changing the order of day and month, also MM with MMM, and YYYY with RRRR, no joy...

@Carp,

I've tried with a number of combinations (ultimately, with this)

SELECT USER_ID, USER_NAME, START_DATE, END_DATE, LAST_LOGON_DATE, DESCRIPTION, EMAIL_ADDRESS FROM FND_USER WHERE USER_NAME NOT IN ('GUEST', 'TEST');
and email_address is not null
and (end_date is null OR NVL(end_date, '31-DEC-4072') > '01-JUL-2022')
ORDER BY END_DATE DESC

but once again I only get users with no end-date only.

Thanks again.

 

[Andrzejek]

Is your END_DATE field declared as Date in your FND_USER table?

Also, [tt]
and ([red]end_date is null[/red] OR [red]NVL(end_date,[/red] '31-DEC-4072') > '01-JUL-2022')[/tt]
is redundant :-( See the documentation of NVL() Function

If you have problem excluding the Date, first try Select records with a specific date that you do have in your table, like:
[tt]SELECT *
FROM FND_USER
WHERE END_DATE = '07-MAR-2022'[/tt]
and when you have that working, then it will be easy to exclude it.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

Thanks, Andy.

This seems to have worked:


SELECT USER_ID, USER_NAME, START_DATE, END_DATE, LAST_LOGON_DATE, DESCRIPTION, EMAIL_ADDRESS FROM FND_USER WHERE USER_NAME NOT IN ('GUEST', 'TEST') and email_address is not null AND (end_date IS NULL OR END_DATE >= ('05-JUL-2022')) ORDER BY END_DATE ASC


but I can't figure out if I've put the parentheses to group together the end_date parameters properly or not...

The other funny thing is that, while the output returned the 1st of July, 2022 as 01/07/2022, the SELECT needed 07 to be converted into JUL and the / into -.

I'll try and remember, next time!

Meanwhile, thanks for your help, everyone.

 

[Andrzejek]

>if I've put the parentheses to group together the end_date parameters properly or not...

Looks good to me:[tt]

SELECT USER_ID, USER_NAME, START_DATE, END_DATE, LAST_LOGON_DATE, DESCRIPTION, EMAIL_ADDRESS
FROM FND_USER
WHERE USER_NAME NOT IN ('GUEST', 'TEST')
AND EMAIL_ADDRESS IS NOT NULL
AND [highlight #FCE94F]([/highlight]END_DATE IS NULL [blue]OR[/blue] END_DATE >= ('05-JUL-2022')[highlight #FCE94F])[/highlight]
ORDER BY END_DATE ASC[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Or, if you do not want "the parentheses to group together the end_date parameters properly", you can use carp's suggestion and do:
[tt]
SELECT USER_ID, USER_NAME, START_DATE, END_DATE, LAST_LOGON_DATE, DESCRIPTION, EMAIL_ADDRESS
FROM FND_USER
WHERE USER_NAME NOT IN ('GUEST', 'TEST')
AND EMAIL_ADDRESS IS NOT NULL
AND [highlight #8AE234]NVL(END_DATE, '[red]05-JUL-2022[/red]') >= '[red]05-JUL-2022[/red]'[/highlight]
ORDER BY END_DATE ASC
[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

As Andy correctly notes - many DO frown on hard-coding, and with good reason. However, when you are using an absurd date as a default, usually people understand and relax that rule (particularly since it's more efficient than trying to come up with a calculated date (e.g., SYSDATE + 30) that may or may not capture the values you are after.

Likewise, '1-JUL-2022' will work as long as your NLS_DATE_FORMAT is set to 'DD-MON-RRRR' or 'DD-MON-YYYY'. However, if you ever change the value of this parameter, you code will break. For this reason, a good rule to follow is to always explicitly decipher the date character string using TO_DATE.

With that said, when you are trying to illustrate a specific point, it is useful to declutter the code to make the salient point stand out.

And finally, it makes code much easier to maintain and debug if it gets broken out into a logical structue (as Andy shows above) rather than writing the query out as a single, wrapped-around line. Looking at Andy's sample, it is very easy to see how the parentheses are laid out,that they are correct, and the parentheses around the date are superfluous.