MS SQL Server is the db. Get the error when trying to add sorting by "Nodequeue: Position in nodequeue and other parameter" to a view.

CommentFileSizeAuthor
#1 nodequeue-mssql_ifnull_error-2007070-1.patch727 bytessgorobet
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

sgorobet’s picture

sgorobet CreditAttribution: sgorobet commented
FileSize
nodequeue-mssql_ifnull_error-2007070-1.patch727 bytes

Changed IFNULL to COALESCE, also MS SQL Server doesn't like aliases in WHERE, ORDER BY, SORT BY clauses, so I had to change that part too.

I think 7.x-3.x is affected as well.

fizk’s picture

fizk CreditAttribution: fizk commented
Issue summary: View changes
Status: Active » Postponed (maintainer needs more info) 
-    $this->query->orderby[] = array('field' => "IFNULL($as, $stub) ", 'direction' => $order);
+    $this->query->orderby[] = array('field' => "COALESCE($table.$field, $stub) ", 'direction' => $order);

Why did you use $table.$field instead of $as ?

ehuelsmann’s picture

ehuelsmann CreditAttribution: ehuelsmann commented

Hi, This is a problem for PostgreSQL too. I simply replaced the one occurrence of IFNULL in the module code by COALESCE. Basically as fizk suggests. This solved my issues with this module. Please do the replacement so other database users can benefit from this module without modifications.

Thanks in advance!

BTW, I would submit a patch if the replacement wasn't too trivial.

Regards,

Erik.

ehuelsmann’s picture

ehuelsmann CreditAttribution: ehuelsmann commented

Provided a patch after all. The hardest part was to find out how to clone git repositories for Drupal :-)

BTW, the reason the OP changed the $as to $table.$field, is explained in his second upload commend: "MS SQL Server doesn't like aliases in WHERE, ORDER BYand SORT BY clauses"....

  • fizk committed c8ecd74 on 7.x-2.x 
    Issue #2007070 by sgorobet: Use COALESCE instead of IFNULL, and avoid...

  • fizk committed 0a044c7 on 
    Issue #2007070 by sgorobet: Use COALESCE instead of IFNULL, and avoid...
fizk’s picture

fizk CreditAttribution: fizk commented
Status: Postponed (maintainer needs more info) » Fixed

Committed, thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.