CODEWITHSUNDEEP
sqloracle-database
PrivateOmega
PrivateOmega

Reputation: 2880

Can't figure out the SQL code

enter image description here

CREATE TABLE empinf(
companyid varchar2(5) PRIMARY KEY,
companyname varchar2(30) NOT NULL,
emailid varchar2(20) REFERENCES usrinf(emailid),
Mobile number CONSTRAINT moc CHECK(LENGTH(Mobile)=10),
city varchar2(20),
industrytype varchar2(20),
functionalarea varchar2(20),
membershipplan varchar2(20) CONSTRAINT cmp CHECK(membershipplan in ('TRIAL','PREMIUM MONTHLY','PREMIUM YEARLY')),
dateofsignup date DEFAULT SYSDATE CONSTRAINT chd CHECK( dateofsignup>=SYSDATE ),
dateofrenewal date generated always as 
    (CASE
        WHEN membershipplan='TRIAL' THEN SYSDATE+14 
        WHEN membershipplan='PREMIUM MONTHLY' THEN SYSDATE+30
        WHEN membershipplan='PREMIUM YEARLY' THEN SYSDATE+365
        ELSE SYSDATE
    END
    ) virtual,
renewalstatus varchar2(20) CONSTRAINT chrs CHECK(renewalstatus in('ACTIVE','EXPIRED')),
CONSTRAINT mun UNIQUE(Mobile)
)

This code is to be generated in Oracle Express 11G, I am unable to figure out what's wrong with code, it shows 'missing paranthesis error'.

Upvotes: 1

Views: 93

Answers (2)

etsa
etsa

Reputation: 5060

As far as I know, in a virtual column you need to define a deterministic function (a one which return same value, irrespective of when you call it.

You could try to use dateofsignup instead of SYSDATE inside case .

dateofrenewal date generated always as 
    (CASE
        WHEN membershipplan='TRIAL' THEN dateofsignup+14 
        WHEN membershipplan='PREMIUM MONTHLY' THEN dateofsignup+30
        WHEN membershipplan='PREMIUM YEARLY' THEN dateofsignup+365
        ELSE dateofsignup
    END
    ) virtual

Or you can remove it from table and use a view.

I forgot the other error (I was concentrating on the first one, so I apologize for the miss). As Thorsten say in other answer, you have a problem to set a check constraint for datesignup in that way.
Other that solution Torstein suggested, another solution could be to add another column (eg. DATE_INSERT DATE DEFAULT SYSDATE)

and write the constraint changing datesignup definition as

dateofsignup date DEFAULT SYSDATE

and add

ALTER TABLE empinf ADD CONSTRAINT chd CHECK( dateofsignup>=DATE_INSERT)

Your missing parenthesis error can be caused by your tries, while you commented some parts of the code.

Tip: I agree with g00dy comment above: create table basic structure with one command, then add constraint with separate commands.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

It is strange you are getting a "missing paranthesis" error. Maybe the tool you are using to execute the statement swallows the last paranthesis?

I am getting "ORA-02436: date or system variable wrongly specified in CHECK constraint"

CONSTRAINT chd CHECK( dateofsignup>=SYSDATE )

You cannot use SYSDATE in a check constraint, as SYSDATE is subject to change. A check constraint must be stable and must not depend on the current time, a current session setting or the like. You'd need a trigger to do what you want to do.

After removing that check constraint I get "ORA-54002: only pure functions can be specified in a virtual column expression", which is about for the same reason.

dateofrenewal date generated always as 
    (CASE
        WHEN membershipplan='TRIAL' THEN SYSDATE+14 
...

A virtual column must be stable and can only depend on other columns, not on the current time, a current session setting or the like. You probably want dateofrenewal to be calculated based on dateofsignup instead.

Upvotes: 1

Related Questions