• Registered: 2014-10-04
  • Posts: 154

Topic: How to get calculated fields in queries?

Hi,
I just need to have calculated field values inside my a query in my script!

I just tried to use the exact same query in my script but got sql syntax Error which is works fine in calculated fields!!!!!

here is the code:

Main.Panel1.Caption := SQLExecute('CASE WHEN (SELECT b.is_active FROM bookings b WHERE b.is_active=1 AND b.id_boxes=1) != 0 THEN "Nein" ELSE "Ja" END');

I just got this error: Near CASE syntax error! and here is the returned query:

CASE WHEN (SELECT b.is_active FROM bookings b WHERE b.is_active=1 AND b.id_boxes=1) != 0 THEN "Nein" ELSE "Ja" END

how can I fix my query?

Many thanks

2 Reply by brian.zaballa (edited by brian.zaballa 2022-04-13 07:14:33)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 431

Re: How to get calculated fields in queries?

Just do if-else in the script instead of that case query 

if SQLExecute('SELECT COALESCE(b.is_active, 0) as isActiveReturn FROM bookings b WHERE b.is_active=1 AND b.id_boxed=1 ORDER BY b.is_active DESC LIMIT 1') = 0 then
    Main.Panel1.Caption := "Ja"
else 
    Main.Panel1.Caption := "Nein";
brian

3 Reply by sparrow (edited by sparrow 2022-04-13 10:26:21)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,077

Re: How to get calculated fields in queries?

Hi all,

Unfortunately, you can't write a query like that. The SQL query must be of the form SELECT ... FROM ... WHERE ....
The calculated field in MVD is essentially a subquery that is inserted into the construction SELECT (calcFied)  FROM ... WHERE ....


Your query can be rewritten as

SELECT (CASE WHEN b.is_active != 0 THEN "Nein" ELSE "Ja" END) AS CF
FROM bookings AS b 
WHERE b.is_active = 1 AND b.id_boxes = 1;

Please note that the SQLExecute function can return only one value from the query result.

  • Registered: 2020-03-31
  • Posts: 233

Re: How to get calculated fields in queries?

In b.is_active, what is b?

5 Reply by CDB (edited by CDB 2022-10-17 09:41:22)

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 372

Re: How to get calculated fields in queries?

unforgettable wrote:

In b.is_active, what is b?

b is an alias for the 'bookings' table.

The idea is, you don't have to keep writing the full name of a table (especially if it is a long name), it can be referred to either by a single letter or a shorter name.

It is declared in the line bookings AS b.

On a clear disk you can seek forever