Failed query in custom view(v9)

oliveira · Post by **oliveira** » Tue Aug 20, 2013 9:52 am

SELECT (Period_Diff(Date_Format(CurDate(), '%y%m'), Date_Format(parcelas.data,
'%y%m')) - parcelas.prestacoes_pagas) AS num_meses_atrasados,
Period_Diff(Date_Format(CurDate(), '%y%m'), Date_Format(parcelas.data,
'%y%m')) AS num_meses,
cliente.endereco AS endereco,
cliente.cpf AS cpf,
parcelas.prestacoes_pagas AS prestacoes_pagas,
parcelas.data AS data,
cliente.escritorio AS escritorio,
cliente.nome AS nome
FROM parcelas JOIN
cliente ON cliente.id = parcelas.id_cliente
GROUP BY cliente.endereco,
cliente.cpf,
parcelas.prestacoes_pagas,
parcelas.data,
cliente.escritorio,
cliente.nome
HAVING (Period_Diff(Date_Format(CurDate(), '%y%m'), Date_Format(parcelas.data,
'%y%m')) - parcelas.prestacoes_pagas) >= 2 AND cliente.escritorio =
'$_SESSION[EW_SESSION_USER_NAMES2]'

With PHPMaker v7 but it works in v9 of the following error when running a search:
Failed to execute SQL. Error: Unknown column 'cliente.endereco' in 'where clause'

Post by **Webmaster** » Tue Aug 20, 2013 11:55 am

You cannot use PHP variables in Custom View.

oliveira · Post by **oliveira** » Tue Aug 20, 2013 6:31 pm

So rather PHPMaker v(7) where everything works !

Post by **Webmaster** » Wed Aug 21, 2013 11:38 am

No, it should not work in v7 neither.

oliveira · Post by **oliveira** » Thu Aug 29, 2013 10:57 am

Yes friend, this query works perfectly in PHPMaker v (7)

mobhar · Post by **mobhar** » Thu Aug 29, 2013 11:52 am

oliveira wrote:
With PHPMaker v7 but it works in v9 of the following error when running a search:
Failed to execute SQL. Error: Unknown column 'cliente.endereco' in 'where clause'

Perhaps in v9 you have modified your project by adding a filter somewhere that involved cliente.endereco field, whereas the SQL doesn't.

Alternatively, recheck your SQL above
cliente.endereco AS endereco,

should be
cliente.endereco,