How to convert CTE to normal query?-CodePudding

How can I convert this to normal query?

WITH cte AS (
    SELECT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12   `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM test
)
SELECT agentID
FROM cte
WHERE flag = 3;

I need to convert this because I think mariadb is not compatible with cte. I am not really familiar with cte too and I don't have any idea how to break this down to normal sql query in php.

UPDATE:

I tried doing this to run the cte

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "sample_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$stmt = $conn->query("SELECT agentID, bonus FROM (WITH cte AS (
    SELECT DISTINCT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12   `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM sample_tbl
)) where agentID = '10710' && flag = 3");

    if($stmt->num_rows > 0){
        echo "You are elligible to take a course!";
    } else{
           echo "You are not elligible to take a course!";
        }




?>

but it is not working, the result shows

"Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') where agentID = '10710' && flag = 3' at line 7 in C:\xampp\htdocs\try\index.php:16 Stack trace: #0 C:\xampp\htdocs\try\index.php(16): mysqli->query('SELECT agentID,...') #1 {main} thrown in C:\xampp\htdocs\try\index.php on line 16"

CodePudding user response：

Indeed MariaDB is compatible with CTEs, however if you don't want to deal with ctes for whatever reason, you can always transform it into a subquery:

SELECT agentID
FROM (
    SELECT agentID, 
           SUM(bonus > 0) OVER (PARTITION BY agentID 
                                ORDER BY `year` * 12   `month`
                                RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag
    FROM test ) agents_with_summed_bonus
WHERE flag = 3;

If this query, in place of the one built with cte, doesn't work for you, then it means that you're initial query has some mistakes in relation of your tables.

CodePudding user response：

Update Again:

It is now working for me, here is my final code:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "sample_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$stmt = $conn->query("SELECT DISTINCT agentID FROM (SELECT DISTINCT agentID, 
SUM(bonus > 0) OVER (PARTITION BY agentID 
                     ORDER BY `year` * 12   `month`
                     RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) flag from sample_tbl) as cte where agentID = '61599' && flag = 3");

    if($stmt->num_rows > 0){
        echo "You are elligible to take a course!";
    } else{
           echo "You are not elligible to take a course!";
        }




?>