ADO QUESTION: using a Harbour function in a WHERE clause

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Sun Jun 23, 2013 12:01 PM

Hello,

I would like to use a Harbour function in a where clause.

I need to pass to Harbour´s function the value from the SQL TABLE:

cSQLi1 := "SELECT * FROM APUNTES WHERE MyFunction(FECHA) = '1' "

That gives an error indicating that MyFunction is not defined on the SQL engine.

So I tried with no luck

"SELECT * FROM APUNTES WHERE "+ MyFunction( "+FECHA+" ) +" = '1' "

But In MyFunction I don´t get FECHA value, I just retrieve +FECHA+

Any advice will be welcomed.

Thank you very much. Best regards

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

Kleyber

Posts: 581

Joined: Tue Oct 11, 2005 11:28 AM

Re: ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Sun Jun 23, 2013 12:13 PM

Lucas,

Try:

"SELECT * FROM APUNTES WHERE "+ MyFunction( FECHA ) +" = '1' "

Kleyber Derick

FWH / xHb / xDevStudio / SQLLIB

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Sun Jun 23, 2013 12:18 PM

Thank you, but it does not work as FECHA must be returned from SQL, it is a field from the table (FECHA=Date)

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

Kleyber

Posts: 581

Joined: Tue Oct 11, 2005 11:28 AM

Re: ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Sun Jun 23, 2013 12:45 PM

Ok, so I would do this way:

MiFecha = MyFunction () // In MyFunction() I'd use the SELECT to get the field FECHA and do the necessary

"SELECT * FROM APUNTES WHERE "+ MiFecha +" = '1' "

Kleyber Derick

FWH / xHb / xDevStudio / SQLLIB

Armando

Posts: 3358

Joined: Fri Oct 07, 2005 08:20 PM

Re: ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Sun Jun 23, 2013 04:04 PM

Lucas:

I do this way with xHarbour and MySql

Code (fw): Select all Collapse

oRsHdr:Source               := "SELECT " +;
                                        "HdrCot.*," +;
                                        "Clientes.* " +;
                                    "FROM " +;
                                        "HdrCot " +;
                                    "LEFT JOIN " +;
                                        "Clientes " +;
                                    "ON " +;
                                        "HdrCot.Hdr_Cli = Clientes.Cli_Cli " +;
                                    "WHERE " +;
                                        "YEAR(Hdr_Fde) = '" + STR(nAmo,4,0) + "' " +;
                                    "AND " +;
                                        "MONTH(Hdr_Fde) = '" + STR(nMes,2,0) + "' " +;
                                    "ORDER BY " +;
                                        "Hdr_Cot"

Regards

SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Sun Jun 23, 2013 05:48 PM

Armando,

Muchas gracias, pero no es lo que necesito, porque Month es una función de MySQL.

Sin Embargo, no está presente en SQLite.

Imagina que quieres usar otra función tuya.

Muchas gracias en todo caso.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Sun Jun 23, 2013 05:50 PM

Armando,

Muchas gracias, pero no es lo que necesito, porque Month es una función de MySQL.

Sin Embargo, no está presente en SQLite.

Imagina que quieres usar otra función tuya.

Muchas gracias en todo caso.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO QUESTION: using a Harbour function in a WHERE clause
Posted: Mon Jun 24, 2013 04:21 AM

If it is MySql, MsSql, Oracle etc I create a sql function on the server with the same functionality of MyFunction. SQL statements can use that function.

I am not sure if SqLite allows creation of server-side procedures and functions.

Another way is to maintain a calculated column in the same table which stores the same result as myfunction(fecha) in another column and use that column in the where clause.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion