Home » SQL & PL/SQL » SQL & PL/SQL » Need help with approach to SQL SELECT
icon5.gif  Need help with approach to SQL SELECT [message #199877] Thu, 26 October 2006 09:41 Go to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Hey folks,

I've baffled myself with this problem. I suspect there's SOME way to get at it through SQL (and not PL/SQL), but I can't for the life of me figure it out. Suggestions welcomed...

The table I'm selecting from essentially describes a hierarchy that looks something like this:

CREATE TABLE HIERARCHY ( 
  CHILD_ORGANIZATION_ID           VARCHAR2 (200), 
  CHILD_ORGANIZATION_NAME         VARCHAR2 (100), 
  PARENT_1                        VARCHAR2 (100), 
  PARENT_2                        VARCHAR2 (100), 
  PARENT_3                        VARCHAR2 (100), 
  PARENT_4                        VARCHAR2 (100), 
  PARENT_5                        VARCHAR2 (100), 
  PARENT_6                        VARCHAR2 (100), 
  ORGANIZATION_LEVEL              NUMBER (2), 
  EFFECTIVE_START_DATE            DATE, 
  EFFECTIVE_END_DATE              DATE, 
 ) ; 


The goal of my query is to list any child_organization_ids whose parent is end-dated (EFFECTIVE_END_DATE is not null).

Some notes:

- The ORGANIZATION_LEVEL is a number that describes where that row fits in the hierarchy. For example, if ORGANIZATION_LEVEL is 6, then the immediate parent of that row will be stored in PARENT_5, and PARENT_6 will be null.

- This table has one row for each organization in the hierarchy except for the very top of the hierarchy. The values listed in the various PARENT_X columns exactly match values in CHILD_ORGANIZATION_NAME.

The trouble I run into is this: for each row in the table, in order to determine who is the parent, I have to look at the organization_level, and from there I can know which of the 6 parent_X columns to check. Then I have to check the row whose CHILD_ORGANIZATION_NAME matches that value, and see if the row is end-dated. I can see how I could do this with PL/SQL, but not with a single select statement.

Any questions or suggestions would be greatly appreciated.

Thanks!

Steve
Re: Need help with approach to SQL SELECT [message #199882 is a reply to message #199877] Thu, 26 October 2006 09:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
something like this do it for you?

CREATE TABLE HIERARCHY (
CHILD_ORGANIZATION_ID VARCHAR2 (200),
CHILD_ORGANIZATION_NAME VARCHAR2 (100),
PARENT_1 VARCHAR2 (100),
PARENT_2 VARCHAR2 (100),
PARENT_3 VARCHAR2 (100),
PARENT_4 VARCHAR2 (100),
PARENT_5 VARCHAR2 (100),
PARENT_6 VARCHAR2 (100),
ORGANIZATION_LEVEL NUMBER (2),
EFFECTIVE_START_DATE DATE,
EFFECTIVE_END_DATE DATE
) ;

select
parent.child_organization_id
,parent.child_organization_name
,parent.effective_start_date
,parent.effective_end_date
,case
when children.organization_level = 1 and parent.child_organization_name = children.parent_1 then 1
when children.organization_level = 2 and parent.child_organization_name = children.parent_2 then 2
when children.organization_level = 3 and parent.child_organization_name = children.parent_3 then 3
when children.organization_level = 4 and parent.child_organization_name = children.parent_4 then 4
when children.organization_level = 5 and parent.child_organization_name = children.parent_5 then 5
when children.organization_level = 6 and parent.child_organization_name = children.parent_6 then 6
end organization_level
,case
when children.organization_level = 1 and parent.child_organization_name = children.parent_1 then children.parent_1
when children.organization_level = 2 and parent.child_organization_name = children.parent_2 then children.parent_2
when children.organization_level = 3 and parent.child_organization_name = children.parent_3 then children.parent_3
when children.organization_level = 4 and parent.child_organization_name = children.parent_4 then children.parent_4
when children.organization_level = 5 and parent.child_organization_name = children.parent_5 then children.parent_5
when children.organization_level = 6 and parent.child_organization_name = children.parent_6 then children.parent_6
end parent_x
from
(
select *
from hierarchy
where effective_end_date is not null
) parent
,hierarchy children
where (
children.organization_level = 1 and parent.child_organization_name = children.parent_1
or children.organization_level = 2 and parent.child_organization_name = children.parent_2
or children.organization_level = 3 and parent.child_organization_name = children.parent_3
or children.organization_level = 4 and parent.child_organization_name = children.parent_4
or children.organization_level = 5 and parent.child_organization_name = children.parent_5
or children.organization_level = 6 and parent.child_organization_name = children.parent_6
)
/


Good luck, Kevin
Re: Need help with approach to SQL SELECT [message #199888 is a reply to message #199877] Thu, 26 October 2006 10:30 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
OK, that's a great suggestion! It's not quite working (yet), but mainly because I didn't include every single piece of information, because that would involve posting more company data than I'm comfortable with.

However, I think the case thingy may help me resolve the issue, so off to play some more!

Thanks very much! (Other ideas still welcomed too, of course! Very Happy)

Steve
Re: Need help with approach to SQL SELECT [message #200347 is a reply to message #199888] Mon, 30 October 2006 07:47 Go to previous message
sbattisti
Messages: 39
Registered: June 2005
Member
Kevin,

Thanks so much for your help. Unfortunately, I was never quite able to get it to work in time, and meanwhile someone else made it work using PL/SQL. Smile *grumble*

But, your information was very helpful, nonetheless!

Thanks again for taking the time to reply...

Steve
Previous Topic: ORA-13203
Next Topic: Return a random value from a list of numbers minus a specified range
Goto Forum:
  


Current Time: Sat Dec 03 03:43:16 CST 2016

Total time taken to generate the page: 0.09105 seconds