Good morning,
I’ve gone rounds with a problem I’ve been having and after reaching out for help, have information on a bug that @GunterEibl was able to confirm for me. It seems a Variable in a Grid’s Select query prior to the “FROM” portion of the query is not being converted/translated to the data that the variable holds. I’ll provide Gunter’s example first because it’s simpler, and then mine below that.
=================
I used a grid based on a table with two fields only (id, test_1) and [t_id] = 2
Code:
SELECT
id,
test_1,
[t_id] as temp_id
FROM
test_1
WHERE
id > [t_id]
The application for sure knows the value of [t_id] before executing the SQL:
If I put an echo into onApplicationInit like
PHP Code: echo “Global var:” . [t_id];
it does show the value.
But it does not replace the [t_id] in the SQL statement (first part). However it does in the second part:
Debug-output:
(pdo-mysql): SELECT id, test_1, [t_id] as temp_id from test_1 where id > 2 LIMIT 0,12
As you can see, the [t_id] is not being translated prior to the FROM clause. Here is mine and in the debug mode, you can see the date variable is there, just not interpreted in the first half of the select query.
=====================
SELECT
Account,
name,
avg_hours,
HireDate,
Location,
If(termed IS NULL,NULL,1) as ended,
If(HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’,1,null) as started,
If(termed IS NULL,NULL,avg_hours) as LostHrs,
if(termed IS NULL,NULL,datediff(termed,HireDate)) AS duration,
termed
FROM
sec_users
WHERE
HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’ OR termed BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’
ORDER BY
CASE WHEN termed is null THEN 1 ELSE 2 END, name
Debug-output:
(pdo-mysql): SELECT name, Location, HireDate, termed, If(termed IS NULL,NULL,1) as ended, If(termed IS NULL,NULL,avg_hours) as losthrs, if(termed IS NULL,NULL,datediff(termed,HireDate)) as duration, If(HireDate BETWEEN ‘[recrut_start]’ AND ‘[recrut_end]’,1,null) as started, avg_hours from sec_users where HireDate BETWEEN ‘20180302’ AND ‘20180327’ OR termed BETWEEN ‘20180302’ AND ‘20180327’ order by Location asc, CASE WHEN termed is null THEN 1 ELSE 2 END asc, name asc LIMIT 0,17