By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

Help with an SQL Query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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
Nov 12 '05 #2

P: n/a
"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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.