Lookup using a Order by clause

sandyindu · Post by **sandyindu** » Mon Mar 02, 2009 3:11 am

In one of our jobs we are using TIME_DIMENSION lookup..We are using below query in TIME_DIMENSION oracle stage "SELECT TIME_DIM_KEY,MONTH_OF_YEAR,YEAR FROM time_dimension where year=2008 and MONTH_OF_YEAR=11 ORDER BY
TIME_DIM_DATE" to get values of TIME_DIM_KEY,MONTH_OF_YEAR,YEAR.We need to get TIME_DIM_KEY for a source data based on YEAR,MONTH.

My doubt is as we are using ORDER BY clause which TIME_DIM_KEY we will get.Always we will get only one TIME_DIM_KEY or is there any possibility that we can get different values.I am getting different TIME_DIM_KEY values.

What's wrong in this query.Please suggest.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Mar 02, 2009 4:06 am

There may be multiple records for the month+year combination. something like one for each day. So you may be getting different values.

What are you key links to lookup? Does the mentioned sql returns more than one row?

ray.wurlod · Post by **ray.wurlod** » Mon Mar 02, 2009 4:24 am

What is the grain of this time dimension?

In every query engine I have ever encountered, the selection is completed before the sort is attempted. This is so that the smallest possible number of records can be sorted.