CODEWITHSUNDEEP
oracle-database
KarthiK
KarthiK

Reputation: 93

Ignore some conditions in 'case' statement

I am trying to create a script which will get constraints automatically from system table in oracle, i just want primary key(P) and foreign key(R) and i have to ignore other constraints like C,U,V,O.

SELECT
    B.COLUMN_NAME,
    CASE
      WHEN A.CONSTRAINT_TYPE = 'P' THEN ', PRIMARY KEY('||B.COLUMN_NAME||')'
      WHEN A.CONSTRAINT_TYPE = 'R' THEN ', FOREIGN KEY('||B.COLUMN_NAME||') 
      REFERENCES'||' '||(SELECT TABLE_NAME FROM all_cons_columns WHERE OWNER 
      ='XXXXXXX' AND COLUMN_NAME = B.COLUMN_NAME AND CONSTRAINT_NAME != 
      A.CONSTRAINT_NAME) ||'('||B.COLUMN_NAME||')'
    ELSE ' '
    END AS CONSTRAINTS
    FROM ALL_CONSTRAINTS A JOIN TABLE_SCRIPT2 B
    ON B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
    WHERE OWNER= 'XXXXXXX' 
    AND TABLE_NAME = 'XXXXXXX'  )

When i run the script i got an output like this

   , PRIMARY KEY(XXXXXXX)














, FOREIGN KEY(XXXXXX) REFERENCES YYYYY(XXXXX)

for the other constraints i got spaces, if i remove the else condition i will get (null) instead of spaces, how to get only P,K without null or space.

Upvotes: 0

Views: 103

Answers (1)

RGruca
RGruca

Reputation: 204

Add a below condition to WHERE caluse

A.CONSTRAINT_TYPE IN ('P','R')

Upvotes: 1

Related Questions