My query doesn’t return results. I know the string length is 20, 21 or 22 because I queried the length.
SELECT
CASE
WHEN LENGTH(Tdate) = 20 THEN SUBSTRING(Tdate,1,8)
WHEN LENGTH(Tdate) = 21 THEN SUBSTRING(Tdate,1,9)
WHEN LENGTH(Tdate) = 22 THEN SUBSTRING(Tdate,1,10)
END AS Sales_Date
FROM Sales
;
The most likely problem is the field type you are checking. Using your syntax on a text field I get the correct results. If I use a date field the results are empty because the date is stored in a different format than may be displayed. The internally stored format is YYYY-MM-DD even though you may see Tuesday, July 15, 2014 because of format selected.
Edit:
Attached is sample with a simple table with two Queries. Query1 is using the Text field in the table and Query2 is using the Date field in the same table.
Sample: DateQuery.odb
Just for information - Base is not a DB but a front end to a DB. If not modified you are using HSQL. You could have been using others such as MySQL or PostgreSQL or others.
You may need to change you statement to :
SELECT CASE WHEN LENGTH("Tdate") = 20 THEN SUBSTRING("Tdate",1,8) WHEN LENGTH("Tdate") = 21 THEN SUBSTRING("Tdate",1,9) WHEN LENGTH("Tdate") = 22 THEN SUBSTRING("Tdate",1,10) END AS "Sales_Date" FROM "Sales"
Second Edit:
Loaded small sample of data (179 records of the 500K plus) and used your exact SQL. Here are the results:
All fields were set as Text except Price - set as Number. “strap” field was set as KEY field. All records produced a result.
The only difference on my system is the LO version (5.1.3.2) and OS - Linux xubuntu v16.04. On RARE occasion, there has been a problem in the Windows version. It would be more likely be because of the LO version.
Sample2: SalesCSV.odb
If it’s a date, then how do I get a string length when I query:
SELECT LENGTH(Tdate) AS Long
FROM Sales
;
Your syntax returns 10 on a date field for me. What is the field type of Tdate? What DB are you using? LO and OS version?
I’m using LO Base and the field is Format Category:text. and Format: @. When I run the query, it returns lengths of 20, 21 and 22.
sample data: 9/13/1999 12:00:00 AM
Please see Edit in original answer.
same result … no data returned
I’m guessing you mean no result using the sample? Still need LO version (ie: v5.0.6 or v5.1.3 or whatever) and operating system you are using.
I’m using Windows 8.1 OS and LO version 4.4. Do you think I should upgrade?
Yes. Could be the cause of your problem. Newer HSQL. Version 4.4 is no longer supported (although some still use). Lots of changes in 5.0 & 5.1. I’m currently using v5.1.3.2 but 5.1.4 is out and soon v5.2 will be released. You will need to upgrade Java JRE to v7 or 8 for Base. If you upgrade, please post here if it resolves your problem.
Just tested with LO v4.4.7.2 using Java 1.8.0_92 and it still works OK here. Any chance of posting a copy of your DB without personal or confidential info?
Sure. I’m using the Bolder County Assessor Public Data Set. Great for practicing with relational tables
http://www.bouldercounty.org/dept/assessor/pages/propertydatadownload.aspx
Take a look at the All Sales table from The Datasets at the bottom of the page
Descriptions of the fields are found in the property data download help pdf
Great. I will upgrade to LO Version 5.1 and then re-run the query. Thanks.
Also, I hope you are not using all that data in an Embedded DB. If so please note the potential for corruption. To create a “split” db ( data separated out from .odb) see this post: click here.
I just tried it after the upgrade to 5.1.4. How do I check my Java version in Windows?
I’m running Java build 1.8.0_91-b15
Your comment “I just tried it after the upgrade to 5.1.4.” means nothing - what were the results? If it didn’t work, I’ve attached Sample2 in orig ans. This is one where I produced display in ans. Try the query. If that doesn’t work, register that DB (Tools->Options->LibreOffice Base->Databases) selecting New and browse to where you saved SalesCSV.odb at and save. Then open the form DisplayVersion, click the button and report the displayed version # here.
I was successful at running your query but the display version button isn’t working
The display version is not important now - the query works. Your original question is now working. FYI, the problem with the button is probably with either macros disabled or the registration of the DB. If the original .odb of yours is still not working, carefully compare your .odb vs the SalesCSV.odb for differences or as a next step recreate yours again under the new LO version. The main issue is you now can get correct results whereas before none were produced.
