Date selection criteria in Unidata stage
Moderators: chulett, rschirm, roy
Date selection criteria in Unidata stage
I have two date fields in a Unidata file that I would like to use as criteria in order to limit the number of records I am returning, but I have not been able to figure out the syntax to use for this, instead of getting the limited number of records I get everything back. The syntax I was trying to use was OIC_RECEIVED_DATE >= @DATE -1 OR WITH OIC_SYSTEM_DATE >= @DATE -3
When this didn't work I tried
OIC_RECEIVED_DATE >= OCONV(@DATE -1, "D") OR WITH OIC_SYSTEM_DATE >= OCONV(@DATE -3, "D")
and this didn't work either. Any suggestions? I need to put this in the criteria so that I can limit the number of records, otherwise I am dealing with 10 million records and the job takes 12 hours to extract this.
When this didn't work I tried
OIC_RECEIVED_DATE >= OCONV(@DATE -1, "D") OR WITH OIC_SYSTEM_DATE >= OCONV(@DATE -3, "D")
and this didn't work either. Any suggestions? I need to put this in the criteria so that I can limit the number of records, otherwise I am dealing with 10 million records and the job takes 12 hours to extract this.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Avoid the second WITH. You'll get better query performance, particularly if the two date columns are indexed (which, ideally, they should be).
Code: Select all
WITH OIC_RECEIVED_DATE >= EVAL "OCONV(@DATE-1, 'D') OR OIC_SYSTEM_DATE >= EVAL "OCONV(@DATE-3, 'D')"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ok, I have more information now. With the above code, I seem to be getting data that is scattered through out the dates in the table. When I hard code it to look like
it works fine. Is there some setting that controls what comes out of ICONV for dates? i.e. Maybe I have a local setting so that the date is in the wrong format for our system to use?
Code: Select all
OIC_RECEIVED_DATE >= "02/01/2004" OR OIC_SYSTEM_DATE >= "02/01/2004"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes, there is. YOU control it, with the specification in OCONV. For example:
Code: Select all
WITH OIC_RECEIVED_DATE >= EVAL "OCONV(@DATE-1, 'D/MDY[2,2,4]') OR OIC_SYSTEM_DATE >= EVAL "OCONV(@DATE-3, 'D/MDY[2,2,4]')"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes it can, depending on what columns (fields, in UniData parlance) you're extracting. If you want to limit the extraction to only those values that meet your criteria, you have the choice of a WHEN clause (meaning that you're extracting the multi-valued fields as multi-valued) or a BY.EXP clause (meaning that you're generating one row per value but within that row there is only one value).wdudek wrote:This looked like it was working, but I have recently found out that not all of the data is being pulled over. The Unidata field is multi valued, does this affect the selection statement?
A WHEN clause mimics a WITH clause exactly. The WHEN clause has the effect of suppressing display of values that do not meet the criteria.
Code: Select all
WITH OIC_RECEIVED_DATE >= EVAL "OCONV(@DATE-1, 'D/MDY[2,2,4]') OR OIC_SYSTEM_DATE >= EVAL "OCONV(@DATE-3, 'D/MDY[2,2,4]')"
WHEN OIC_RECEIVED_DATE >= EVAL "OCONV(@DATE-1, 'D/MDY[2,2,4]') OR OIC_SYSTEM_DATE >= EVAL "OCONV(@DATE-3, 'D/MDY[2,2,4]')"
Code: Select all
WITH OIC_RECEIVED_DATE >= EVAL "OCONV(@DATE-1, 'D/MDY[2,2,4]') OR OIC_SYSTEM_DATE >= EVAL "OCONV(@DATE-3, 'D/MDY[2,2,4]')" BY.EXP OIC_RECEIVED_DATE
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.