Re: ERROR: syntax error at or near "group"

Please chek again and inform me.thanx for the information.

On 12/28/09, Shruthi A <shruthi(dot)iisc(at)gmail(dot)com> wrote:
> On 12/28/09, Premila Devi <premiladevi(dot)dayalan(at)agbnielsen(dot)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
>

--
$älmân