CODEWITHSUNDEEP
sqloracle-database
icecurtain
icecurtain

Reputation: 675

oracle sql select script help

Can any one please help with select script?

desired outcome 

 TABLENAME, ATTRIBUTE, NULLABLE, DATATYPE, LENGTH, DESC, HELP_TEXT having count(datadic.ATTRIBUTE)>1  (because these can only be unique)

and is not unique to any other attribute (not include DDKEY and SerNo) with the same value.

i.e 

 TABLE2 ADDR4 NULL VARCHAR2 15 Unit Address Line 4 Unit address line 4 
 TABLE1 ADDR4 NULL VARCHAR2 30 Unit Address Line 4 Unit address line 4

I am trying to get my head around nested queries

Real life senerio I want to see if my database has any attributes with different values in different tables a sort of house keeping exercise.

I have 154 table 700 attributes

so far

 select attribute,count(DATADIC.ATTRIBUTE) as "ATTRIBUTE" 
 from "DATADIC" "DATADIC"
 group by DATADIC.ATTRIBUTE 
 having count(datadic.ATTRIBUTE)>1)
 order by count(DATADIC.ATTRIBUTE)

then

 select TABLENAME,ATTRIBUTE,NULLABLE, DATATYPE, LENGTH, DESC, HELP_TEXT 
 from datadic 
 where      
 attribute in
 (select attribute,count(DATADIC.ATTRIBUTE) as "ATTRIBUTE" 
   from "DATADIC" "DATADIC"
   group by DATADIC.ATTRIBUTE
   having count(datadic.ATTRIBUTE)>1)
   order by count(DATADIC.ATTRIBUTE)

has too many values

 desc
 DATADIC.
  DDKEY Number 
  SER Number 
  TABLENAME Varchar2 15 
  ATTRIBUTE Varchar2 25
  NULLABLE Varchar2 10 
  DATATYPE Varchar2 15  
  LENGTH number(7,3) 
  DESCRIPTION Varchar2 30
  HELP_TEXT Varchar2 1000

sample data

556 5 TABLE2 ADDR1 NULL VARCHAR2 30 Unit Address Line 1 Unit address line 1 
545 5 TABLE1 ADDR1 NULL VARCHAR2 30 Unit Address Line 1 Unit address line 1 
546 6 TABLE1 ADDR2 NULL VARCHAR2 30 Unit Address Line 2 Unit address line 2 
557 6 TABLE2 ADDR2 NULL VARCHAR2 30 Unit Address Line 2 Unit address line 2 
547 7 TABLE1 ADDR3 NULL VARCHAR2 30 Unit Address Line 3 Unit address line 3 
558 7 TABLE2 ADDR3 NULL VARCHAR2 30 Unit Address Line 3 Unit address line 3 
559 8 TABLE2 ADDR4 NULL VARCHAR2 15 Unit Address Line 4 Unit address line 4 
548 8 TABLE1 ADDR4 NULL VARCHAR2 30 Unit Address Line 4 Unit address line 4

useful script

CREATE TABLE  "DATADIC" 
(   "DDKEY" NUMBER, 
"SER" NUMBER, 
"TABLENAME" VARCHAR2(15), 
"ATTRIBUTE" VARCHAR2(25), 
"NULLABLE" VARCHAR2(10), 
"DATATYPE" VARCHAR2(15), 
"LENGTH" NUMBER(7,3), 
"DESCRIPTION" VARCHAR2(30), 
"HELP_TEXT" VARCHAR2(1000), 
 CONSTRAINT "DATADIC_PK" PRIMARY KEY ("DDKEY") ENABLE
)

/

CREATE OR REPLACE TRIGGER  "bi_DATADIC" 
before insert on "DATADIC"              
for each row 
begin  
  for c1 in ( 
   select "DATADIC_SEQ".nextval next_val 
    from dual 
  ) loop 
  :new."DDKEY" :=  c1.next_val; 
 end loop; 
end;

