simple quey help - list of id's with multiple entries

[evild]

i must be going mad today cause i cant even do the simplest of sql!

i have a table called z308. This table looks like this:

Z308_REC_KEY---------------------- CHAR(22)
Z308_VERIFICATION--------------- VARCHAR2(20)
Z308_VERIFICATION_TYPE------- CHAR(2)
Z308_ID------------------------------ VARCHAR2(12)
Z308_STATUS----------------------- CHAR(2)
Z308_ENCRYPTION-----------------CHAR(1)

The Rec_Key is 22 characters long but the first two characters act as an identifier. The rest of the 20 chars are unique.

What I want to do is create a report of z308_ID's that have more than one '01' type of z308_rec_key. the rec_key is the primary key so there is one ID for every unique rec_key.

i.e. list the IDs that have MORE THAN ONE rec_key beginning with '01'.

I know this is simple but i cant seem to think today. Help needed.

btw i use pl/sql so i can use the 'substr' funtion to strip out characters check.
e.g. substr(z308_rec_key,1,2) will return the first 2 chars of z308_rec_key.

 

[avjoshi]

If I understood you correctly then what you want is to do create a report of z308_ID's that have more than one '01' type of z308_rec_key.
Then the sql statement would look like,

Select z308_id from <your table name> where substr(z308_rec_key,1,2) = ‘01’;

I am sure you want something more than that. Can you clarify?


Anand

 

[avjoshi]

I commited a mistek of posting wrong sql statement,

this is what I meant to post,

SELECT z308_id,SUBSTR(z308_rec_key,1,2),COUNT(1) FROM <your TABLE name>
WHERE SUBSTR(z308_rec_key,1,2) = ‘01’
GROUP BY z308_id,SUBSTR(z308_rec_key,1,2)
HAVING COUNT(1) > 1 ;


Anand

 

[evild]

thanks, thats what i was looking for exactly!

i think i need to bruch up on my sql. clearly i am rusty these days. doesn help not doing any sql in months...