Help with SQL Query

Hello Everyone,

I need help writing this query. I have a table named "PMP" that stores up to 10 objective ID's:

Code Snippet

SELECT ObjectiveID1,ObjectiveID2,ObjectiveID3,ObjectiveID4,... ObjectiveID10

FROM PMP

WHERE PMPID = @.PMPID

I have a child table named "METRICS" that stores the details for each objective:

Code Snippet

SELECT ObjectiveID, TitleText, Description, KPIvalue

FROM METRICS

WHERE ObjectiveID IN (query the PMP table)

I need to write a query that will return all the Metrics that are used for a given PMPID. Any ideas how this can be done?

Thank You

My first suggestion is to correct a bad design mistake. You have a severly de-normalized table.

You most likely 'should' NOT have columns Objective1-Objective10.

You most likely would benefit from having a separate table for Objectives.

THEN issues such as this one would become MUCH easier to solve.

If you are using SQL 2005, you may find the UNPIVOT statement to be useful.

|||

Which version of SQL Server are you using?

AMB

|||

I am using SQL Server 2005 Express Edition

I understand why you would think this table is de-normalized, but it's the right structure for the solution. My organization allows a maximum of 10 objectives to be identified per request. I need to send my crystal report 1 record.

Can someone help me with the UNPIVOT query?

Thank You

|||

I figured it out:

SELECT * FROM COACHING.METRICS

WHERE OBJECTIVEID IN (

SELECT OBJECTIVEID

FROM

(SELECT RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID

FROM COACHING.PMPFORMS

WHERE PMPID = 271 ) OBJ

UNPIVOT

(OBJECTIVEID FOR PMPID IN (RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID)) VALS)

|||

Thanks Arnie Rowland and hunchback for your reponses

|||I'm glad we could point you in a direction that worked.sql

Help With Replication Configuration

Hi
I have not had much luck setting this up. Here is my
situation.
I have 3 stores which have inventory. Each store updates
their own inventory every few hours. I want all stores to
be able to see inventory from all stores.
I want this to be as close to real time as possible. I
used transactional replication with queued fail-over. I
set up Store A as the publisher and pushed subscriptions
to store B and C.
Here is my problem. When data is updated at store B it
overwrites the databases at A and C instead of just
syncing them. Same happens with A and C as they update in
a round robin fashion every few hours.
How can I set this up so A, B, and C can each update
their own inventory yet see everything from the other
stores?
Basically I want A,B and C to be in sync all the time.
Thanks
Sal DiStefano
Sal,
there are different ways to set this up, but the easiest is to have separate
tables for each store and have a view at each store that unions the 3
store's data. Replication is typically implemented using merge, with each
table being published separately. Technically it doesn't matter who is the
publisher as you ensure the store records can only be changed at the host
store.
HTH,
Paul Ibison

Help with Query

Hi Everyone,

I need help writing the following query. I have a table named DeptHistory that stores an employee's department history:

DeptHistory [DeptID(pk),EmpID(fk),StartDate,EndDate]

(EndDate will be NULL for the current department)

I need to write a query that will return all the department that employee was in between Jan 2007 to Jun 2007

Example:

--

Employee Name: Sam Costa

Service Dept 5/1/2006 - 12/5/2006

Parts Dept 12/6/2006 - 3/1/2007

Dispatch Dept 3/2/2007 - NULL

--

So the query should return Parts Dept and Dispatch Dept

Thank You,

-Sam

Code Snippet

select DeptID

from DeptHistory dh

where isnull(year(EndDate), 2007) = 2007

and EmpID = 1

|||

Hi DaleJ,

Thank you for your response. The query you provided does not check which depts the employee was in for a specifed date range.

|||

Well, If the end date isn't in 2007 (or null) then they weren't in that dept between jan 2007 and jun 2007.

|||

try the following query..

Code Snippet

Create Table #data (

[Dept] Varchar(100) ,

