SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Create Table Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JPgy
Starting Member

2 Posts

Posted - 03/31/2013 :  03:39:36  Show Profile  Reply with Quote
The code comes out error, it caused from the "case statement" line
Please somebody help to correct this code.
Thanks in advanced

create table payment
(
paymentid number(10) constraint payment_paymentid_pk primary key,
tenantno number (5) not null constraint payment_tenantno_fk references tenant(tenantno),
propid number(5) not null constraint payment_propid_fk references proplookup(proptype),
paydate date default sysdate not null,
amount number(8,2) not null constraint payment_amount_ck check(amount>0),
paidby number(1) not null constraint payment_paidby_ck check(paidby in('cash','chq','credit','bpay')
case paidby 
when 'cash' then paidby = 1
when 'chq' then paidby = 2
when 'credit' then paidby = 3
when 'bpay' then paidby = 4
else paidby = 0
end),
mthof number(2) not null constraint payment_mthof_ck check(mthof between 1 and 12)
);


Error at Command Line:8 Column:101
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/31/2013 :  03:53:03  Show Profile  Reply with Quote
that case statement doesnt make any sense. Are you trying to set a new column which is computed based on paidby field? Also you've braces put in wrong place which is what error suggests.

One more thing to note is this is a ms sql server forum and you're using Oracle. So suggestion posted here may not work well with Oracle as people here are experts on Sql server and not much proficient in Oracle. So you may be better off posting in some Oracle forums like www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JPgy
Starting Member

2 Posts

Posted - 03/31/2013 :  05:38:41  Show Profile  Reply with Quote
Hi visakh16,

yup, I'm trying to set a new column which its value will be computed based on the constraint "case" before inserted into table.
any syntax error there?

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  00:21:25  Show Profile  Reply with Quote
in T-SQL, it should be as below (you can try same in Oracle and see if it works)

create table payment
(
paymentid number(10) constraint payment_paymentid_pk primary key,
tenantno number (5) not null constraint payment_tenantno_fk references tenant(tenantno),
propid number(5) not null constraint payment_propid_fk references proplookup(proptype),
paydate date default sysdate not null,
amount number(8,2) not null constraint payment_amount_ck check(amount>0),
paidby number(1) not null constraint payment_paidby_ck check(paidby in('cash','chq','credit','bpay')),
case paidby 
when 'cash' then paidby = 1
when 'chq' then paidby = 2
when 'credit' then paidby = 3
when 'bpay' then paidby = 4
else paidby = 0
end as newcolumn,
mthof number(2) not null constraint payment_mthof_ck check(mthof between 1 and 12)
);


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000