Archives of the TeradataForum

Message Posted: Wed, 20 Feb 2008 @ 16:55:14 GMT

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

<-- Anonymously Posted: Wed, 20 Feb 2008 11:15 -->

The first query breaks with an error message of 3637.

Some examples of similar queries that perform correctly are attached.

(IMHO, the rewrites seem trivial; why do they make a difference.)

Any thoughts why the first version breaks?

Maybe the question should be: why do versions 2 and 3 execute correctly?

Thank-you in advance for your thoughts on this matter.

     database db2345;

     /** fails with 3637 ***/

     select
         'Oct 2002 - Sep 2003',
         0,
         srvcctgryinf.m_svc_cat_cd_desc,
         sum(f.chg_allow_amt),
         sum(f.mdst_qty_svcs_cnt),
         count(distinct f.person_prov_date)
     from svc_cat as srvcctgryinf,
           epi_prof_clm as f,
           clm_general as claimgeneral
     where claimgeneral.mdst_claim_type_cd = 1
        and f.claim_general_key = claimgeneral.claim_general_key
        and f.svc_cat_key = srvcctgryinf.svc_cat_key
        and f.period_inc_key between 8310 and 8674
     group by 1
                  , srvcctgryinf.m_svc_cat_cd_desc
                  , srvcctgryinf.m_svc_cat_cd_desc
     order by 1 asc, 3 asc ;


     /****
     Results in error #3637:

     3637 Invalid ORDER BY constant.

     Explanation: An ORDER BY constant does not represent
     the position of a selected expression, or the selected
     expression represented is itself a constant.

     Generated By: RES and OPT modules.

     For Whom: End User.

     Remedy: Change ORDER BY phrase.


     The statements below succeed, producing the same answerset:

     (approach: alias the constant)

     ****/



     /** queries below succeed ***/

     select 'Oct 2002 - Sep 2003' as rolling_qtr, 0,
         srvcctgryinf.m_svc_cat_cd_desc,
         sum(f.chg_allow_amt),
         sum(f.mdst_qty_svcs_cnt),
         count(distinct f.person_prov_date)
     from svc_cat as srvcctgryinf,
           epi_prof_clm as f,
           clm_general as claimgeneral
     where claimgeneral.mdst_claim_type_cd = 1
        and f.claim_general_key = claimgeneral.claim_general_key
        and f.svc_cat_key = srvcctgryinf.svc_cat_key
        and f.period_inc_key between 8310 and 8674
     group by 1
                  , srvcctgryinf.m_svc_cat_cd_desc
                  , srvcctgryinf.m_svc_cat_cd_desc
     order by 1 asc, 3 asc ;


     /***
     (approach: remove the constant from the ORDER BY clause)
     ***/

     select 'Oct 2002 - Sep 2003', 0,
         srvcctgryinf.m_svc_cat_cd_desc,
         sum(f.chg_allow_amt),
         sum(f.mdst_qty_svcs_cnt),
         count(distinct f.person_prov_date)
     from svc_cat as srvcctgryinf,
           epi_prof_clm as f,
           clm_general as claimgeneral
     where claimgeneral.mdst_claim_type_cd = 1
        and f.claim_general_key = claimgeneral.claim_general_key
        and f.svc_cat_key = srvcctgryinf.svc_cat_key
        and f.period_inc_key between 8310 and 8674
     group by 1
                  , srvcctgryinf.m_svc_cat_cd_desc
                  , srvcctgryinf.m_svc_cat_cd_desc
     order by 3 asc ;

Thanks

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