"Mike B" <mrcics2000-news-nomail@[EMAIL PROTECTED]
> wrote in message
news:42711639_3@[EMAIL PROTECTED]
> I apologize if this is off-topic. If it is off-topic, please direct me
to
> a more appropriate group.
>
> I am writing an SQL query for a project and am a little stuck. I need to
> classify a list of employees into age brackets. (E.g.. 16-15, 25-35,
etc.)
> I am using the Micro Focus NetExpress SQL Wizard in DB2 compatibility
> mode.
>
> I created a table AgeBrackets
>
> Range | Lower | Upper
> -----------------------
> 16-25| 16 |25
> 25-35|25 |35
>
> Then I created a view:
>
> CREATE VIEW vAgeRange (
> Range,
> Start,
> Last)
> As
> SELECT A1.RangeDesc,
> Start_Date = Today - A1.RangeLow years + 1
days,
> End_Date = Today - A1.RangeHigh years
> FROM ALAMEDA.ALAMEDA.AGERANGE A1
>
> To put the age brackets in dates starting from today, so I get the
> earliest and latest date someone can be born to be in an age bracket.
>
> I am now trying to create a view where for each row in my EMPLOYEE
table,
> I want to compare the Employee_DateOfBirth to the Start_Date, end_Date
and
> if it is BETWEEN those dates, I want to insert the RangeDesc into the
new
> view.
>
> This is what I tried:
>
> SELECT E1.EMPLOYEE, E1.DoB, V1.Range
> FROM ALAMEDA.ALAMEDA.EMPLOYEE E1,
> ALAMEDA.ALAMEDA.VAGERANGE V1
> WHERE (E1.DOB BETWEEN V1.Start AND V1.Last)
>
> But this returns an empty Query.
>
Sure. The inner product of two tables without a common set of column
values
is an empty set.
What you need is a nested SELECT clause:
SELECT
E1.EMPLOYEE,
E1.DOB,
(SELECT V1.RANGE
FROM ALAMEDA.ALAMEDA.VAGERANGE V1
WHERE E1.DOB BETWEEN V1.START and V1.LAST
AND E1.DOB IS NOT NULL
AND E1.DOB <= TODAY
AND E1.DOB > (TODAY - 130 YEARS)
LIMIT TO 1 ROW)
FROM ALAMEDA.ALAMEDA.EMPLOYEE E1;
The nested SELECT clause locks the E1.DOB value for each row of EMPLOYEE -
and then
tests each E1.DOB value against each row of the view VAGERANGE to pull the
V1.RANGE value.
|
