Filtering out a specific date

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Filtering out a specific date

Post by mjohnson62982 »

I am trying to filter out a specific expiration date. I am currently using a filter stage with a where clause:
Level_Status_Expiration_Date <> '1900-01-01'
I get the following error:
Expiration_Date_Filter: Parse error: Expected 'is' statement ('is true', 'is false') or boolean operator (and, or) got: "-01".
What would the best way to exclude records with this expiration date?
Respectfully,
Matt Johnson
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

The logic you have should work fine. Double check to make sure you don't have the word 'is' or any extra stuff like that floating around in the Filter stage or in its where clause.
Choose a job you love, and you will never have to work a day in your life. - Confucius
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

Here is the copied text of what I have in the Where Clause...

Code: Select all

Level_Status_Expiration_Date <> 1900-01-01
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So... quoted or not quoted? You've shown it both ways now. I'd also like to know if your "expiration date" field's datatype is a date or a string.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

Sorry it is not quoted, and the field's datatype is date. Thanks.
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'm not sure how picky the Filter's where clause conditions are and if it supports direct comparison to a DATE field like that. From looking at the documentation, you may be better off converting that date to a string and then filtering on the resulting string date.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Try enclosing the date constant in single quote characters.

Not sure where the "is" message is coming from. You might consider inspecting the generated OSH, or dumping the job score, to investigate this.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

Filtering Date with Quotes (Just like SQL Where Clause) is not giving any Issue: Example: date_1 <> '1900-01-01' (Filtering without Quotes thinks its a Condition and expecting operators)
Venkata Srini
Post Reply