Help with an SQL Query

[Mike B]

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.

I'm a little stuck and any pointers will be really helpful.

thanks
Mike

--
Mike B

[Mike B]

"Mike B" <mr********************@nomail.yahoo.com> wrote in message
news:42********@news1.prserv.net

I apologize if this is off-topic. If it is off-topic, please direct
me to a more appropriate group.
<snipped >

I finally figured it out.
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.
All I had to do was to change the WHERE clause to
WHERE (E1.DOB Between v1.Last and V1.Start)

I'm a little stuck and any pointers will be really helpful.

I apologize for my off-topic post. Won't do it again.

--
Mike B

[Bob M. Lee]

"Mike B" <mr********************@nomail.yahoo.com> wrote in message
news:42********@news1.prserv.net...

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.