Skip to main content
Inspiring
April 18, 2016
Question

Order table by specific values

  • April 18, 2016
  • 1 reply
  • 1945 views

I have a query that needs to sort a column in a specific order. Currently for the act_code Charges have a '+' value, Payments have a '-' value and Awards have a '=' value. I need the order to be Charges, Payments, Awards but I get Payments, Charges, Awards. I get "Invalid Column Name act_code" when I run my query. Here is my query:

SELECT tmptract.soc_sec, name.last_name, name.first_name, name.mi, address.st_addr, address.add_addr, address.add_add2, address.city, address.state, address.zip, tcodes.act_code,  '' as employee_soc_sec, 2 as bill_type

FROM tmptract, name, address,  tcodes

WHERE tmptract.soc_sec = name.soc_sec

    AND address.soc_sec = name.soc_sec

    AND tcodes.tcodes = transact.tcodes

    AND tmptract.token = '#session.token#'

ORDER BY name.last_name, name.first_name, name.mi, tmptract.soc_sec, bill_type,

(CASE WHEN act_code = '+' THEN '1'

     WHEN act_code = '-' THEN '2'

     WHEN act_code = '=' THEN '3' ELSE act_code END)

Any help will be appreciated!

This topic has been closed for replies.

1 reply

EddieLotter
Inspiring
April 19, 2016

Bonnie, the field name "act_code" in the "order by" clause does not have its table prefix. Change them all to "tcodes.act_code" and see if it helps.

Cheers

Eddie

Cozmo2Author
Inspiring
April 19, 2016

It did not make a difference.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Tue, Apr 19, 2016 at 10:43 AM, EddieLotter <forums_noreply@adobe.com>

EddieLotter
Inspiring
April 19, 2016

Does the query work without that field in the "order by" clause?

Cheers

Eddie