Home » SQL & PL/SQL » SQL & PL/SQL » help with CASE (oracle 10.1.2)
help with CASE [message #352444] Tue, 07 October 2008 12:47 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
When i'm running i'm getting the following error message :
ORA-00904:"PRIMES_AND_SUBS_VE:FIRM_TYPE_CODE" is an invalid identifier.

SELECT DISTINCT CONTRACT_VW2.ACTION_AMOUNT,CONTRACT_VW2.CONTRACT_NUMBER,CONTRACT_VW2.FIRM_NAME,CONTRACT_VW2.CONTRACT_ID
,CONTRACT_VW2.CONTRACT_TYPE,CONTRACT_VW2.AMENDMENT_NUMBER,ASSIGNMENT_PO_VW.PO_DATE,ASSIGNMENT_PO_VW.WORK_ORDER_AMOUNT
,PRIMES_AND_SUBS_VW.FIRM_TYPE_CODE
FROM(SELECT
       CASE WHEN CONTRACT_VW2.CONTRACT_TYPE='On Call' THEN ASSIGNMENT_PO_VW.WORK_ORDER_AMOUNT
	   ELSE CONTRACT_VW2.ACTION_AMOUNT
	   END
FROM CONTRACT_VW2,ASSIGNMENT_PO_VW,PRIMES_AND_SUBS_VW
WHERE CONTRACT_VW2.CONTRACT_NUMBER=ASSIGNMENT_PO_VW.CONTRACT_NUMBER
AND CONTRACT_VW2.CONTRACT_NUMBER=PRIMES_AND_SUBS_VW.CONTRACT_NUMBER
AND CONTRACT_VW2.AMENDMENT_NUMBER=0
)
UNION ALL
SELECT DISTINCT CONTRACT_VW2.ACTION_AMOUNT,CONTRACT_VW2.CONTRACT_NUMBER,CONTRACT_VW2.CONTRACT_ID,CONTRACT_VW2.CONTRACT_TYPE
,CONTRACT_VW2.AMENDMENT_NUMBER,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM CONTRACT_VW2
WHERE AMENDMENT_NUMBER<>0


Please help me
Thanks

[Mod-Edit: Frank added linebreaks in long lines in code]

[Updated on: Wed, 08 October 2008 01:02] by Moderator

Report message to a moderator

Re: help with CASE [message #352451 is a reply to message #352444] Tue, 07 October 2008 14:03 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aarti81,

First,
CASE WHEN CONTRACT_VW2.CONTRACT_TYPE='On Call' THEN ASSIGNMENT_PO_VW.WORK_ORDER_AMOUNT
	   ELSE CONTRACT_VW2.ACTION_AMOUNT
	   END

The above CASE statement is invalid. Please go through CASE Statement

Secondly,
CREATE TABLE MyTest
(
col_1 VARCHAR2(10),
col_2 NUMBER
);

INSERT INTO MyTest VALUES('A', 10);
INSERT INTO MyTest VALUES('B', 20);

Select * from MyTest;

COL_1       COL_2
---------- ---------
A                 10
B                 20

SELECT COL_2 FROM 
         (SELECT COL_1 FROM MyTest);

SELECT COL_2 FROM
       *
ERROR at line 1:
ORA-00904: "COL_2": invalid identifier 


Hope you understood what went wrong with your query from the above example. Your INLINE View should return the columns you are using in the outer SELECT statement. Your INLINE View returns only one column and that too as mentioned has an invalid CASE Statement.

Finally,
Check the number of Columns in both your Select Statements with UNION ALL
SQL> SELECT col_1, col_2 FROM MyTest
  2  UNION ALL
  3  SELECT 'A', 20, NULL, NULL FROM Dual;
SELECT col_1, col_2 FROM MyTest
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns


Hope this helps.

Regards,
Jo
Re: help with CASE [message #352485 is a reply to message #352451] Wed, 08 October 2008 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joicejohn wrote on Tue, 07 October 2008 21:03
@aarti81,

First,
CASE WHEN CONTRACT_VW2.CONTRACT_TYPE='On Call' THEN ASSIGNMENT_PO_VW.WORK_ORDER_AMOUNT
	   ELSE CONTRACT_VW2.ACTION_AMOUNT
	   END

The above CASE statement is invalid. Please go through CASE Statement

Sorry, but I seem to be missing your point here. Why is his case-construct invalid?
  1  select empno
  2  ,      deptno
  3  ,      ename
  4  ,      case when ename = 'KING'
  5              then deptno
  6              else empno
  7         end
 11* from emp
SQL> /

     EMPNO     DEPTNO ENAME      CASEWHENENAME='KING'THENDEPTNOELSEEMPNOEND
---------- ---------- ---------- ------------------------------------------
      7369         20 SMITH                                            7369
      7499         30 ALLEN                                            7499
      7521         30 WARD                                             7521
      7566         20 JONES                                            7566
      7654         30 MARTIN                                           7654
      7698         30 BLAKE                                            7698
      7782         10 CLARK                                            7782
      7788         20 SCOTT                                            7788
      7839         10 KING                                               10
      7844         30 TURNER                                           7844
      7876         20 ADAMS                                            7876
      7900         30 JAMES                                            7900
      7902         20 FORD                                             7902
      7934         10 MILLER                                           7934

14 rows selected.

In addition to Jo's remark about the non-matching number of columns in your union:
The error might be caused by the fact that you have no privs on the primes_or_subs_vw object, or the firm_type_code is not a column or function in that object.

[Edit: Removed duplicate column, used for testing]

[Updated on: Wed, 08 October 2008 01:03]

Report message to a moderator

Re: help with CASE [message #352500 is a reply to message #352444] Wed, 08 October 2008 01:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And ,


CASE WHEN CONTRACT_VW2.CONTRACT_TYPE='On Call' THEN ASSIGNMENT_PO_VW.WORK_ORDER_AMOUNT
	   ELSE CONTRACT_VW2.ACTION_AMOUNT
	   END


in your sub query is missing an Alias . Especially when it is reference outside the sub query.

Thumbs Up
Rajuvan.

Re: help with CASE [message #352537 is a reply to message #352485] Wed, 08 October 2008 05:10 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Frank,
Thanks for correcting me. I misread OP's querry:
Quote:

CASE WHEN CONTRACT_VW2.CONTRACT_TYPE='On Call' THEN ASSIGNMENT_PO_VW.WORK_ORDER_AMOUNT
ELSE CONTRACT_VW2.ACTION_AMOUNT
END
FROM CONTRACT_VW2,ASSIGNMENT_PO_VW,PRIMES_AND_SUBS_VW


AS
Quote:

CASE WHEN CONTRACT_VW2.CONTRACT_TYPE='On Call' THEN ASSIGNMENT_PO_VW.WORK_ORDER_AMOUNT
ELSE CONTRACT_VW2.ACTION_AMOUNT
END
FROM CONTRACT_VW2.ASSIGNMENT_PO_VW,PRIMES_AND_SUBS_VW


I thought CONTRACT_VW2.CONTRACT_TYPE will give an Invalid identifier error as I thought the table CONTRACT_VW2 is not in the FROM clause. http://img2.mysmiley.net/imgs/smile/sick/sick0002.gif
I will be more careful next time. Thanks again Frank...
My apologies to OP.

Regards,
Jo
Previous Topic: update a field on a table depending on file created on sun server
Next Topic: Date Diff exclude weekends in SQL
Goto Forum:
  


Current Time: Sun Dec 11 04:15:30 CST 2016

Total time taken to generate the page: 0.07511 seconds