New post
Question
· Aug 21, 2023

Ambiguous sort column error <csp:search>

#CSP #SQL #InterSystems IRIS

A bit of an old school CSP question.  <csp:search> is a really nice, quick way to make easy lookups for CSP pages, especially for back office stuff that doesn't need to look pretty for the modern web app user.

At any rate, I'm here at the moment:

<csp:search SHOWSQL=1 NAME="OrgSearch" SELECT="OrganizationName Organization Name,Abbreviation,Software->Abbreviation Software" WHERE="OrganizationName Organization Name,Abbreviation,Software->Abbreviation Software" STARTVALUES=",,Software" PREDICATES="%startswith,%startswith,%startswith" CLASSNAME="AppLibrary.Organization" OPTIONS="popup" ONSELECT="OrgSearchSelect">

When I try to sort by the second column, the Organization Name column, I get an ambiguous search error.  With SHOWSQL=1 I can see that the alias I indicate in the SELECT param is not translated to the SQL query as SELECT Software->Abbreviation as Software so it won't see that as a different column name.  I can't use AppLibrary.Organization.Abbreviation or Organization.Abbreviation as they are invalid for this use (these options do work in the SQL explorer in the SMP though).

Any suggestions on how I can overcome this sort issue?

Product version: IRIS 2023.1
$ZV: IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2023.1 (Build 229U) Fri Apr 14 2023 17:20:01 EDT
Discussion (3)1
Log in or sign up to continue
Vitaliy Serdtsev · Aug 22, 2023

The problem can be solved in two ways:

  1. use OPTIONS="popup,sortbox"
  2. make a correction to the %CSP.PageLookup
    Write "&nbsp;<a href=""javascript:searchSort("_..QuoteJS(value)_");"" title=""Sort Results by "_alias_""">"
|
V
Write "&nbsp;<a href=""javascript:searchSort("_i_");"" title=""Sort Results by "_alias_""">"

In both case, the query will take the form "ORDER BY <the ordinal number of the field>", instead of "ORDER BY <field name>"

0 0
Michael Davidovich · Aug 22, 2023

Thank you!

sortbox - If present, the Search Page displays a set of radio buttons that allows the user to choose how to sort the results.

I wouldn't have immediately thought that would solve it but it makes sense along with your explanation.

Out of curiosity, how would you override that write statement in %CSP.Lookup?  Isn't that generated code which one wouldn't want to touch?

0 0