But this query work not in this plugin. this query is related WITH statement
1. my table information as below..
CREATE TABLE IF NOT EXISTS retail_m ( date DATE not null, country CHAR(128) not null, retail FLOAT, department FLOAT, supermarket FLOAT, cvs FLOAT, without_store FLOAT, offline FLOAT, online FLOAT, online_portion FLOAT, online_products_portion FLOAT, intenet FLOAT, mobile FLOAT,
...
2. My query is as below
WITH retail_y AS ( SELECT EXTRACT(year from date) as year, EXTRACT(month from date) as month, retail as retail, online_portion AS online_portion, online_products_portion AS online_products_portion, offline AS offline, online AS online, internet AS internet, mobile AS mobile FROM retail_m
WHERE retail_m.`country` = 'korea' AND retail_m.`date` > now() -INTERVAL 61 MONTH GROUP BY 1,2 ) SELECT month AS '월',
retail AS '소매판매', ((retail / LAG(retail,12) OVER (ORDER BY year, month))/ LAG(retail,12) OVER (ORDER BY year, month))*100 AS '소매판매 YoY', offline AS '오프라인', ((offline / LAG(offline,12) OVER (ORDER BY year, month))/ LAG(offline,12) OVER (ORDER BY year, month))*100 AS '오프라인 YoY', online AS '온라인쇼핑', ((online / LAG(online,12) OVER (ORDER BY year, month))/ LAG(online,12) OVER (ORDER BY year, month))*100 AS '온라인쇼핑 YoY', online_portion AS '온라인쇼핑 비중', online_products_portion AS '온라인쇼핑 상품 비중', internet AS '인터넷쇼핑', ((internet / LAG(internet,12) OVER (ORDER BY year, month))/ LAG(internet,12) OVER (ORDER BY year, month))*100 AS '인터넷쇼핑 YoY', mobile AS '모바일쇼핑', ((mobile / LAG(mobile,12) OVER (ORDER BY year, month))/ LAG(mobile,12) OVER (ORDER BY year, month))*100 AS '모바일쇼핑 YoY'
Since this is a quite complicated query for wpDataTables, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.
Hi,
I made some sql query statment.
This query work well at PHPMyADMIN
But this query work not in this plugin. this query is related WITH statement
1. my table information as below..
CREATE TABLE IF NOT EXISTS retail_m (
date DATE not null,
country CHAR(128) not null,
retail FLOAT,
department FLOAT,
supermarket FLOAT,
cvs FLOAT,
without_store FLOAT,
offline FLOAT,
online FLOAT,
online_portion FLOAT,
online_products_portion FLOAT,
intenet FLOAT,
mobile FLOAT,
...
2. My query is as below
WITH retail_y AS (
SELECT EXTRACT(year from date) as year,
EXTRACT(month from date) as month,
retail as retail,
online_portion AS online_portion,
online_products_portion AS online_products_portion,
offline AS offline,
online AS online,
internet AS internet,
mobile AS mobile
FROM retail_m
WHERE
retail_m.`country` = 'korea'
AND retail_m.`date` > now() -INTERVAL 61 MONTH
GROUP BY 1,2
)
SELECT month AS '월',
retail AS '소매판매',
((retail / LAG(retail,12) OVER (ORDER BY year, month))/ LAG(retail,12) OVER (ORDER BY year, month))*100 AS '소매판매 YoY',
offline AS '오프라인',
((offline / LAG(offline,12) OVER (ORDER BY year, month))/ LAG(offline,12) OVER (ORDER BY year, month))*100 AS '오프라인 YoY',
online AS '온라인쇼핑',
((online / LAG(online,12) OVER (ORDER BY year, month))/ LAG(online,12) OVER (ORDER BY year, month))*100 AS '온라인쇼핑 YoY',
online_portion AS '온라인쇼핑 비중',
online_products_portion AS '온라인쇼핑 상품 비중',
internet AS '인터넷쇼핑',
((internet / LAG(internet,12) OVER (ORDER BY year, month))/ LAG(internet,12) OVER (ORDER BY year, month))*100 AS '인터넷쇼핑 YoY',
mobile AS '모바일쇼핑',
((mobile / LAG(mobile,12) OVER (ORDER BY year, month))/ LAG(mobile,12) OVER (ORDER BY year, month))*100 AS '모바일쇼핑 YoY'
FROM retail_y
ORDER BY 1,2;
3. Error message
Only this plugin..
In this plugin said : WITH not implemented.
What suould do I?
please give me advices.
Thanks
Hi Dongsoon
Thank you for reaching out to us.
Since this is a quite complicated query for wpDataTables, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature: