Why my sql throws an Every derived table must have its own alias error?

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 queue_name limit 5)union all select 'others',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 -1 offset 5)";
	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]\]";
	    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]\]";
select queue_name
from (
  select queue_name
  from queues 
  order by queue_name limit 5)
union all 
select 'others' 
  ,sum(jobs_pending)as jobs_pending
  ,sum(jobs_running)as jobs_running 
  select jobs_pending
  from queues 
  order by queue_name limit -1 offset 5)


The [AS] tbl_name clause is mandatory because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names.

See the problem(s)?

i had also used the same thing which you had mentioned .

The query that I posted is the exact same query in your first post, reformatted in the hope it would be more readable allowing you to see the mistake(s)

MySQL is looking for FROM table_name
but your query has like FROM ()

so now what should i fix it?

A start would be giving the derived tables alias names.
But also note the “columns in the sub query select list must have unique names” bit.

Could you plesae show me changes with my code.

You didn’t go to the page I linked to earlier. Here it is again.


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