Ask Your Question
0

Base report sorting

asked 2017-04-08 02:32:53 +0200

rotormansa gravatar image

I have an odd type of sorting that is needed. I have a field in the form of : Letter Letter Number Letter Letter Letter but the field could have Letter Number Letter Letter Letter OR Letter Number Letter Letter

Basically I want to sort alphabetically by the characters to the Right of the number.

Not sure how to do this.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-04-08 23:50:48 +0200

Ratslinger gravatar image

You need a new field generated from the field you have based upon your criteria. You can do this in your SQL statement which you probably have for input to your report. There are better methods to do this in other DB's, but this is the best I could come up with using HSQLDB SQL. It utilizes the CASE statement but needs to be nested 10 x's to cover each of the ten digits. Here is the statement I used as a test:

select ID, STRFIELD,
 CASE WHEN LOCATE('0',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('0',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('1',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('1',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('2',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('2',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('3',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('3',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('4',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('4',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('5',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('5',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('6',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('6',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('7',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('7',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('8',STRFIELD) > '0' THEN SUBSTR(STRFIELD, LOCATE('8',STRFIELD) + 1) ELSE
 CASE WHEN LOCATE('9',STRFIELD) > '0' then SUBSTR(STRFIELD, LOCATE('9',STRFIELD) + 1) ELSE
 STRFIELD END END END END END END END END END END
 AS "SORTFLD" from TESTSAMPLE

This will find any single digit and return the portion after that digit. Here is the result:

image description

STRFIELD was input to the process and SORTFLD was the result. Note: If the input does not contain a numeric digit, the entire string is returned.

If this answers your question please click on the ✔ (upper left area of answer).

edit flag offensive delete link more

Comments

thx... I will give this method a try.

rotormansa gravatar imagerotormansa ( 2017-04-10 02:51:46 +0200 )edit

this works running as an SQL command. How would I put this 'filter' into my table that has all the data?

rotormansa gravatar imagerotormansa ( 2017-04-10 13:45:00 +0200 )edit

If I understand your comment correctly, you cannot put the SQL into your table. You can create a new field and update that new field with an "UPDATE" SQL statement and thus create the new information, but then you would need to do that for each new record entered into the table.

To do this automatically for each new record, specific macro code would need to be written to read the input data, create the new data based on that and then move it to the new field for writing to the record.

Ratslinger gravatar imageRatslinger ( 2017-04-10 14:51:17 +0200 )edit

I'm going to amend my last comment. Using a newer HSQL (v 2.x as in a split DB) or other external DB's, it may be possible to do this automatically for new records using a DB trigger. I will try testing & get back to you on this. The already entered records would still need creation via SQL update statement.

Ratslinger gravatar imageRatslinger ( 2017-04-10 16:20:00 +0200 )edit

@rotormansa I have successfully tested creating a trigger based upon the above discussions. If you are interested, please create a new question such as "Generate trigger in split DB to create field derived from another". I ask for a new question (again if interested) not only because it requires quite a bit of explanation, but also this subject is rarely asked for in this forum and could be of benefit to others. If the answer were buried here it may not be found.

Ratslinger gravatar imageRatslinger ( 2017-04-10 19:07:31 +0200 )edit

just posted... many thanks!

rotormansa gravatar imagerotormansa ( 2017-04-11 00:12:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-04-08 02:32:53 +0200

Seen: 45 times

Last updated: Apr 08 '17