--0016e647195aa12756047bc84969
Content-Type: text/plain; charset=ISO-8859-1
On 12/28/09, Premila Devi <premiladevi.dayalan [at] agbnielsen.com> wrote:
>
> select sum(alq.quantity + alq.locked), category.id, alq.assetStatus.name
>
> from AssetLocationQuantity alq inner join alq.asset asset inner join
asset.assetModel model inner join model.category category
>
> group by category, alq.assetStatus.name;
>
> ERROR: syntax error at or near "group"
>
I think you've got several parts of the syntax wrong here. Looking at your
query, we cannot even figure out what are the structures of your tables and
what is it that you are trying to get out of your query. I am guessing that
'AssetLocationQuantity' is a table and 'alq' is an alias for this
table. This table has columns 'quantity', 'locked' and 'assetStatus'.
'Category' is another table, having column 'id'. But what are '
alq.assetStatus.name', 'alq.asset', 'asset', 'asset.assetModel',
'model.category'??? Its very confusing..
First of all, you *cannot* join columns. You have to join 2 tables 'on a
condition' involving columns of the 2 tables. And you can join only 2
tables at a time. If you want to join multiple tables, you need to
'cascade' the join by first joining a pair, considering the resultant as a
single table and join this resultant with the third table and so on.
Next, you should give a join condition (for each inner join) before the
'group by' clause. The general syntax should be something like this:
select ...
from T1 inner join T2 on T1.c1 = T2.c2
inner join T3 on T1.c1 = T3.c3
group by .....
Very importantly, the names appearing in the 'group by' list *cannot* be
table names. They *have to* be column names.
Next, each name specified in the 'select' and 'group by' clauses must be
unambiguous. ie if any column name appears in more than one table refered
to in the query, then the column name should be qualified (prepended) with
the table name in both the clauses.
And SQL is quite strict about this requirement: All the names appearing in
the 'group by' clause *should* appear in the 'select' clause also. And all
the names appearing in the 'select' clause that dont appear in 'group'
by, *have
to* be aggregate functions only. Else you will get a syntax error.
We might try to help you write the correct query if you tell us the
structure of your tables ie all the columns of all the tables. And also
try to tell us what you want your query to do.
And general tip: Whenever you get a syntax error, look for the error *
before* the point where the error marker is there.
All the best,
Shruthi
--0016e647195aa12756047bc84969
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
On 12/28/09, Premila Devi <<a href=3D"mailto:premiladevi.dayalan [at] agbniel=
sen.com">premiladevi.dayalan [at] agbnielsen.com</a>> wrote:<br>> <br>>=
select sum(alq.quantity + alq.locked), <a href=3D"http://category.id">cate=
gory.id</a>, <a href=3D"http://alq.assetStatus.name">alq.assetStatus.name</=
a><br>
> <br>> from AssetLocationQuantity alq inner join alq.asset asset inn=
er join asset.assetModel model inner join model.category category<br>> <=
br>> group by category, <a href=3D"http://alq.assetStatus.name">alq.asse=
tStatus.name</a>;<br>
> <br>> ERROR:=A0 syntax error at or near "group"<br>> <=
br><br>I think you've got several parts of the syntax wrong here.=A0=A0=
Looking at your query, we cannot even figure out what are the structures of=
your tables and what is it that you are trying to get out of your query.=
=A0=A0I am guessing that 'AssetLocationQuantity' is a table and =
9;alq' is an alias for this table.=A0=A0This table has columns 'qua=
ntity', 'locked' and 'assetStatus'.=A0=A0 'Category=
' is another table, having column 'id'.=A0=A0 But what are '=
;<a href=3D"http://alq.assetStatus.name">alq.assetStatus.name</a>',=A0=
=A0'alq.asset', 'asset', 'asset.assetModel', 'm=
odel.category'???=A0=A0 Its very confusing..<br>
<br>First of all, you <i>cannot</i> join columns.=A0=A0You have to join 2 t=
ables 'on a condition' involving columns of the 2 tables.=A0=A0 And=
you can join only 2 tables at a time.=A0=A0If you want to join multiple ta=
bles, you need to 'cascade' the join by first=A0=A0joining a pair, =
considering the resultant as a single table and join this resultant with th=
e third table and so on.<br>
<br>Next, you=A0should give a join=A0condition (for=A0=A0each inner join) b=
efore=A0the 'group by' clause.=A0=A0 The general syntax should be s=
omething like this:<br><br>select ...<br>from=A0=A0T1 inner join T2=A0=A0on=
T1.c1 =3D T2.c2<br>
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0inner join=A0=A0T3=A0=A0 on=A0=A0 T1.c1 =3D T=
3.c3<br>group by .....<br><br><br>Very importantly, the names appearing in =
the 'group by' list <i>cannot</i> be table names.=A0=A0They <i><str=
ong>have to</strong></i> be column names.<br>
<br>Next, each name specified in the 'select' and 'group by'=
; clauses must be unambiguous.=A0=A0ie if any column name appears in more t=
han one table refered to in the query, then the column name should be quali=
fied (prepended) with the table name in both the clauses.<br>
<br>And SQL is quite strict about this requirement:=A0=A0All the names appe=
aring in the 'group by' clause <i>should</i> appear in the 'sel=
ect' clause also.=A0=A0 And all the names appearing in the 'select&=
#39; clause that dont appear in 'group' by, <i><strong>have to</str=
ong></i> be aggregate functions only.=A0=A0Else you will get a syntax error=
..<br>
<br>We might try to help you write the correct query if you tell us the str=
ucture of your tables ie all the columns of all the tables.=A0=A0 And also =
try to tell us what you want your query to do.<br><br>And general tip: =A0W=
henever you get a syntax error, look for the error <i><strong>before</stron=
g></i> the point where the error marker is there.<br>
<br><br>All the best,<br>Shruthi<br>
--0016e647195aa12756047bc84969--
