PDA

View Full Version : SQL error on LOCATE


ABC123

Steve Wood
08-27-2004, 02:10 PM
This SQL error statement seems to have some dummy data in it. The LOCATE function derives automatically from a static choice list for the field named "C" with these options:

Hold|H
Cancelled|C
Active|A

Error executing SQL: SELECT C, Jobno, Statusdate, Sortid FROM TRK_ALL.CSV WHERE C = 'A' OR date() - StatusDate " 180 AND (( {fn LOCATE(upper(?), CONCAT('xx',upper(C)),2)} - 2 " 0 )) ORDER BY SortID
[Microsoft][ODBC Text Driver] Undefined function 'CONCAT' in expression.

Selwyn Rabins
08-27-2004, 04:32 PM
what database are you going against?

the error says that this database does not support the concat() function, which is surprising since this function is part of the ansi standard.

Steve Wood
08-28-2004, 12:06 PM
Database? No database, comma delimited text file using Microsoft ODBC Text Driver. I guess I should have included that minor detail! Before I get a nasty frown, let me explain what I am trying to accomplish.

I have a non-Alpha Tracking System that exports this CSV file every 15 minutes. I use that file to automatically present tracking detail to the entire office and remote users via Microsoft Excel and Auto-Update (poor-man's ASP, works like a charm).

I can't use Alpha to import the data every 15 minutes without significant overhead (10,000 records). So since I have this ready made CSV file, why not use this? The other options I have considered is to import the data direct to a DBF file using a clipper routine or one of the many CSVtoDBF utilities. The tracking info is read-only.

I'm rewriting the Tracking System in native Alpha, but that is a few months off.

Steve

Selwyn Rabins
08-29-2004, 10:16 AM
i have been able to duplicate the problem. we had never tested the odbc text driver. anyway, i am pretty sure that we can get this to work for the next patch.

in playing with the text driver i see it seems pretty limited. you can use it for viewing data, but apparently not for entering and updating data?

Selwyn Rabins
08-29-2004, 11:17 AM
ok. next patch (due early next week) will allow searches on ODBC Text data sources.

the only limitation is that the search is always case sensitive.