/
ALTER TRIGGER  "bi_DATADIC" ENABLE
/

data csv

 556,5,TABLE2,ADDR1,NULL,VARCHAR2,30,Unit Address Line 1,Unit address line 1
 557,6,TABLE2,ADDR2,NULL,VARCHAR2,30,Unit Address Line 2,Unit address line 2
 558,7,TABLE2,ADDR3,NULL,VARCHAR2,30,Unit Address Line 3,Unit address line 3
 559,8,TABLE2,ADDR4,NULL,VARCHAR2,15,Unit Address Line 4,Unit address line 4
 545,5,TABLE1,ADDR1,NULL,VARCHAR2,30,Unit Address Line 1,Unit address line 1
 546,6,TABLE1,ADDR2,NULL,VARCHAR2,30,Unit Address Line 2,Unit address line 2
 547,7,TABLE1,ADDR3,NULL,VARCHAR2,30,Unit Address Line 3,Unit address line 3
 548,8,TABLE1,ADDR4,NULL,VARCHAR2,30,Unit Address Line 4,Unit address line 4

updated data is now using this query but I need not make the not unique

select * from ( select TABLENAME, ATTRIBUTE, NULLABLE,
DATATYPE,
LENGTH,
DESCRIPTION,
HELP_TEXT, count(*) over (partition by attribute) attr_count
from datadic ) where attr_count > 1

TABLENAME ATTRIBUTE NULLABLE DATATYPE LENGTH DESCRIPTION HELP_TEXT ATTR_COUNT TABLE2 ADDR1 NULL VARCHAR2 30 Unit Address Line 1 Unit address line 1 2 TABLE1 ADDR1 NULL VARCHAR2 30 Unit Address Line 1 Unit address line 1 2 TABLE1 ADDR2 NULL VARCHAR2 30 Unit Address Line 2 Unit address line 2 2 TABLE2 ADDR2 NULL VARCHAR2 30 Unit Address Line 2 Unit address line 2 2 TABLE1 ADDR3 NULL VARCHAR2 30 Unit Address Line 3 Unit address line 3 2 TABLE2 ADDR3 NULL VARCHAR2 30 Unit Address Line 3 Unit address line 3 2 TABLE2 ADDR4 NULL VARCHAR2 30 Unit Address Line 4 Unit address line 4 2 TABLE1 ADDR4 NULL VARCHAR2 30 Unit Address Line 4 Unit address line 4 2

Upvotes: 1

Views: 935

Answers (1)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

select TABLENAME,ATTRIBUTE,NULLABLE, DATATYPE, LENGTH, DESC, HELP_TEXT from datadic where attribute in (select attribute,count(DATADIC.ATTRIBUTE) as "ATTRIBUTE" from "DATADIC" "DATADIC" group by DATADIC.ATTRIBUTE having count(datadic.ATTRIBUTE)>1) order by count(DATADIC.ATTRIBUTE)

You are getting a too many values because the query is expecting one column after the "IN" Clause. Try the following query to get all the rows in the table with an attribute that has occurred more than once.

select TABLENAME,
       ATTRIBUTE,
       NULLABLE, 
       DATATYPE, 
       LENGTH, 
       DESC, 
       HELP_TEXT 
  from datadic 
  where attribute in 
  (
  select attribute from (
  select attribute,
         count(DATADIC.ATTRIBUTE) as "ATTRIBUTE" 
   from "DATADIC" "DATADIC" 
   group by DATADIC.ATTRIBUTE 
   having count(datadic.ATTRIBUTE)>1) 
   order by count(DATADIC.ATTRIBUTE)
   )
  )

You can make good use of Analytic functions to answer this query in a simpler way.

select * from (
select TABLENAME,
       ATTRIBUTE,
       NULLABLE, 
       DATATYPE, 
       LENGTH, 
       DESC, 
       HELP_TEXT,
       count(*) over (partition by attribute) attr_count 
  from datadic
) where attr_count > 1

Upvotes: 1

Related Questions