April 21, 2017 at 4:04 pm
SQL newbee: How do I loop thru the return data (HRORGANIZATIONUNIT.PARENTUNIT) so I can do some post processing using the IF ELSE?
WHILE (SELECT HRORGANIZATIONUNIT.PARENTUNIT
FROM lm_ghr.HRORGANIZATIONUNIT, lm_ghr.WORKASSIGNMENT, lm_ghr.EMPLOYEE , lm_ghr.EMPLOYEEBENEFIT
WHERE EMPLOYEE.EMPLOYEE = WORKASSIGNMENT.EMPLOYEE
AND HRORGANIZATIONUNIT.HRORGANIZATIONUNIT = WORKASSIGNMENT.HRORGANIZATIONUNIT
AND EMPLOYEE.PRIMARYWORKASSIGNMENT = WORKASSIGNMENT.WORKASSIGNMENT
AND [EMPLOYEEBENEFIT].EMPLOYEE = WORKASSIGNMENT.EMPLOYEE
AND [EMPLOYEEBENEFIT].BENEFITPLAN = ('2017 WCHN DEN')
AND [EMPLOYEEBENEFIT].DELETEFLAG = 0x00000000000000000000000000000000
AND [EMPLOYEEBENEFIT].STATUS = 6
AND [EMPLOYEEBENEFIT].COVERAGECLASS = 1
AND [EMPLOYEEBENEFIT].DREND > getdate()) IS NOT NULL
BEGIN
IF (SELECT HRORGANIZATIONUNIT.PARENTUNIT FROM lm_ghr.HRORGANIZATIONUNIT) = 1
BREAK
ELSE
CONTINUE
END
April 21, 2017 at 4:16 pm
SQL isn't a procedural language so in general you do NOT want to set something up to loop with it. Trying to use it as if it's C# or java is likely going to get you in trouble. Syntax errors aside - what exactly are you trying to do with the loop you're trying to set up?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 23, 2017 at 7:28 am
This data "HRORGANIZATIONUNIT.PARENTUNIT" from the SELECT STATEMENT returns sub-divisions that they report to , I want to:
1. Get the first data point
2. Run a SQL query against it to find its next level division that it reports to. KEEP looping until the very TOP LEVEL DIVISION is found.
3. Repeat steps 1 and 2 for the second, third ....data point until the end
Data looks like this:
| SubDiv
1 | 59
2 | 21
3 | 60
You are correct, I am learning that it is not a procedural language and trying very hard to stop seeing it that way
April 23, 2017 at 12:24 pm
patrick.domond - Sunday, April 23, 2017 7:28 AMThis data "HRORGANIZATIONUNIT.PARENTUNIT" from the SELECT STATEMENT returns sub-divisions that they report to , I want to:
1. Get the first data point
2. Run a SQL query against it to find its next level division that it reports to. KEEP looping until the very TOP LEVEL DIVISION is found.
3. Repeat steps 1 and 2 for the second, third ....data point until the endData looks like this:
| SubDiv
1 | 59
2 | 21
3 | 60
You are correct, I am learning that it is not a procedural language and trying very hard to stop seeing it that way
This sounds like you need to spend some time with Jeff Moden's articles on hierarchies
Hierarchies on Steroids #1
Hierarchies on Steroids #2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply