Key words: SQL multi-field conditional query; inter-row calculation

Question

I have a database table consisting of dozens of fields. To get records containing Beijing in any field, I tried the following SQL query:

SELECT * FROM table WHERE concat(field1,field2,field3……fieldn) like ‘% Beijing %’

By entering all field names. Now I am wondering if there is a way to directly locate records with the specified string. By this I mean if there is a language or tool supporting separate record handling? It would be the best if I can do this in the following way:

SELECT * FROM table WHERE concat(RECORD) like ‘% Beijing %’

Answer

SPL script:

A2: Retrieve source data from the database.

A3: PL select() function gets records containing the specified string.

The sign ~ represents the current record;

~.array() splits each record into a sequence (the sequence is similar to Java’s array, only with more inclusive members which can be of any data type);

~.array().concat(",")concatenates members of the sequence into a string;

pos(~.array().concat(","),"Beijing") locates the field containing Beijing by looking at if the result is -1.

Many queries are difficult to achieve without the support of discrete records.

Here’s another instance. I have data where the sales amounts are already ordered by dates. I need to find dates whose next dates see the decrease of sales amount by over 10 thousand yuan. The key of the query is how to write the sales amount in the current record and that of the next record.

~.sales is the sales amount in the current record;

~ [1].sales is the sales amount in the next record;

Similarly, ~[-1] represents the previous record; and ~[3] is the third record following the current one.

Based on a game-changing theory, SPL (Structured Process Language) has its own way to describe an algorithm clearly, concisely and efficiently. One SPL programmer can handle the work load that requires 10 Java or SQL database programmers. It’s amazingly high-performing!

More explanations and examples of SPL’s handling of order-based calculations can be found in Order-based SPL and SQL Headaches Therapies – For Record Reference.

Want to have a try?

Download esProc