Query worked with Wt 3.3.6 doest not work ant more with Wt 3.5.1
Added by Lamiel Toch about 4 years ago
Hello,
I ran my project with Wt 3.3.6 and a postgre database until now and it worked well.
Now, I try to run my project with Wt 3.5.1 but a query does not work any more :
std::string select = "select s.id_activite, s.timestamp, max(s.date_transfert) m "
" from saisies s, activites a where "
" s.id_activite = a.id_activite "
" and date(date_transfert) >= '" + toSqlDate(debutMin) + "'" +
" and date(date_transfert) <= '" + toSqlDate(finMax) + "'" +
" and a.societe = ? "
" group by s.timestamp, s.id_activite"
" order by m desc";
In the output of my application : Wt: fatal error: syntax error near « as»
LINE 1: ...ol0, s.timestamp as col1, max(s.date_transfert) m as col2 f...
It seems that Wt add a "as" key word. Is there a way to disable this ?
Thanks in advance
Lamiel
Replies (6)
RE: Query worked with Wt 3.3.6 doest not work ant more with Wt 3.5.1 - Added by Roel Standaert about 4 years ago
Do you have the full query that is being generated here? The as keyword should normally be accepted by PostgreSQL, are you using a particularly old version?
RE: Query worked with Wt 3.3.6 doest not work ant more with Wt 3.5.1 - Added by Roel Standaert about 4 years ago
Ah, no I see now. You're already aliasing max(s.date_transfert) as m
RE: Query worked with Wt 3.3.6 doest not work ant more with Wt 3.5.1 - Added by Lamiel Toch about 4 years ago
This is the full generated query :
select s.id_activite as col0, s.timestamp as col1, max(s.date_transfert) m as col2 from saisies s, activites a where s.id_activite = a.id_activite and date(date_transfert) >= '2019-12-18' and date(date_transfert) <= '2019-12-18' and a.societe = $1 group by s.timestamp, s.id_activite order by m desc
I use PostgreSQL 9.5. I have tested a query with pgadmin with the "as" key word and it works without problem.
I think the problem is that i already put the alias "m" after max(date.transfert) and Wt append other alias "col2".
RE: Query worked with Wt 3.3.6 doest not work ant more with Wt 3.5.1 - Added by Roel Standaert about 4 years ago
It looks like if you write as m instead of just m, then it seems like it will actually remove your alias, so it would become max(s.date_transfert) as col2, but that isn't ideal either, since you're ordering by this m. That should probably be fixed.
RE: Query worked with Wt 3.3.6 doest not work ant more with Wt 3.5.1 - Added by Roel Standaert about 4 years ago
I actually tested it, and it should be fine if you use max(s.date_transfert) as m instead of the shorthand max(s.date_transfert) m.
RE: Query worked with Wt 3.3.6 doest not work ant more with Wt 3.5.1 - Added by Lamiel Toch about 4 years ago
Actually, I have tested with the following query in Wt 3.5.1 :
std::string selectElements = "select e, o from elements e, organisation_activites o "
" where e.id_element = o.id_element and o.id_activite = " + std::to_string(id_collecte_) + " order by o.ordre asc";
without the keyword "as" and it works anyway.