Ask Your Question
0

logic error in Base CASE statement

asked 2016-07-14 20:03:48 +0200

MasterG gravatar image

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 ;

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-07-14 20:50:35 +0200

Ratslinger gravatar image

updated 2016-07-18 22:01:40 +0200

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:

image description

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

edit flag offensive delete link more

Comments

If it's a date, then how do I get a string length when I query: SELECT LENGTH(Tdate) AS Long FROM Sales ;

MasterG gravatar imageMasterG ( 2016-07-14 21:34:40 +0200 )edit

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?

Ratslinger gravatar imageRatslinger ( 2016-07-14 21:45:05 +0200 )edit

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.

MasterG gravatar imageMasterG ( 2016-07-14 21:53:23 +0200 )edit

sample data: 9/13/1999 12:00:00 AM

MasterG gravatar imageMasterG ( 2016-07-14 21:55:58 +0200 )edit

Please see Edit in original answer.

Ratslinger gravatar imageRatslinger ( 2016-07-14 22:18:29 +0200 )edit

same result ... no data returned

MasterG gravatar imageMasterG ( 2016-07-15 08:00:09 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2016-07-15 15:00:09 +0200 )edit

I'm using Windows 8.1 OS and LO version 4.4. Do you think I should upgrade?

MasterG gravatar imageMasterG ( 2016-07-15 20:50:51 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2016-07-15 21:10:31 +0200 )edit

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?

Ratslinger gravatar imageRatslinger ( 2016-07-15 21:30:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-07-14 20:03:48 +0200

Seen: 330 times

Last updated: Jul 18 '16