SQL EXTRACT Problem

Relativity Server
1

Hi i would like to use the MySQL EXTRACT(MONTH … but i always get the Error Comma expected.
Is the EXTRACT Method Supported in RemObjects / Relativity Server?
The SQL itself works when it is tested with phpMyAdmin:

 sql_string_rep_default := 'SELECT '+
                                    'EXTRACT(MONTH FROM HOTEL_D.D_DATE) AS S_MONTH, '+
                                    'EXTRACT(YEAR FROM HOTEL_D.D_DATE) AS S_YEAR, '+
                                    'HOTEL_DD.BIBLE_TYPE, '+
                                    'SUM(HOTEL_DD.AMOUNT) AS TOT_AMOUNT '+
                                  'FROM '+
                                    'HOTEL_D '+
                                    'INNER JOIN HOTEL_DD ON (HOTEL_D.ID = HOTEL_DD.ID_D) '+
                                  'WHERE '+
                                    'HOTEL_D.C_NO = ''' + camp_ident_part + ''' AND '+
                                    'D_DATE BETWEEN ''' + date_from + ''' AND ''' + date_to + ''' AND '+
                                    'HOTEL_D.STAT_DEL = 0 AND '+
                                    'HOTEL_DD.STAT_DEL = 0 '+
                                  'GROUP BY '+
                                    'EXTRACT(MONTH FROM HOTEL_D.D_DATE), '+
                                    'EXTRACT(YEAR FROM HOTEL_D.D_DATE), '+
                                    'HOTEL_DD.BIBLE_TYPE';

DM.RemoteDataAdapter.FillWithDASql(DM_reports.tbl_rep_default_s1,sql_string_rep_default);

This is the working SQL Result:

SELECT EXTRACT(MONTH FROM
HOTEL_D.D_DATE) AS S_MONTH, EXTRACT(YEAR FROM
HOTEL_D.D_DATE) AS S_YEAR, HOTEL_DD.BIBLE_TYPE,
SUM(HOTEL_DD.AMOUNT) AS TOT_AMOUNT FROM HOTEL_D INNER
JOIN HOTEL_DD ON (HOTEL_D.ID = HOTEL_DD.ID_D) WHERE
HOTEL_D.C_NO = 'I47710' AND D_DATE BETWEEN '2010.01.01' AND
'2015.06.25' AND HOTEL_D.STAT_DEL = 0 AND HOTEL_DD.STAT_DEL = 0
GROUP BY EXTRACT(MONTH FROM HOTEL_D.D_DATE), EXTRACT(YEAR
FROM HOTEL_D.D_DATE), HOTEL_DD.BIBLE_TYPE
2

Hello

The .FillWithDASql method accepts only DA SQL statements. While DA SQL itself is very close to a ‘real’ SQL it provides only a limited set of functions. DB-specific SQL functions aren’t supported by DA SQL.
The best way to use such SQL statements is to create a read-only Schema table exposing them as static SQL statement…

Regards

3

Thanks for the Advice.

Shalom
Manfred