Been there, done that - and none of gives me the result I need. Why doesn't changing the SQL to

SELECT LName, FName, Mi, ThID, iif(empty(neSigOpt),1,neSigOpt), lPin, lFingerReg, ;
  PADR(ConfirmType(neSigOpt, lPin, lFingerReg), 30) AS ConfData, isactive, lOverride ;
FROM Ther ;
&lcFilter ;
INTO CURSOR SigTypeR;
 ORDER BY isActive, neSigOpt DESC, 8, LName, FName, Mi, ThID

make no difference? One would think that the names within the neSigOpt = 1 block would be list alphabetically, but they're not.


>Change the order of the elements after ORDER BY according to your need

>....ORDER BY neSigOpt DESC,LName, FName,isActive,  8,  Mi, ThID

>
>>Unfortunately, the customer wants it sorted in blocks by the neSigOpt and then by name.
>>
>>>You must change the ORDER BY clause, something like

>>>SELECT LName, FName, Mi, ThID, neSigOpt, lPin, lFingerReg, ;
>>>  PADR(ConfirmType(neSigOpt, lPin, lFingerReg), 30) AS ConfData, isactive, lOverride ;
>>>FROM Ther ;
>>>&lcFilter ;
>>>INTO CURSOR SigTypeR;
>>>  ORDER BY LName, FName,isActive, neSigOpt DESC, 8,  Mi, ThID

>>>
>>>>I inherited an SQL statement
>>>>
>>>>

>>>>SELECT LName, FName, Mi, ThID, neSigOpt, lPin, lFingerReg, ;
>>>>  PADR(ConfirmType(neSigOpt, lPin, lFingerReg), 30) AS ConfData, isactive, lOverride ;
>>>>FROM Ther ;
>>>>&lcFilter ;
>>>>INTO CURSOR SigTypeR;
>>>>  ORDER BY isActive, neSigOpt DESC, 8, LName, FName, Mi, ThID
>>>>

>>>>where lcFilter = "Where (neSigOpt = 2 or neSigOpt .LessThan. 2) AND IsActive"
>>>>
>>>>What we're seeing is:
>>>>
>>>>

>>>>Lname, Fname        neSigOpt (Not Diplayed)             neSigOpt Translation (displayed)
>>>>AA, Abel                     3                                 PIN
>>>>BB, Betty                    3                                 PIN
>>>>CC, Cain                     3                                 PIN
>>>>...                          3                                 PIN
>>>>AA, Andy                     2                                 Fingerprint
>>>>...
>>>>ZZ,Zed                       2                                 Fingerprint
>>>>AA, Aaron                    1                                 Not Authorized
>>>>..
>>>>XX, Xena                     1                                 Not Authorized
>>>>AA, Annie                 (Blank)                              Not Authorized
>>>>...
>>>>

>>>>I've tried manipulating the neSigOpt field with iif(empty(neSigOpt),1,neSigOpt) as neSigOpt - but it seems to make no difference.
>>>>
>>>>What I need is AA, Annie to follow AA, Aaron, etc
>>>>
>>>>Suggestions?