Query Error

[lloraine]

I am getting the following error in Enterprise Guide 4.1 NOTE: The query requires remerging summary statistics back with the original data. ERROR: ORACLE execute error: ORA-01652: unable to extend temp segment by 128 in tablespace TMP_01. NOTE: Table WORK.QUERY_FOR_V_MKTDEC_PROVISIONS created, with 0 rows and 13 columns. I am using this code to create a computed column MAX(CASE When V_MKTDEC_PROVISIONS.LINE_ORDER = 1 Then V_MKTDEC_PROVISIONS.PROVISION_DETAIL ELSE " " END) I am only testing on 1 record but it doesn't bring back anything.  This is a hugh table that I am trying to query.

[ChrisHemedinger]

See this SAS Note about the NOTE.

http://support.sas.com/kb/4/308.html

Does your query include any grouping/aggregation?  You can get this message if you have SELECTed additional variables that are not included in the aggregations that you asked for.

Chris

[lloraine]

I have figured out everything but now I am having a problem with filtering on dates. I want to use the following: Eff_From_Dt=intnx('day',date(),0) This code "intnx('day',date(),0" gives me the current date. When I use the above code, I do not get results.  When I take the filtering out, I get results.  I tried formatting the from and to date to MMDDYYS10.0 but I got ***** for the from date.  It is probably something simple that in my ignorance of Sas, I am missing.

[MartyS]

Temporary tablespaces are used by Oracle as scratchpads during a query.  They can overflow and cause the error you are seeing.  Causes include many things, but the most common reason for end users to see the error is that a large sort is taking place.  Sorts are usually the result of ORDER BY or GROUP BY clauses.

The error can also happen for other reasons, such as index creation or because too many transactions are competing for temp space, but the most common culprit is a large sort.

Glad you got it worked out.

Marty