[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Getting a Subquery filed to be part of the main SELECT statement

Posted on 2007-04-03
4
Medium Priority
?
187 Views
Last Modified: 2010-03-20
Hello and how are things? I believe this is not as hard as I am making it to be, but I want to find the best way to do this SELECT statemtent. What I have is this:

Select TOP 10 * from dm2_employee dm2E Where Date_of_Term IS NULL AND control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi') AND EXISTS (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.Employee_Status from dm2_Emp_Activity dm2EA Group BY dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status)

In the subquery, I need to make that MAX(dm2EA.activity_date) part of the data set in the main query. How do I go about doing that with this SELECT statement?

C
0
Comment
Question by:Corey_819
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18843399
I would guess some query like this following:

Select TOP 10 *
from dm2_employee dm2E
JOIN (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.Employee_Status
from dm2_Emp_Activity dm2EA
Group BY dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status) l
on <some condition>
Where Date_of_Term IS NULL
AND control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')

however, as you had no joining condition in the exists subquery, you will have to put that yourself...
0
 
LVL 1

Author Comment

by:Corey_819
ID: 18843918
Thanks for your help and suggestion. One more quick question. I am receving a syntax error near the keyword ON. Thoguhts?

Select dm2E.Employee_number, dm2E.Employment_date, dm2E.Last_Name from dm2_employee dm2E JOIN (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.employee_status from dm2_emp_activity dm2EA Group by dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status) ON (dm2E.employee_number = dm2EA.employee_number) Where dm2E.Date_of_Term IS NULL AND dm2E.control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')  
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 260 total points
ID: 18843948
you forgot to give the joined query an alias name, like I put an "l" behind the closing bracket:

Select dm2E.Employee_number, dm2E.Employment_date, dm2E.Last_Name, l.Effective_Date
from dm2_employee dm2E
JOIN (Select dm2EA.employee_number, MAX(dm2EA.activity_date) AS Effective_Date, dm2EA.employee_status from dm2_emp_activity dm2EA Group by dm2EA.employee_number, dm2EA.activity_date, dm2EA.Employee_Status
) l
ON (dm2E.employee_number = l.employee_number)
Where dm2E.Date_of_Term IS NULL
AND dm2E.control_2 IN ('amho','chco','hosp','msvs','psvs','vent','cfoi')

tip:
* format your queries like above, makes them much easier to read
0
 
LVL 1

Author Comment

by:Corey_819
ID: 18844075
Thanks angellll for your help, DUH... I should have noticed that. Thanks again for your help> :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question