You're right, Oracle supports the "AS" keyword as of version 7.1. I guess I'm
showing my age ;)
Anyhow, I was having a problem with the column not aliasing properly. When I
looked at the SQL I assumed it was due to the additional "AS" keyword. The
problem was actually just a result of using the wrong case in my aliases. It
should have been "TOTAL" not "total", to match what was in my datamap.
- Dan
Quoting Andrus Adamchik <andru..bjectstyle.org>:
> Hi Dan,
>
> I tried a similar query and it works for me on Oracle 9.2:
>
> [oracl..oombox oracle]$ sqlplus andrus@cnora
> SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jun 17 14:04:30 2004
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> select count(1) AS TOTAL from ARTIST;
>
> TOTAL
> ----------
> 10
>
>
> The unit tests with aliases pass as well... What version of Oracle are
> you using? If "AS" is indeed a problem with older Oracle, please open a
> bug report in JIRA - we'll implement Velocity directive customization
> per DB adapter.
>
> Andrus
>
>
>
> On Jun 17, 2004, at 12:16 PM, dan17..elus.net wrote:
>
> > I'm using SQLTemplate to generate aggregated values.
> >
> > This is my sql statement:
> >
> > String sql = "select " +
> > "#result('eez.entity_id' 'Integer')," +
> > "#result('p.yearmonth' 'Integer'), " +
> > "#result('sum(p.value)' 'BigDecimal' 'total')" +
> > " from economic_entity_zone eez, production p" +
> > " where (eez.zone_id = p.zone_id) and (eez.entity_id =
> > #bind($helper.cayenneExp
> > ($entity, 'db:ENTITY_ID')))" +
> > " group by eez.entity_id, p.yearmonth";
> >
> > When this is sent to oracle. I get:
> >
> > select eez.entity_id, p.yearmonth, sum(p.value) AS total
> > from economic_entity_zone eez, production p
> > where (eez.zone_id = p.zone_id) and (eez.entity_id = 1234)
> > group by eez.entity_id, p.yearmonth
> >
> > In Oracle SQL, you don't use the keyword "AS" when aliasing a column.
> > The SQL
> > should read:
> >
> > select eez.entity_id, p.yearmonth, sum(p.value) total from [...]
> >
> > How do I suppress the "AS"?
> >
> > - Dan
>
>
This archive was generated by hypermail 2.0.0 : Thu Jun 17 2004 - 15:18:53 EDT