create table agl_spell_gr7 as
select * from(
WITH cte1 AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Meter_id ORDER BY Realtimeclock) rn
FROM loadsurvey t
),
cte2 AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Meter_id ORDER BY Realtimeclock) rn
FROM loadsurvey t
WHERE I_R <> 0 and I_Y <> 0 and I_B <> 0
),
cte3 AS (
SELECT t1.*,
t1.rn - t2.rn AS diff
FROM cte1 t1
INNER JOIN cte2 t2
ON t1.Meter_id = t2.Meter_id AND t1.Realtimeclock = t2.Realtimeclock
)
SELECT
Meter_id,
MIN(Realtimeclock) AS start_time,
MAX(Realtimeclock) AS end_time,
COUNT(I_Y) AS I_Y,
COUNT(I_B) AS I_B,
COUNT(I_R) AS I_R, ROW_NUMBER() OVER (PARTITION BY Meter_id ORDER BY Meter_id ) AS Spell,
sum(KWH) as KWH,
sum(KWPH) as KWPH
FROM cte3
GROUP BY
Meter_id,
diff);
Hi all,
I am getting the below error while creating table from the above code.
Please guide me how to proceed with table creation. [Error] Execution (28: 6): ORA-00600: internal error code, arguments: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []
ORA-00600 is Oracle's error number for unhandled exceptions i.e. bugs. The nature of ORA-00600 errors is that they are generally edge cases, hurled by certain code constructs running against particular database versions on specific OS platforms. Consequently, they usually require patches to fix them. However, as you appear to using an obsolete version of Oracle (10g but also 11g??) I guess it's unlikely you work for an organisation with a Support contract and/or an interest in keeping databases in a patched state.
In this case it might not matter. Google suggests this internal error is sometimes thrown by CREATE TABLE AS SELECT statements. You have a doozy of a CTAS statement, with a bunch of analytic functions. So you may be able to resolve this problem simply by have a standalone CREATE TABLE statement and populating the table with a separate INSERT statement.