tlkhorses Blue Pill
Joined: May 09, 2007 Posts: 1
|
Posted: Wed May 09, 2007 7:46 pm Post subject: base, sql error |
|
I have posted this over on the openoffice.org base forum but thought I would try here also. Here is a copy of my stuff there... short version:
Ok, I have looked at this until I think I am going crazy. I think I need another set of eyes. Anyway I tried a different tact.
I ran the following in console and it worked:
select Customer.customerid,Customer.lastname,Customer.firstname,Customer.status,CustomerEventLog.username, substring(CustomerEventLog.Text,(locate('-',CustomerEventLog.Text)+1),2), substring(CustomerEventLog.Text,(locate('@',CustomerEventLog.Text)+1),2), substring(CustomerEventLog.Text,(locate('sec',CustomerEventLog.Text)-3),7), InstallTimes.username, InstallTimes.status from Customer, CustomerEventLog, InstallTimes where CustomerEventLog.time > 20070401 and CustomerEventLog.subject like '%survey%' and locate('@',CustomerEventLog.Text)>0 and Customer.customerid = CustomerEventLog.customerid and Customer.customerid = InstallTimes.customerid order by CustomerEventLog.customerid, CustomerEventLog.time, InstallTimes.time;
If I run the following in console I get the same error as my initial post here:
SELECT `Customer`.`CustomerID`, `Customer`.`LastName`, `Customer`.`FirstName`, `Customer`.`Status`, `CustomerEventLog`.`Username`, SUBSTRING ( `CustomerEventLog`.`Text`, ( LOCATE( '-', `CustomerEventLog`.`Text` ) + 1 ), 2 ), SUBSTRING ( `CustomerEventLog`.`Text`, ( LOCATE( '@', `CustomerEventLog`.`Text` ) + 1 ), 2 ), SUBSTRING ( `CustomerEventLog`.`Text`, ( LOCATE( 'sec', `CustomerEventLog`.`Text` ) - 3 ), 7 ), `InstallTimes`.`Username`, `InstallTimes`.`Status`, `CustomerEventLog`.`Time`, `CustomerEventLog`.`CustomerID`, `InstallTimes`.`Time` FROM `powernoc`.`Customer`, `powernoc`.`CustomerEventLog`, `powernoc`.`InstallTimes` WHERE ( `Customer`.`CustomerID` = `CustomerEventLog`.`CustomerID` AND `Customer`.`CustomerID` = `InstallTimes`.`CustomerID` ) AND ( ( `CustomerEventLog`.`Time` > {D '2007-04-01' } AND `CustomerEventLog`.`Subject` LIKE '%survey%' AND LOCATE( '@', `CustomerEventLog`.`Text` ) > '0' ) ) ORDER BY `CustomerEventLog`.`Time` ASC, `CustomerEventLog`.`CustomerID` ASC, `InstallTimes`.`Time` ASC;
If I try to put the first select into base and go to design view I get sql error.
If I try to paste the first select here into the base sql I get sql error (and thats all) when I try to switch to design view.
A bit more on this. In the second SELECT above if I remove the space between SUBSTRING and the ( then that one works also in console. However, trying to put it into base query, it automatically inserts that space back into it.
Any work around for that?
tk |
|