Highlighted

Order table by specific values

Explorer ,
Apr 18, 2016

Copy link to clipboard

Copied

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!

TOPICS
Reporting

Views

1.0K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Order table by specific values

Explorer ,
Apr 18, 2016

Copy link to clipboard

Copied

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!

TOPICS
Reporting

Views

1.0K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 18, 2016 0
Advocate ,
Apr 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2016 0
Explorer ,
Apr 19, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2016 0
Advocate ,
Apr 19, 2016

Copy link to clipboard

Copied

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

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2016 0
Explorer ,
Apr 19, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2016 0
Advocate ,
Apr 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2016 0
Explorer ,
Apr 19, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2016 0
Advocate ,
Apr 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2016 0
Explorer ,
Apr 20, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 20, 2016 0
Advocate ,
Apr 20, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 20, 2016 0
Advocate ,
May 02, 2016

Copy link to clipboard

Copied

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

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 02, 2016 0
Explorer ,
May 02, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 02, 2016 0
Advocate ,
May 02, 2016

Copy link to clipboard

Copied

You're welcome.

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

Thanks.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 02, 2016 0
Cozmo2 LATEST
Explorer ,
May 02, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 02, 2016 0