Sorting claims

[shangrilla]

Table1

Provider Year Type PatNo PatName
111 2000 21 78677 Thomas, James R
111 2000 21 88656 Daniel, Eric
111 2001 31 88889 Som, Tom
131 2002 41 77660 XXX, YYY

I sort the data like
select * from Table1 order by Provider, Year, Type, (PatNo or PatName)

For some reason the it doesn't want to sort data by PatName or PatNo.
??

 

[rgbean]

What criteria do you want to use whether to use Patno or PatName? It's likely you can only sort on one field or the other as they don't appear to be similar data.

Note: Normally you can only Order By on a selected column.

Rick

 

[DSummZZZ]

Just to expand on Rick's post, since PatNo looks to be numeric, maybe character, and PatName looks to be character, neither of which are of type locical, and you are effectively running the query on a boolean expression, not a column, (PatNo or PatName), you will get an error.
So you will need to change it to run on either PatNo or PatName or even both:

select * from Table1 order by Provider, Year, Type, PatNo
-or-
select * from Table1 order by Provider, Year, Type, PatName
-or-
select * from Table1 order by Provider, Year, Type, PatNo, PatName
Dave S.

 

[wjs2]

Shangrilla,

Assuming you are trying to do this either or thing on the fly, you need a method of choosing one or the other. That leaves you with several options for the selection. A check box, radio button, whatever. Then an If, do case, what ever or macro substitution, which ever suits your need. You could obtain all of the sort fields from a form that allows the user to select what ever thay want. That way you don't get stuck when the user wants something entirely different from what you have preprogramed. The method of obtaining which fields to use for the sort depends what you are attempting to accomplish, with or without user intput.

Bill