[SDate] datetime ,

[EDate] datetime

);

Insert Into #data Values('Service Dept','5/1/2006','12/5/2006');

Insert Into #data Values('Parts Dept','12/6/2006','3/1/2007');

Insert Into #data Values('Dispatch Dept','3/2/2007',NULL);

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or EDate between '01/01/2007' and '6/30/2007'

|||

what if an employee was employeed at a dept for the entire duration?

Code Snippet

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or isnull(EDate, getdate()) between '01/01/2007' and '6/30/2007'

or (SDate < '01/01/2007' and isnull(EDate, getdate()) > '6/30/2007')

also... for each end date, you'll have to check for null.

|||

As the approach below demonstrates, it is only necessary to determine if someone's StartDate is before the end of the period, and that their EndDate was sometime after the beginning of the period. (The current date is substitued for the NULL values.)

Code Snippet

SET NOCOUNT ON

DECLARE @.DeptHistory table
( RowID int IDENTITY,
DeptID int,
EmpID int,
StartDate smalldatetime,
EndDate smalldatetime
)

DECLARE @.Employee table
( EmpID int IDENTITY,
EmpName varchar(20)
)

DECLARE @.Department table
( DeptID int IDENTITY,
DeptName varchar(20)
)

INSERT INTO @.Employee VALUES ( 'Sam Costa' )
INSERT INTO @.Employee VALUES ( 'Bilbo Baggins' )

INSERT INTO @.Department VALUES ( 'Service' )
INSERT INTO @.Department VALUES ( 'Parts' )
INSERT INTO @.Department VALUES ( 'Dispatch' )

INSERT INTO @.DeptHistory VALUES ( 1, 1, '5/1/2006', '12/5/2006' )
INSERT INTO @.DeptHistory VALUES ( 1, 2, '5/1/2006', NULL )
INSERT INTO @.DeptHistory VALUES ( 2, 1, '12/6/2006', '3/1/2007' )
INSERT INTO @.DeptHistory VALUES ( 3, 1, '3/2/2007', NULL )

SELECT
d.DeptName,
e.EmpName,
h.StartDate,
h.EndDate
FROM @.DeptHistory h
JOIN @.Employee e
ON h.EmpID = e.EmpID
JOIN @.Department d
ON h.DeptID = d.DeptID
WHERE ( h.StartDate < '2007/07/01'
AND isnull( h.EndDate, getdate() ) >= '2007/01/01'
)

DeptName EmpName StartDate EndDate
-- -- -- -
Service Bilbo Baggins 2006-05-01 NULL
Parts Sam Costa 2006-12-06 2007-03-01
Dispatch Sam Costa 2007-03-02 NULL

(Output edited for display.)

Help with Oracle 8i Outer Join

I have a table which stores five important facts about a user:
ID (number)
First Name
Last Name
Type
Change_Indicator

The system, for some reason, stores maiden names of the female people in this table as well except the type = ALMD.

So, if I wanted to get a persons maiden name I go:
SELECT Last_Name FROM person WHERE type = ALMD and ID = 1234

Every time a person changes his or her details (any part of the name, etc.) a new row is inserted into the table and the old rows have a change_indicator flag set to Y.

So, if I have changed my name before and I want to get my latest record, I do:
SELECT * FROM person WHERE change_indicator IS NULL and ID = 12345

As a side note, the change_indicator for a maiden name is set to (something) (or other words NOT NULL).

The query Im trying is to get all users and, if applicable, their maiden name.

Sounds like a perfect candidate for a self outer join, right?

Here is what I have:

SELECT
s.first_name,
s.last_name as married_name,
m.last_name as maiden_name
FROM
person s,
person m
WHERE
s.change_indicator is null
and s.id = m.id(+)
and m.type_code = 'ALMD'
and s.last_name != m.last_name

The last little s.last_name != m.last_name is because there can be duplicates (say if someone was once married and is now divorced their maiden name will match their last name, etc.).

