Re: Strange error - ORA-00928: missing SELECT keyword

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 <carl_at_fireword-computing.co.uk> wrote in message news:<3B7CE7E9.86C3F3FC_at_fireword-computing.co.uk>...
> 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 wrote:
>
> > 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
Received on Fri Aug 17 2001 - 10:05:32 CDT