Home » SQL & PL/SQL » SQL & PL/SQL » Spooling Output Issue (Oracle 10G on Red Hat Linux)
Spooling Output Issue [message #362332] Mon, 01 December 2008 19:40 Go to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
Hi.

I am spooling some records from a table to output in a comma-delimited format. I've done this many times before but am having a problem trying to figure why I am getting ORA-01785 error for this particular statement. The code will execute just fine like this:

 SELECT SUM(LIMITED_EVAL.FT_329) ||','|| SUM(LIMITED_EVAL.FT_328)
	||','|| LIMITED_EVAL.DimRow0
	||','|| 0
	||','|| LIMITED_EVAL.DimRow1
	||','|| 0 
	||','|| LIMITED_EVAL.DimColumn0
	||','|| MIN(LIMITED_EVAL.DimColumn0_SORTER)
	||','|| 0
	||','|| CASE WHEN 1=2 THEN 1 ELSE NULL END
	||','|| CASE WHEN 1=2 THEN 1 ELSE NULL END
   FROM TTMMPP_TABLE LIMITED_EVAL
	GROUP BY LIMITED_EVAL.DimRow0,
        LIMITED_EVAL.DimRow1,
        LIMITED_EVAL.DimColumn0
	ORDER BY 
		MIN(LIMITED_EVAL.DimColumn0_SORTER);

But it bombs out on me when attempting to add the column header fields: 

SELECT 'SUM1,'
||'SUM2,'
||'COUNTRY,'
||'gDimRow0,'
||'DimRow1,'
||'gDimRow1,'
||'MonthYear,'
||'DayMonthYear,'
||'gDimColumn0,'
||'currency_key,'
||'num_currencies' " "
FROM DUAL
UNION ALL
   SELECT SUM(LIMITED_EVAL.FT_329) ||','|| SUM(LIMITED_EVAL.FT_328)
	||','|| LIMITED_EVAL.DimRow0
	||','|| 0
	||','|| LIMITED_EVAL.DimRow1
	||','|| 0 
	||','|| LIMITED_EVAL.DimColumn0
	||','|| MIN(LIMITED_EVAL.DimColumn0_SORTER)
	||','|| 0
	||','|| CASE WHEN 1=2 THEN 1 ELSE NULL END
	||','|| CASE WHEN 1=2 THEN 1 ELSE NULL END
   FROM TTMMPP_5417387_666 LIMITED_EVAL
	GROUP BY LIMITED_EVAL.DimRow0,
        LIMITED_EVAL.DimRow1,
        LIMITED_EVAL.DimColumn0
	ORDER BY 
		MIN(LIMITED_EVAL.DimColumn0_SORTER);


Oddly enough, if I remove the ORDER BY clause the entire script executes without a problem. Confused Can anyone advise as to what needs to be changed in order to keep the ORDER BY clause? I really need this data sorted.

Any help is appreciated. Smile

thx
Re: Spooling Output Issue [message #362333 is a reply to message #362332] Mon, 01 December 2008 20:30 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ORA-01785: ORDER BY item must be the number of a SELECT-list expression


You order by a "MIN(LIMITED_EVAL.DimColumn0_SORTER)".

This order is applied over all the unions. In the first part of the union there is no "MIN(LIMITED_EVAL.DimColumn0_SORTER)" in the select-list expression.

You need to create an identical column to order by in both selects.

Something like :

select data_col from (
   select -100 order_col, 
          'Sum1....
           ...' data_col
   union all
   select SUM(LIMITED_EVAL.FT_329) order_col,
          SUM(LIMITED_EVAL.FT_329) ||','|| ...
          ... data_col
) order by order_col;



Previous Topic: Items validation
Next Topic: help requred
Goto Forum:
  


Current Time: Thu Dec 08 14:25:43 CST 2016

Total time taken to generate the page: 0.09473 seconds