BiLe asked 3 years ago
Hello,
I am trying to run the following query, but the ORDER BY clause is causing problems. Is there a better way to label values? I am running 2 languages. I hope I do not need to build an extra table for my labels:
$grid->select_command = “SELECT
CASE
WHEN chart_id = 0 THEN ‘Aktuell’
WHEN chart_id = 1 THEN ‘Vorschlag’
WHEN chart_id = 2 THEN ‘Vorschlag B’
WHEN chart_id = 3 THEN ‘Vorschlag C’
ELSE ”
END AS chart_id,
xy,
rank,
rank_cnt
FROM
app_1_analysis_1
WHERE
xy = ‘test’
ORDER BY chart_id ASC, rank ASC”;
BiLe answered 3 years ago
Also, by changing values 0,1,2,3 to text (using CASE), the search for this column will only respond to the actual value and not the label.
Abu Ghufran Staff answered 3 years ago
For your first query, remove the ORDER BY clause from select_command and set it in:
$opt[“sortname”] = “chart_id ASC, rank”;
$opt[“sortorder”] = “ASC”;
// …
$grid->set_options($opt);
For second query, with chart_id column, you need to set:
$col[“dbname”] = “CASE
WHEN chart_id = 0 THEN ‘Aktuell’
WHEN chart_id = 1 THEN ‘Vorschlag’
WHEN chart_id = 2 THEN ‘Vorschlag B’
WHEN chart_id = 3 THEN ‘Vorschlag C’
ELSE ” END”;
By this it will use this CASE statement in where clause.
