Automatically adding GROUP BY

rbuser

Hello,

I'm running through an error regarding to group by with auto generated
SQL.
I know I have to post more details for you to help me, but for now I would
like to know if there's any docs that explains what are the rules to DADE
add fields on GROUP BY clause and, if possible, some tips regarding to this.
I'm trying to understand what is happening yet. I have a little complex
query:

--auto generated it is

SELECT CLIENTE_1.NOME, CLIENTE_1.NOMECONJUJE,
TIPOCONTRATO_1.TIPO, CLIENTE_1.SEXO,
PROFISSAO_1.NOME NOME_2, CLIENTE_1.RG,
CLIENTE_1.RGCONJUJE, CLIENTE_1.CPF,
CLIENTE_1.CPFCONJUJE,
FINANCIAMENTO_1.ENDERECO,
FINANCIAMENTO_1.NUMERO,
FINANCIAMENTO_1.COMPLEMENTO,
FINANCIAMENTO_1.BAIRRO,
FINANCIAMENTO_1.CEP,
FINANCIAMENTO_1.IDESTADO,
CLIENTE_1.IDCIDADE, CIDADE_1.NOME NOME_3,
ESTADO_1.SIGLA,
(SELECT NOME FROM Profissao P WHERE
IdProfissao=CLIENTE_1.IdProfissaoConjuje) SELECT_NOME_FROM_Profiss,
(SELECT SUM(Valor) FROM ParcelaContrato WHERE
IdContratoFinanciamento=CONTRATOFINANCIAMENTO_1.IdContratoFinanciamento)
SELECT_SUM_Valor_FROM_Pa
FROM CONTRATOFINANCIAMENTO CONTRATOFINANCIAMENTO_1
INNER JOIN FINANCIAMENTO FINANCIAMENTO_1 ON
(FINANCIAMENTO_1.IDFINANCIAMENTO =
CONTRATOFINANCIAMENTO_1.IDFINANCIAMENTO)
INNER JOIN CLIENTE CLIENTE_1 ON
(CLIENTE_1.IDCLIENTE = FINANCIAMENTO_1.IDCLIENTE)
INNER JOIN TIPOACAO TIPOACAO_1 ON
(TIPOACAO_1.IDTIPOACAO = CONTRATOFINANCIAMENTO_1.IDTIPOACAO)
INNER JOIN TIPOCONTRATO TIPOCONTRATO_1 ON
(TIPOCONTRATO_1.IDTIPOCONTRATO =
CONTRATOFINANCIAMENTO_1.IDTIPOCONTRATO)
INNER JOIN PROFISSAO PROFISSAO_1 ON
(PROFISSAO_1.IDPROFISSAO = CLIENTE_1.IDPROFISSAO)
INNER JOIN CIDADE CIDADE_1 ON
(CIDADE_1.IDCIDADE = CLIENTE_1.IDCIDADE)
INNER JOIN ESTADO ESTADO_1 ON
(ESTADO_1.IDESTADO = CLIENTE_1.IDESTADO)
WHERE
( CONTRATOFINANCIAMENTO_1.IDCONTRATOFINANCIAMENTO = 174531 )

--

The issue is that when I add one more field it generates all this group
by:

GROUP BY CLIENTE_1.NOME, CLIENTE_1.NOMECONJUJE,
TIPOCONTRATO_1.TIPO, CLIENTE_1.SEXO,
PROFISSAO_1.NOME, CLIENTE_1.RG,
CLIENTE_1.RGCONJUJE, CLIENTE_1.CPF,
CLIENTE_1.CPFCONJUJE,
FINANCIAMENTO_1.ENDERECO,
FINANCIAMENTO_1.NUMERO,
FINANCIAMENTO_1.COMPLEMENTO,
FINANCIAMENTO_1.BAIRRO,
FINANCIAMENTO_1.CEP,
FINANCIAMENTO_1.IDESTADO,
CLIENTE_1.IDCIDADE, CIDADE_1.NOME,
ESTADO_1.SIGLA,
FINANCIAMENTO_1.VALORCONTRATUALMENSALIDADE

But see, the group by is completely unnecessary because the new field is in
FINANCIAMENTO_1 table and there is no calculations that require any group
by. If I take the first query and just add the new field, the query is
perfectly valid.

Thanks
Ricardo

nardmoseley

A SQL Group By clause must contain all fields from the Select clause, except
aggregate calculations (i.e. Sum(), Min(), etc).

If you are not performing any aggregate calculations, use Select Distinct
rather than Group By.

Dade is designed to generate ANSI Standard SQL - which is supported by most
database engines. The DataSettings.SQLType and DatabaseType properties can
be used to control the exact syntax. The Group By limitation is defined in
the ANSI Standard and most database engines enforce the same rule.

rbuser

That's what is puzzling me.
I don't do any calculation, just simple fields and two subqueries. But what
is really puzzling me is the fact that no group by is added until the point
shown below (first query). But when I add one simple field to it DADE
generates all that group by.
Even id distinct is checked, all the groupBy are added. And as you can see
in the SQL, no calculation involved.

thanks
Ricardo