Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

› WebAssist Forums › Data Bridge › Data Bridge General / Announcements › Problem with empty value and descending sort function

Problem with empty value and descending sort function

Thread began 3/11/2013 3:12 am by Noa2086 | Last modified 3/12/2013 1:16 pm by Noa2086 | 1077 views | 4 replies |

3/11/2013 3:12 am | #1 Noa2086

Problem with empty value and descending sort function

Hi!

I would like to order my prices from low to high. When a value is empty, then it means "on demand". Now it is smart when the "on demand" prices would be at the end of the sort list.
At the moment they are at the top of the sort list.

How can i get the empty values at the end of a descending price sort list?

Thx for your help!

Kind Regards
Roman

3/11/2013 7:32 am | #2 Jason ByrnesWebAssist

use COALESCE(). ORDER BY COALESCE(price,999999) will return the value of the price column if it is set, or 999999 if it is null

SELECT * FROM items ORDER BY COALESCE(price,999999) ASC

3/11/2013 12:33 pm | #3 Noa2086

Hi jason!

thx for your quick answer. This is a good solution thank you.

how can i integrate it in the wa dataassist sort?

$WADA_Sort_1 = new WADA_SortObject();
$WADA_Sort_1->DefaultSort = "ORDER BY Fahrzeugbezeichnung ASC";
$WADA_Sort_1->ColumnList = "".$_GET['Fahrzeuge_Aufsteigend']."";
$WADA_Sort_1->SortOrderList = "Ascending";
$WADA_Sort_1->ToggleOn = false;
if ((isset($_GET['Fahrzeuge_Aufsteigend']) && $_GET['Fahrzeuge_Aufsteigend'] != "")) {
  $WADA_Sort_1->SetToClause = $WADA_Sort_1->getDefaultSetToClause();
  if ($WADA_Sort_1->ToggleOn && isset($_SESSION["SortierenFahrzeugeAufsteigend"])) {
    $WADA_Sort_1->ToggleClause = $_SESSION["SortierenFahrzeugeAufsteigend"];
  }
}
else if (isset($_SESSION["SortierenFahrzeugeAufsteigend"])) {
  $WADA_Sort_1->SetToClause = $_SESSION["SortierenFahrzeugeAufsteigend"];
}
$WADA_Sort_1->initialize();
$_SESSION["SortierenFahrzeugeAufsteigend"] = $WADA_Sort_1->SetToClause;

the sort function is on a result page.

3/11/2013 3:42 pm | #4 Jason ByrnesWebAssist

change these 2 lines:

php:

$WADA_Sort_1->DefaultSort = "ORDER BY Fahrzeugbezeichnung ASC";

$WADA_Sort_1->ColumnList = "".$_GET['Fahrzeuge_Aufsteigend']."";

to:

php:

$WADA_Sort_1->DefaultSort = "ORDER BY COALESCE(Fahrzeugbezeichnung, 99999) ASC";

$WADA_Sort_1->ColumnList = "".("COALESCE(".$_GET['Fahrzeuge_Aufsteigend'].",999999)")  ."";

3/12/2013 1:16 pm | #5 Noa2086

thx you jason for helping me again!

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now. Build a store, a gallery, or a web-based email solution.