Order table by specific values

[Cozmo2]

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!

[EddieLotter]

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

[Cozmo2]

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]

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

Cheers

Eddie

[Cozmo2]

Yes.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Tue, Apr 19, 2016 at 1:36 PM, EddieLotter <forums_noreply@adobe.com>

[EddieLotter]

Okay, put the entire "case" statement into the select clause, not the "order by" clause.

If the query fails, please provide the exact error message.

Cheers

Eddie

[Cozmo2]

When I try to save the query I get:

If I try to run the query I get:

This query is a Union Query.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Tue, Apr 19, 2016 at 2:02 PM, EddieLotter <forums_noreply@adobe.com>

[EddieLotter]

Using an "order by" clause with a union query is tricky.

To do it in Microsoft SQL Server you would do it as follows:

select fieldA, fieldB from (
select field1 as fieldA, field2 as fieldB from table1
union
select field3 as fieldA, field4 as fieldB from table2
) as dtOuter
order by fieldA, fieldB

How it's done for your database engine is a question that is best asked in a forum that deals with questions about your database engine. I've mentioned the latter before.

Cheers

Eddie

[Cozmo2]

I am also using the Microsoft SQL Server. Which forum would you recommend?

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Tue, Apr 19, 2016 at 2:18 PM, EddieLotter <forums_noreply@adobe.com>

[EddieLotter]

Cozmo2 wrote:

I am also using the Microsoft SQL Server.

In that case you can apply the concept I demonstrated to you. The reason you're getting the "invalid column name" message is because you are using an "order by clause" with a union query; a vital piece of information that you didn't mention in your original post.

Cozmo2 wrote:

Which forum would you recommend?

A good place is the SQL Server -> Transact-SQL Microsoft forum.

Cheers

Eddie

[EddieLotter]

Bonni, did you modify your SQL statement the way I recommended?

Cheers

Eddie

[Cozmo2]

I tried but it was not working. Based on your response I thought I posted

in the wrong forum. I found a different path to take for my report. Thanks

for checking.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Mon, May 2, 2016 at 11:17 AM, EddieLotter <forums_noreply@adobe.com>

[EddieLotter]

You're welcome.

Please go ahead and mark your post as the answer to close this thread.

Thanks.

Cheers

Eddie

[Cozmo2]

I get 'no actions are available' under actions.

Bonni

---

Bonni Harris

Database Analyst

Eureka College

300 E. College Avenue

Eureka, IL 61530-1500

309-467-6467

On Mon, May 2, 2016 at 11:31 AM, EddieLotter <forums_noreply@adobe.com>