AnsweredAssumed Answered

Subselects with ExecuteSQL?

Question asked by davidhamannmedia on Sep 19, 2012
Latest reply on Sep 20, 2012 by davidhamannmedia

Hey everybody,

 

doesn't ExecuteSQL() support subselects or am I blind?

 

I can't figure out why this query doesn't work:

 

ExecuteSQL (

"SELECT " &
"SUM(" & _fieldNameQuoted ( _Salaries_::d_pay ) & ") " &
"FROM " & 
"(SELECT " &
_fieldNameQuoted ( _Salaries_::_fk_person ) & ", " &
"MAX(" & _fieldNameQuoted ( _Salaries_::d_dateStart ) & ") as maxstartdate " & 
"FROM " &
_fieldTableName ( _Salaries_::__pk ) & " " &
"WHERE " &
_fieldNameQuoted ( _Salaries_::_fk_person ) & " IN (123,456) AND " & 
_fieldNameQuoted ( _Salaries_::d_dateStart ) & " <= ?" & " " &
"GROUP BY " &
_fieldNameQuoted ( _Salaries_::_fk_person ) & " " &
") p " &
"JOIN " &
_fieldTableName ( _Salaries_::__pk ) & " s " &
"ON " &
"s." & _fieldNameQuoted ( _Salaries_::_fk_person ) & "=" & "p." & _fieldNameQuoted ( _Salaries_::_fk_person ) & " AND " &
"s.maxstartdate=" & "p." & _fieldNameQuoted ( _Salaries_::d_dateStart )
;"";"";Date(1;1;2012)

)

 

 

 

FYI: _fieldNameQuoted is just a CF that returns the quoted field name - nothing special.

 

Do I need to find a nasty bug in my code or is ExecuteSQL() not able to understand subselects?

 

Any hint is highly appreciated.

Outcomes