break by

[anandknr]

hi all,
I am very new to progress so please help to find the solution .
I have a db table with 4 fields ....
Table - student
columns - rollno,name,std,grade.

now i have to display sorted listed of students but sorting condition is to be choose d by user through a drop down. So i need to change the ' break by ' field accordingly.
here is the logic i used .

def var sortParam as character no-undo .
sortParam = /* gets drop down selected field value to sort */

if sortParam = "rollno" then do:
for each student by rollno :
/* dispaly to user*/
end .
end .

if sortParam = "name" then do:
for each student by name :
/* dispaly to user*/
end .
end .

if sortParam = "std" then do:
for each student by std :
/* dispaly to user*/
end .
end .

the problem here is that ,a lot of code redundancy is here as i am using same display code in all for each loop.
Can any one suggest a better method for this .

 

[MaximMonin]

you have to use dynamic queries it this case.


define variable sqlquery as character.
define variable tablename as character initial "student".
sqlquery = "FOR EACH " + TableName + " NO-LOCK".
filtertext = trim(filtertext).
if filtertext <> "" then
sqlquery = sqlquery + " where " + filtertext.
if trim(sortby) <> "" then
sqlquery = sqlquery + " by " + TableName + "." + sortby.


CREATE QUERY hQueryData.
CREATE BUFFER hBufferData FOR TABLE TableName.
hQueryData:SET-BUFFERS(hBufferData).
hQueryData:QUERY-PREPARE(sqlquery).
hQueryData:QUERY-OPEN.
hQueryData:GET-FIRST ().
repeat:
if hQueryData:QUERY-OFF-END then leave.

hQueryData:GET-NEXT ().
end.

 

[Stefan]

And remember that if you create a dynamic object (the query and the buffer in this case) you are responsible for cleaning it up!

You can also combine dynamic queries with static buffers, possibly making it easier to keep your original display code.

I prefer a different looping mechanism

...

CREATE QUERY hQueryData.
hQueryData:SET-BUFFERS( BUFFER student:HANDLE ).
hQueryData:QUERY-OPEN().
DO WHILE hQueryData:GET-NEXT():
  /* do your stuff */
END.

DELETE OBJECT hQueryData.