Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL error
Oracle SQL error [message #258278] Fri, 10 August 2007 14:32 Go to next message
njgirl
Messages: 8
Registered: August 2007
Junior Member
OS :SunOS5.8
DB : oracle 10.2.0.3

Hi

I am running this case statements and getting below errors. pls help me to figure out the error.

case 01 error :'error -ORA-00905: missing keyword'
case 02 error : 'ORA-00920: invalid relational operator'

Case 01

select (cba.billing_name1||' '||cba.billing_name2) as cust_bill_name from USRP.cust_billing_acct cba,USRP.circuit ckt,USRP.customer cust
where
case when ckt.CKT_MCN is NOT NULL THEN cba.MCN = ckt.CKT_MCN ELSE cba.MCN = cust.MCN where ckt.C_ID = cust.CUSTOMER_ID end
AND case when ckt.CKT_GRC IS NOT NULL THEN cba.GRC = ckt.CKT_GRC ELSE cba.GRC = cust.GRC where ckt.C_ID = cust.CUSTOMER_ID end
AND case when ckt.CKT_SOC IS NOT NULL THEN cba.SOC = ckt.CKT_SOC ELSE cba.SOC = cust.SOC where ckt.C_ID = cust.CUSTOMER_ID end

case 02

select (cba.billing_name1||' '||cba.billing_name2) as cust_bill_name from USRP.cust_billing_acct cba,USRP.circuit ckt,USRP.customer cust
where
case
IF ckt.CKT_MCN IS NOT NULL THEN cba.MCN = ckt.CKT_MCN ELSE cba.MCN = cust.MCN where ckt.C_ID = cust.CUSTOMER_ID
AND IF ckt.CKT_GRC IS NOT NULL THEN cba.GRC = ckt.CKT_GRC ELSE cba.GRC = cust.GRC where ckt.C_ID = cust.CUSTOMER_ID
AND IF ckt.CKT_SOC IS NOT NULL THEN cba.SOC = ckt.CKT_SOC ELSE cba.SOC = cust.SOC where ckt.C_ID = cust.CUSTOMER_ID
End



Thanks


Re: Oracle sql error [message #258279 is a reply to message #258278] Fri, 10 August 2007 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW
http://www.orafaq.com/forum/t/85037/74940/

Fixing syntax errors is NOT an EXPERT level question.

Remove the () from the SELECT lines
Re: Oracle sql error [message #258292 is a reply to message #258279] Fri, 10 August 2007 18:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> select empno, ename from emp
  2  where case mod(empno,2) when 1 then empno else null end = empno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7839 KING

SQL> select empno, ename from emp
  2  where case mod(empno,2) when 1 then empno else null end ;
where case mod(empno,2) when 1 then empno else null end
                                                       *
ERROR at line 2:
ORA-00920: invalid relational operator


SQL> select empno, ename from emp
  2  where case mod(empno,2) when 1 then empno else null where empno = empno end;
where case mod(empno,2) when 1 then empno else null where empno = empno end
                                                    *
ERROR at line 2:
ORA-00905: missing keyword

HTH

cheers

Rajaram
Re: Oracle SQL error [message #258393 is a reply to message #258278] Sat, 11 August 2007 08:17 Go to previous message
njgirl
Messages: 8
Registered: August 2007
Junior Member
Thank you all for responses.
Issue has been solved.

Thanks

Previous Topic: execute immediate
Next Topic: pivot concept doubt
Goto Forum:
  


Current Time: Sat Dec 10 01:31:46 CST 2016

Total time taken to generate the page: 0.12283 seconds