Home » SQL & PL/SQL » SQL & PL/SQL » ora 00913
ora 00913 [message #244712] Wed, 13 June 2007 21:08 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
The following query is returning error ORA-00913: too many values
could somebody explain what might be the reason

select to_char(record_date,'YYYY-MM-DD') as "name",
decode('U','U','Upgrade','D','Downgrade') as "direction",
'ALL' as "portfolio_id"
,
avg(total_mv) as "marketValue",
avg(total_sv) as "statValue",
avg(total_gv) as "gaapValue",
avg(total_par) as "par",
count(*) as "naic_shifts",

(
select ccmsectype as "name",
decode('U','U','Upgrade','D','Downgrade') as "direction",
sum(marketvalue) as "marketValue",
sum(statvalue) as "statValue",
sum(gaapvalue) as "gaapValue",
sum(par) as "par",
count(*) as "naic_shifts"
from naic_up_down_view inner where
outer.record_date = inner.record_date and
(
(
(DECODE ('U','U',1,0)=1 AND ORG_NAIC>CURRENT_NAIC)
OR (DECODE('U', 'D' , 1, 0) = 1 AND ORG_NAIC < CURRENT_NAIC)
)

) group by ccmsectype

)

from naic_up_down_view outer
where
(
( decode ('U','U',1,0)=1 and org_naic>current_naic) OR
( decode ('U','D',1,0)=1 and org_naic<current_naic)
)
group by record_date
Re: ora 00913 [message #244714 is a reply to message #244712] Wed, 13 June 2007 21:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Are you unwilling or incapable of following the posting guidelines enumerated in the #1 Sticky post at the top of this forum?

The unformatted posted code is next to impossible to debug & I won't waste my time trying to do so.

You're On Your Own (YOYO)!

Re: ora 00913 [message #244759 is a reply to message #244712] Thu, 14 June 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I'm in a kind way this morning, I will post you the links:
How to format your posts
How to get a quick answer to your question: TIPS AND TRICKS

And always post your Oracle version (4 decimals).
And search for "too many values".

Regards
Michel

Re: ora 00913 [message #244888 is a reply to message #244759] Thu, 14 June 2007 08:32 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
sorry about that posting it again in the code tags.


select to_char(record_date,'YYYY-MM-DD') as "name",
decode('U','U','Upgrade','D','Downgrade') as "direction",
'ALL' as "portfolio_id"
,
avg(total_mv) as "marketValue",
avg(total_sv) as "statValue",
avg(total_gv) as "gaapValue",
avg(total_par) as "par",
count(*) as "naic_shifts",

(
    select ccmsectype as "name",
    decode('U','U','Upgrade','D','Downgrade') as "direction",
		sum(marketvalue) as "marketValue",
		sum(statvalue) as "statValue",
		sum(gaapvalue) as "gaapValue",
		sum(par) as "par",
		count(*) as "naic_shifts"
		from naic_up_down_view inner where 
		outer.record_date = inner.record_date and 
		( 
       ( 
          (DECODE ('U','U',1,0)=1 AND ORG_NAIC>CURRENT_NAIC)
          OR (DECODE('U', 'D' , 1, 0) = 1 AND ORG_NAIC < CURRENT_NAIC)   
        )

			)	group by ccmsectype
     
)

from  naic_up_down_view outer 
where
( 
    ( decode ('U','U',1,0)=1 and org_naic>current_naic) OR 
		( decode ('U','D',1,0)=1 and org_naic<current_naic)   
) 
group by record_date




Here I ahve tried to merge the two queries but its not gonna work since in the inner one returns multiple fields, I have not been able to figure out how I can merge the two in a neat working way.
Re: ora 00913 [message #245052 is a reply to message #244888] Fri, 15 June 2007 01:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Your 'INNER' select (btw, avoid Oracle keywords as alias: INNER and OUTER are keywords as they are used in ANSI joins) can only return ONE column.

MHE
Re: ora 00913 [message #245060 is a reply to message #244888] Fri, 15 June 2007 01:35 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
8. Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
9. Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
10. Provide your expected result set.

As you did not provide anything from the above list and I have no idea about the result you expect, I may just guess the solution you need.
UNION ALL for row union.
FULL OUTER JOIN using name(?) for column join.
Previous Topic: Hello, how to get last week DAY from sysdate
Next Topic: Getting some wierd compile errors that i can't solve......Please Help
Goto Forum:
  


Current Time: Sat Dec 03 07:45:31 CST 2016

Total time taken to generate the page: 0.11031 seconds