Strange error - ORA-00928: missing SELECT keyword

[Dan]

I got the following error saying missing SELECT. But it has select and
the SQL seems OK with me. What could be wrong? Is this some sort of
bug? I use Oracle 8.1.6.0. Thanks.

--------------------------------------------------------------------

SQLException information

Exception 1
Message :ORA-00928: missing SELECT keyword
SQLState :42000
SQL Error code :928
Exception 2
Message :ORA-00928: missing SELECT keyword
SQLState :42000
SQL Error code :928
Statement
ID : 1187
Interruptible? : NO
Last SQL :

CREATE TABLE TTMMPP_517909_95 TABLESPACE MART_DATA NOLOGGING PARALLEL
1 PCTFREE 0 PCTUSED 40 AS SELECT
NVL(DimRow0,NULL) DimRow0,
NVL(DimColumn0,NULL) DimColumn0,
NVL((COUNT( DISTINCT CASE WHEN F0_DCNT_1 <> 0 THEN F0_DCNT_1_C ELSE
NULL END)),(0)) F0_DCNT_1,
0 g0,
0 g1

FROM (
SELECT /*+use_hash(T0,T1,T2)*/
NVL(T1.Business_Type,NULL) DimRow0,
NVL(T1.Account_Status,NULL) DimColumn0,
/*COUNT DISTINCT T1.Account_Number BOOK*/ (SUM (DECODE((
T0.transtype_key ),( 1 ),( 1) , (NULL) ))) F0_DCNT_1,
T1.Account_Number F0_DCNT_1_C
FROM
CALL_0_A T0,
CUSTOMER_0_B T1,
DATE_0 T2
WHERE
T0.group_key = T1.customer_key
AND T0.date_key = T2.date_key
AND (T2.cy_name IN ('2001'))
AND T0.transtype_key IN (1)
GROUP BY
T1.Business_Type,
T1.Account_Status,
T1.Account_Number
HAVING SUM(T0.Existence) <> 0
) T GROUP BY
DimRow0,
DimColumn0

[Carl Hathaway]

Is the following valid syntax?

DISTINCT CASE WHEN F0_DCNT_1 <> 0 THEN F0_DCNT_1_C ELSE
NULL END)),(0)) F0_DCNT_1,

[Dan]

Carl,

Thanks for the reply. Yes, the syntax is right. I tried just taking
out "AND T0.date_key = T2.date_key" from the WHERE clause, and it
worked. In fact, I tried different SQL with the same tables, as long
as I didn't include any column other than customer_key from my
CUSTOMER_0_B (T1) table in WHERE clause, it seemed fine.

Dan

Carl Hathaway <ca...@fireword-computing.co.uk> wrote in message news:<3B7CE7E9...@fireword-computing.co.uk>...