Home » SQL & PL/SQL » SQL & PL/SQL » query giving error of spool space
( ) 1 Vote
| query giving error of spool space [message #241231] |
Mon, 28 May 2007 23:32  |
iram_11181
Messages: 3
Registered: May 2007
|
Junior Member |
|
|
create volatile table med_call_usage as
(
Select
substr(trim(med.del_no),1,11)del_no,
substr(trim(city_code),1,5)city_code,
substr(trim(city),1,10)city,
substr(trim(med.circle_code),1,5)circle_code,
substr(trim(med.circle),1,10)circle,
substr(trim(med.call_type_code),1,5)call_type_code,
substr(trim(med.call_service_type_code),1,5)call_service_type_code,
substr(trim(med.call_distance_band_code),1,5)call_distance_band_code,
substr(trim(med.outgoing_trunk_id),1,8)outgoing_trunk_id,
substr(trim(med.call_switch_id),1,8)call_switch_id,
substr(trim(mou),1,8)mou,
trim(Pulses)Pulses,
trim(calls)calls,
trim(call_start_date)call_start_date,
substr(trim(cu.CALL_DIRECTION),1,10)CALL_DIRECTION,
cu.CALL_USAGE_TYPE_DESC,
cu.CALL_DISTANCE_BAND_DESC,
cu.TERMINATING_OPERATOR_TYPE,
CASE
WHEN TRIM(med.OUTGOING_OPERATOR) = 'TT_F'
AND TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'FIXED'
THEN 'AIRTEL_FIXED'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'GSM' AND
TRIM(med.OUTGOING_OPERATOR) = 'AIRTEL_GSM' THEN 'AIRTEL_GSM'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'FIXED' THEN 'OTHERS_FIXED'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'GSM' THEN 'OTHERS_GSM'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'WLL' THEN 'WLL'
ELSE 'OTHERS'
END "OPER_NAME"
from mediation_det med
left join b_usersharedb.callUsageCode cuc
on med.call_type_code = cuc.call_type_code
and med.call_service_type_code = cuc.call_service_type_code
and
(( case when trim(med.call_distance_band_code) = 'NF' then '0'
else trim(med.call_distance_band_code) end ) = coalesce(trim(cuc.call_distance_band_code) ,'0')
or ( cuc.call_distance_band_code is null and not exists ( sel 1 from b_usersharedb.callUsageCode cuc1
where cuc1.call_type_code = med.call_type_code
and cuc1.call_service_type_code = med.call_service_type_code
and cuc1.call_distance_band_code = med.call_distance_band_code) ))
inner join bp_viagg.call_usage cu
on cuc.call_code = cu.call_code
) with data on commit preserve rows ;
|
|
|
|
|
|
| Re: query giving error of spool space [message #241256 is a reply to message #241231] |
Tue, 29 May 2007 00:31  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| reported message | Reported By: iram_11181 On: Tue, 29 May 2007 06:47 In: SQL & PL/SQL » SQL Experts » query giving error of spool space
Reason as this is a very critical query and i have to run thi son daily basis.But till now i have recieved nio response.
|
Reporting messages is meant to report abusive messages, spam, etc. NOT to get attention.
Please don't do that.
|
|
|
|
| Re: query giving error of spool space [message #241886 is a reply to message #241231] |
Wed, 30 May 2007 18:46 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
You need to post the complete exact error message that you are getting and all of the commands that you are running when this happens. It sounds like you are probably running the spool command before your sql statement and have run out of space to spool to. If you run "spool off", then run your sql statement, do you still got the same error? It has probably run out of space to spew out error messages, because "create VOLATILE table ..." is not valid Oracle SQL syntax. Since you are using "on commit preserve rows", I am going to guess that maybe you are looking for "CREATE GLOBAL TEMPORARY TABLE ...".
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 08:24:25 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
