Dynamic Sorting

[Dawn M]

Here's my situation.

I am creating a temp-table that will be holding data. I need to create a secondary "reference" temp-table that will hold the order which it is displayed.

I need to create this reference temp-table based on a combination of TWO sorts entered by the user. Each sort has TEN possible selections.

The long, tedious, inefficient way to do it would be this....

if xsort1 = "SomeSort" then do:

if xsort2 = "AnotherSort" then do:
for each tt-data break by field-1 by field-2:
create tt-order.
....
end.
end.

if xsort2 = "ADifferentSort" then do:
for each tt-data break by field-3 by field-4:
create tt-order.
...
end.
end.

...

end. /* if xsort1 = "SomeSort" */

else if xsort2 = "SecondSort" then do:

/*** SAME CODE AS ABOVE, REPEATED ***/

end. /* else if xsort2 = "SecondSort" */

THERE HAS GOT TO BE A BETTER WAY!!!!

Anybody have something slicker that will work in my situation?

Thanks in advance....

 

[plus_marca]

Hi.

You can try something like this.

DEFINE VARIABLE L-qhandle AS HANDLE NO-UNDO.
DEFINE VARIABLE L-bhandle AS HANDLE NO-UNDO.
DEFINE VARIABLE L-fdhandle AS HANDLE NO-UNDO.
DEFINE VARIABLE L-i AS INTEGER NO-UNDO.
DEFINE VARIABLE L-tabla AS CHARACTER NO-UNDO.
DEFINE VARIABLE L-campo AS CHARACTER NO-UNDO.
DEF VAR field-1 AS c.
DEF VAR field-2 AS c.

L-tabla = "P-detlista".
field-1 = "Plis-clave".
FIELD-2 = "Pdli-horas".

CREATE QUERY L-qhandle.
CREATE BUFFER L-bhandle FOR TABLE L-tabla.

L-qhandle:SET-BUFFERS(L-bhandle).
L-qhandle:QUERY-PREPARE("FOR EACH " + L-bhandle:NAME + " by " + field-1 + " by " + field-2).
L-qhandle:QUERY-OPEN.
REPEAT:
L-qhandle:GET-NEXT.
IF L-qhandle:QUERY-OFF-END THEN LEAVE.
DISPLAY L-bhandle:BUFFER-FIELD(1):BUFFER-VALUE
L-bhandle:BUFFER-FIELD(2):BUFFER-VALUE with down.
down.
END. /* repeat */
L-qhandle:QUERY-CLOSE.
DELETE OBJECT L-qhandle.
DELETE OBJECT L-bhandle.

 

[TomBascom]

If the sort options are known in advance you can use a static query. Something along these lines:

 define query q for tt_xstat.

 case sort-criteria:
   when "l" then open query q for each tt_xstat no-lock by tt_xstat.stat1[x]   descending.
   when "w" then open query q for each tt_xstat no-lock by tt_xstat.stat2[x]   descending.
   when "h" then open query q for each tt_xstat no-lock by tt_xstat.stat-ratio descending.
   when "L" then open query q for each tt_xstat no-lock by tt_xstat.stat1[x].
   when "W" then open query q for each tt_xstat no-lock by tt_xstat.stat2[x].
   when "H" then open query q for each tt_xstat no-lock by tt_xstat.stat-ratio.
   when "n" then open query q for each tt_xstat no-lock by tt_xstat.xname.
   when "N" then open query q for each tt_xstat no-lock by tt_xstat.xname      descending.
   when "#" then open query q for each tt_xstat no-lock by tt_xstat.xid.
   when "-" then open query q for each tt_xstat no-lock by tt_xstat.xid        descending.
   otherwise     open query q for each tt_xstat no-lock by tt_xstat.stat1[x]   descending.
 end.

 do while true:

   get next q.

   if not available tt_xstat then leave.

   /*** whatever... ***/

 end.

 

[Dawn M]

Thanks for your responses, gentlemen. I think I can use either one of these solutions to meet my needs.

 

[Dawn M]

One last thing...

I need to incorporate some "last-of" logic on this query. How do I do that using dynamic queries?

 

[Casper]

KB 20295: http://tinyurl.com/hsayt
KB 20296: http://tinyurl.com/flbsa

Regards,

Casper

 

[Dawn M]

Thanks Casper!!!