Ok, theres something wrong with that query. Its not doing an outer join. Only women with maiden names are selected. Assuming the query is correct I think it has something to do with the way Oracle 8i handles the join parameters, but I cant find anything online to tell me what to look for.

Help? :)Once you start outer joining a table, all joins and conditions for that table must take into account that it may be returning NULLs for a non-match. So neither of these conditions will work as is for someone with no maiden name:

and m.type_code = 'ALMD'
and s.last_name != m.last_name

This should work:

and m.type_code (+) = 'ALMD' -- definitely
and s.last_name != m.last_name (+) -- maybe

Though I'm not sure about (+) with != (and haven't got Oracle to hand to test it). If that doesn't work you could change the last condition to:

and (m.last_name is null or s.last_name != m.last_name)|||Duh! *slaps forhead*

I totally forgot about that. When I get into work tomorrow I'll give it a try.

Thanks for the help! :)|||I'm also working on a client database in Oracle, with versioning and a "most recent" flag which is the same as your "change_indicator". And, I also had prior Transact/SQL background, where the outer join operator would not apply when checkging against a constant.

There is an interesting performance issue here. For most of the time, the queries would like to pick up the most recent version (or at least start from there ). It is tempting to have a composite index on ( ID, Change_indicator ). The question is: will Oracle include into the index a record which has a null Change_indicator ?

Upon reading the documentation again, it seems to me that an ordinary B-tree index excludes null columns only if _all_ columns are null. This is not the case, since ID will never be null. Would anyone like to comment on this ?

In my database, I've side-stepped the issue, by using Y and N for my "most_recent" flag, but never null. I feel it's safer, since I'm relying heavily on that index.|||You are correct: Oracle only excludes rows from index where ALL indexed columns are NULL. You can check this for yourself like this:

SQL> create table t (a number, b number);

Table created.

SQL> create index tx on t (a,b);

Index created.

SQL> insert into t values (1,1);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME NUM_ROWS
---------- ----
TX 1

SQL> insert into t values (2,null);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME NUM_ROWS
---------- ----
TX 2

SQL> insert into t values (null,3);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME NUM_ROWS
---------- ----
TX 3

SQL> insert into t values (null,null);

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select index_name, num_rows from user_indexes where table_name='T';

INDEX_NAME NUM_ROWS
---------- ----
TX 3

As you can see, only the last insert of (NULL,NULL) did not get stored in the index.

BTW: andrewsc/andrewst - this could get confusing!|||>> andrewsc/andrewst - this could get confusing! <<

Thanks for pointing this out, it is an innocent coincidence.
It's not wise arguing with the moderator =:) .But then again, I'm using "andrewsc" for quite a while now, in all sorts of forums. E.g. http://www.oracle.com/forums/thread.jsp?forum=75&thread=54538&message=155898&q=#155898

For better clarity, I will add a signature.

BTW: who/why is sponsoring this forum ? Is there any commercial and/or community aspect to it ? I feel a vendor-neutral forum was long overdue in the Rdbms arena.

My best regards,

Andrew Schonberger
"andrewsc"
OTN member since Sep. 1998.|||This is a privately run forum. Paul, the admin, is an Oracle DBA. I'm a Sybase ASE DBA, and you'll find many other DBAs and such here. Luckily we're without sponsorship from vendors. ;)|||Originally posted by andrewsc
>> andrewsc/andrewst - this could get confusing! <<

Thanks for pointing this out, it is an innocent coincidence.
It's not wise arguing with the moderator =:) .But then again, I'm using "andrewsc" for quite a while now, in all sorts of forums. E.g. http://www.oracle.com/forums/thread.jsp?forum=75&thread=54538&message=155898&q=#155898

For better clarity, I will add a signature.

Hey, I just found it amusing! I wasn't suggesting you change it or anything. Mine actually stands for "Tony Andrews", but of course everybody thinks my first name is Andrew like you!