Home » SQL & PL/SQL » SQL & PL/SQL » Problem with query having cast function (merged)
Problem with query having cast function (merged) [message #364408] Mon, 08 December 2008 05:34 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

I Had a problem with below query with cast function.


SQL>  SELECT
  2    CAST ( ( ( (COUNT (
  3    CASE
  4      WHEN ServiceRequest_ec.WasCaseClosed = 1
  5      THEN 1
  6    END) )* 100) / (COUNT (DISTINCT ServiceRequest_ec.case_id)) ) AS nvarchar) AS Average
  7     FROM ServiceRequest_ec
  8  INNER JOIN cases_ec
  9       ON ServiceRequest_ec.case_id = cases_ec.case_id
 10  INNER JOIN contacts_ec
 11       ON ServiceRequest_ec.contact_id = contacts_ec.contact_id
 12  INNER JOIN users_ec
 13       ON contacts_ec.user_id = users_ec.user_id
 14  INNER JOIN departments_ec
 15       ON contacts_ec.department_id = departments_ec.department_id
 16    WHERE 1                         = 1;
  END) )* 100) / (COUNT (DISTINCT ServiceRequest_ec.case_id)) ) AS nvarchar) AS Average
                                                                   *
ERROR at line 6:
ORA-00902: invalid datatype 


SQL> spool off



Any help reallly appreciated.

Thanks in advance
Re: Problem with query having cast function [message #364412 is a reply to message #364408] Mon, 08 December 2008 05:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
Quote:
ORA-00902: invalid datatype

Is not this message self-explanatory? There is no NVARCHAR built-in data type in Oracle (at least from 10g - by the way, what is your Oracle version?). How did you find this data type? Did you create it on your own? If so, how?

[Edit: Added Oracle version remark]

[Updated on: Mon, 08 December 2008 05:49]

Report message to a moderator

Re: Problem with query having cast function [message #364417 is a reply to message #364412] Mon, 08 December 2008 05:58 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

I tried with nvarchar2. But doesnt worked for me.

Here below is the oracle version

Oracle 10g R2


Thanks
Re: Problem with query having cast function (merged) [message #364421 is a reply to message #364408] Mon, 08 December 2008 06:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
But doesnt worked for me.

Then you are in a trouble. I never faced this "not work" message. But yesterday my friends said in the pub, that this may happen and that it is caused by issuing improper SQL statements to Oracle. After their critical amount, it gets too overfed with them (or maybe starts striking) with "not work" message.

Maybe you face another error message/unexpected result, but then I wonder why would you not post it similarly as in your initial post. As NVARCHAR2 is Oracle built-in data type, I do not see there any problem (except division by zero for empty data), at least from a quick glance.
Re: Problem with query having cast function [message #364422 is a reply to message #364417] Mon, 08 December 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I post the following, maybe it will help to find the reason:
SQL> create table t (c nvarchar2);
create table t (c nvarchar2)
                           *
ERROR at line 1:
ORA-00906: missing left parenthesis

Regards
Michel
Re: Problem with query having cast function [message #364426 is a reply to message #364417] Mon, 08 December 2008 06:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I tried with nvarchar2. But doesnt worked for me.


No, it won't have done.
But it will have given you a different error message (that you also didn't bother to tell us)
Your original message was Invalid Datatype
The error message when you used NVARCHAR2 would have been 'Missing Left Parenthesis'

Try this (and next time think 'If I were knowledable about Oracle, what information might I want in order to fix a problem':
SELECT
    CAST ( ( ( (COUNT (
    CASE
      WHEN ServiceRequest_ec.WasCaseClosed = 1
      THEN 1
    END) )* 100) / (COUNT (DISTINCT ServiceRequest_ec.case_id)) ) AS nvarchar2(30)) AS Average
     FROM (select level case_id
                 ,mod(level,3) wascaseclosed
           from   dual
           connect by level <=20) servicerequest_ec;


That shows you that the principle works (although you would be better of using TO_NCHAR as that would let you provide an explicit format mask.)
Now just plumb your query in and away you go.
Re: Problem with query having cast function [message #364429 is a reply to message #364426] Mon, 08 December 2008 07:37 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hey thanks for your solution.
Previous Topic: SMTP permanent error: 500 #5.5.1
Next Topic: cartesian join with correlated queries
Goto Forum:
  


Current Time: Sat Dec 03 16:27:38 CST 2016

Total time taken to generate the page: 0.08702 seconds