Date selection criteria in Unidata stage

wdudek · Post by **wdudek** » Thu Feb 05, 2004 4:11 pm

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.

wdudek · Post by **wdudek** » Thu Feb 05, 2004 4:43 pm

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.

kduke · Post by **kduke** » Thu Feb 05, 2004 6:25 pm

Do a search on EVAL. Unidata is similar to Universe. It needs a valid dictionary item or EVAL.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 05, 2004 6:34 pm

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')"

wdudek · Post by **wdudek** » Wed Feb 11, 2004 10:18 am

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?

wdudek · Post by **wdudek** » Wed Feb 11, 2004 11:53 am

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?

ray.wurlod · Post by **ray.wurlod** » Wed Feb 11, 2004 3:13 pm

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]')"

ray.wurlod · Post by **ray.wurlod** » Wed Feb 11, 2004 3:17 pm

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