Custom Order By clause in Select Statment 1

[Theresajn]

I need to create a custom order by. Can icase() be used in an order by clause? If so, what am I missing here? For the life of me, I can't figure it out. I get an error when trying to save the code: Function name is missing ). Any help would be greatly appreciated.

SELECT * FROM RosterReport ORDER BY fldTitle DESC, ICASE(fldYearInSchoolID = 4 AND fldIsPledgeFlag = 0, 4 ;
fldYearInSchoolID = 3 AND fldIsPledgeFlag = 0, 3 ;
fldYearInSchoolID = 2 AND fldIsPledgeFlag = 0, 2, 1) DESC, fldLastName, fldFirstName

I am using VFP 9 SP2.

 

[Mike Lewis]

You can't use an expression with ORDER BY. You have to specify a field, either by name or by number.

The best you can do is to create an intermediate field, containing the result of the ICASE(), then order by that field.

So, for example:

SELECT  fldLastName, fldFirstName, ICASE( blah! blah!) FROM RosterReport ORDER BY 3 DESC

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

My suggestion

SELECT fldLastName, fldFirstName, fldYearInSchoolID FROM RosterReport WHERE fldYearInSchoolID = [i][highlight #FCE94F]YourFilterValue[/highlight][/i] and fldIsPledgeFlag = 0 INTO [i][highlight #FCE94F]Destination[/highlight][/i] ORDER BY 3, 1, 2 DESC

hth

marK

 

[Mike Lewis]

Theresa, you've had two replies to your post. It would be helpful if you could let us know if either of them was any help to you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Theresajn]

Sorry it took me a week to get back to you but I tried Mike's suggestion and it worked great. Thank you so much for taking the time to help me.

T

 

[Mike Lewis]

Glad to hear it is working. Thanks for letting us know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads