Page 1 of 1
Date selection criteria in Unidata stage
Posted: Thu Feb 05, 2004 4:11 pm
by wdudek
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.
Posted: Thu Feb 05, 2004 4:43 pm
by wdudek
I hard coded the data and it worked, i.e. OIC_SYSTEM_DATE >= "02/04/04" and this worked, but I need this to be dynamic.
Posted: Thu Feb 05, 2004 6:25 pm
by kduke
Do a search on EVAL. Unidata is similar to Universe. It needs a valid dictionary item or EVAL.
Posted: Thu Feb 05, 2004 6:34 pm
by ray.wurlod
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')"
Posted: Wed Feb 11, 2004 10:18 am
by wdudek
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?
Posted: Wed Feb 11, 2004 11:53 am
by wdudek
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
Code: Select all
OIC_RECEIVED_DATE >= "02/01/2004" OR OIC_SYSTEM_DATE >= "02/01/2004"
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?
Posted: Wed Feb 11, 2004 3:13 pm
by ray.wurlod
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]')"
Posted: Wed Feb 11, 2004 3:17 pm
by ray.wurlod
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?
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).
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]')"
A BY.EXP clause specifies an exploded sort on a multi-valued field.
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