|
|
Posted by Jeff Kish on 06/19/06 16:36
Hi.
I have read lots, but obviously not the right stuff.
A reference pointer would be fine, or an answer. I in fact got an oracle
solution that I am still researching, but was wondering about either a generic
or an ms sql server solution?
I'd like to use sql to find out:
given a metadata table
where object is a table name
attribute is a col name,
keyseq is if the attribute is
part of a primary key
AND
given (by rules, not db design) that you should only have a single attribute
with a given non null value in keyseq
AND
table metatable(object varchar2, attribute varchar2, keyseq int)
table1, col1, 1
table1, col2, 2
table1, col3, null
table1, col4, null
table2, col1, 1
table2, col2, 1
table2, col3, null
table2, col4, null
table3, col1, 1
table3, col2, 2
table3, col3, null
table3, col4, null
I'd like to easily find out what tables have more than one attribute for any
keyseq that is not null, and which attribute it was with the problem.
Is it possible?
I started off with select object, attribute, keyseq from metatable
where keyseq is not null, and got a few thousand rows.
Then I tried a count(*) and group by (I wish I knew this aggregate stuff
better) like this:
select object, keyseq, count(keyseq) from metatable
where keyseq is not null
group by object, keycolseq
order by (count(keyseq))
and got a few thousand rows, where the very last row told me the object that
had more than one attribute with a single keyseq value.
So yes, I can figure it out, but I was hoping/wondering if there was a better
way, using just sql, that would return what object/attribute had the
'violation'.
I thought I might be able to use the above query in some sort of
subquery/exists combination, but I'm at a loss.
Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more advanced
solution I'm not showing because I don't understand it, and it might be oracle
specific.
select object, keyseq, count(keyseq)
from metatable
where keyseq is not null
group by object, keyseq
having count(*) > 1 ---<----- just this line to add
order by count(keyseq)
thanks for your time and knowledge.
Thanks
Jeff Kish
Jeff Kish
Navigation:
[Reply to this message]
|