Mysql fails to print the others columns with its data type?

Get Started
1 
sub pending_running_partition
{
    $var_data_running = "";
    $var_data_pending = "";
 	 my $str= shift;
	$DBH = &connect or die "Cannot connect to the sql server \n";
	$DBH->do("USE $str;");

	my $stmt="select queue_name,jobs_pending,jobs_running from((select queue_name,jobs_pending,jobs_running from queues order by jobs_pending desc ,jobs_running desc limit 5)union all(select 'others' as queue_name ,sum(jobs_pending)  as jobs_pending ,sum(jobs_running) as jobs_running from (select jobs_pending,jobs_running from queues order by queue_name limit 0 offset 5 ) foo)) foo;";
	#my $stmt="SELECT    CASE WHEN num <= @row_limit THEN queue_name ELSE 'others' END name,    num, queue_name, jobs_pending, jobs_running FROM   (   SELECT     @row_num := @row_num + 1 as num,     queue_name, jobs_pending, jobs_running   FROM     queues, (SELECT @row_num := 0, @row_limit:=5) r   ) q;";

	my $sth = $DBH->prepare( $stmt );
	 $sth->execute() or die $sth->errstr;
	 my $tmp = 0;
	while(my @row_array=$sth->fetchrow_array)
	{
	    if ($tmp == 0) {
		$var_data_running .= "\[\"$row_array[0] \($row_array[2]\)\",$row_array[2]\]";
		
		$var_data_pending .= "\[\"$row_array[0] \($row_array[1]\)\",$row_array[1]\]";
		$tmp++;
	    }
	    else {
		$var_data_running .= ",\[\"$row_array[0] \($row_array[2]\)\",$row_array[2]\]";
		$var_data_pending .= ",\[\"$row_array[0] \($row_array[1]\)\",$row_array[1]\]";
	    }
	
	}
	$sth->finish;
	$DBH->disconnect();
}

Here the others row printts which as follows:

How to add integer data type for those rows from mysql statement.

queue_name | jobs_pending | jobs_running |
+------------+--------------+--------------+
| others     |         NULL |         NULL |

Instead of null integers should be printed from mysql statement

2

It might be the LIMIT 0 ?

3

so what would be the limit then.

4

What do you[quote=“finddata15, post:3, topic:259443, full:true”]
so what would be the limit then.
[/quote]

What do you think it should be?
Obviously 0 will return no results.
I notice you use a while look to process row by row, so presume you are expecting more than a single row.
Do you even want a limit at all?

5

I dont need limit in the select jobs_pending,jobs_running from queues order by queue_name limit 0 offset 5 ) foo) in this part because the others column should the remaining rows

6

Good. Get rid of it and run the query again, and see if it works.

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.