Okay
  Public Ticket #2887919
sql query don't work only in this plugin
Closed

Comments

  • Dongsoon started the conversation

    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


  • [deleted] replied

    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:

    • 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.