Archives of the TeradataForum

Message Posted: Tue, 03 Jun 2008 @ 10:30:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

When try to use a WITH clause in Select query in a bteq for inserting stage table, I am getting the below error:

     *** Failure 3706 Syntax error: WITH is not allowed in either
               a subquery or a view.
               Statement# 1, Info =1242
     *** Total elapsed time was 1 second.

The query I have used is

     INSERT INTO GL_SEGMNT3_DHS_temp1
     (
     parent_value
     ,child_value
     ,range_attribute
     ,last_update_login
     ,src_sys_id
     ,created_by
     ,creation_date
     ,last_updated_by
     ,src_nam
     ,dat_orgn
     ,postng_agnt
     ,ods_postng_ts
     ,ods_upd_ts
     ,last_update_date
     )


     SELECT
     a.parent_flex_value,
     b.flex_value,
     a.range_attribute,
     a.last_update_login,
     b.flex_value_set_id||'~'
          ||a.parent_flex_value||'~'
          ||trim(substr(b.flex_value_id,1,index(b.flex_value_id,'.')-1)),
     a.src_cretn_id,
     a.src_cretn_ts,
     a.src_upd_id,
     a.src_nam,
     'APPLSYS.FND_FLEX_VALUE_NORM_HIERARCHY,FND_FLEX_VAL',
     'BTQ_OFA_060_dTI_SSTG_GLPSEG3DHS',
     CURRENT_TIMESTAMP(0),
     CURRENT_TIMESTAMP(0)

     FROM   FDS_ETL_TARGET. GL_GLP_FND_FLX_VAL_NORM_HIER_S  a
            ,FDS_ETL_TARGE.GL_GLP_FND_FLEX_VAL_S          b

     WHERE   a.flex_value_set_id     in      (:VAR_FLEX_VAL_SET_ID)

     AND     a.flex_value_set_id     = b.flex_value_set_id
     AND     b.flex_value BETWEEN a.child_flex_value_low
                             AND a.child_flex_value_high

     AND     a.range_attribute =
              (
              CASE
                      WHEN b.summary_flag = 'Y'
                      THEN 'P'
                      ELSE 'C'
              END
              )
     WITH MAX(a.src_upd_ts) BY a.parent_flex_value,b.flex_value;

     *** Failure 3706 Syntax error: WITH is not allowed in either
               a subquery or a view.
               Statement# 1, Info =1242
     *** Total elapsed time was 1 second.

Can Someone explain me why I am getting this error.

Thanks

Sridhar.S

	<Prev	Next>		<<First	<Prev	Next>	Last>>