MAX (CASE WHEN... how to use it?

eeeemmmm

Hi everyone, I need help to make a query with MAX () AS xxx.

This is my query:

SELECT

MAX(CASE WHEN item.name='A004' THEN item_data.value ELSE NULL END) AS NOMBRE,

MAX(CASE WHEN item.name='A005' THEN item_data.value ELSE NULL END) AS APELLIDO1,

The "MAX (CASE WHEN..." sentence I don't know how to include it because if I just copy the code of the query I get an error. And if I only copy the code into the () it works (no errors at least).

Any help?

allan

You are going to have to give us a whole lot more information than you have. Where is that SQL being executed (i.e. what script)? Is it a script you found on this site? What is the error? I'm not sure how this relates to DataTables - it looks like a generic SQL question.

Allan

eeeemmmm

Is a server-side script, I take it from here: https://datatables.net/development/server-side/php_postgres

The prompt error is this one:

DataTables warning. table id=table_id - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

But it's only when I put the MAX() sentence, if I only write the content of the MAX( the code in here ), works without problem. So I don't know how to indicate the MAX sentence.

With GROUP BY I'm having problems too, the same message error.

allan

The documentation does note:

This is a legacy documentation page and the methods described may not work with DataTables 1.10+.

Have you updated the script for DataTables 1.10? Or are you using DataTables in legacy mode?

I'm afraid I can't help much with that script other than to point to the client / server documentation should you want to implement your own server-side processing script for DataTables.

Allan

allan

Oh - one suggestion - check what the server is returning. it is presumably returning an error if it is not valid JSON, which will tell you where the issue is.

eeeemmmm

Good morning.

Thank you for your suggestions, allan. I'm using DataTables 1.10.4. Now MAX (CASE) is working correctly. Even with Group by.

Now the problem is that the column name in the MAX sentence is not working. An example:

MAX(CASE...) as NAME;

The 'NAME' is returning a null. I indicate the column NAME in the $aColumns array, but don't recognize it. Any suggestion for custom column names?

EDIT: fixed, I have to put column names in lowercase.

.
.

.

I put here the code that I resolve with MAX case for if anyone need it or has the same question about it like me:

$sQuery = 
SELECT MAX(CASE WHEN item.name='A004' THEN item_data.value ELSE NULL END) as name
FROM    ...
WHERE   ...
GROUP BY ...
ORDER BY ...
$sLimit
";

allan

Great to hear you have it working. Thanks for posting back with your solution.

Allan