Hi

We have a customized session that contains extra attributes about our items. The users are able to run a filter on items by selecting up to 5 attributes to search on. It works perfectly but the speed is not great and the users, as always, are complaining. I am not sure how to speed it up with out changing the whole design of the session. It gets all its data from one table. The table has item no, attribute code and response so we would have

Item: 00008
attribute code: 10
response: bottle

Then for the next attribute
Item: 00008
attribute code: 20
response: 500ml

So we currently have 5 sql statement that run depending on how many attributes they filter on. I don't think we can run it all in one sql statment as they search on the same index ie.

z.subq(1,ff) ="select tczzz016.* from tczzz016 where (tczzz016._index3 = {" & str$(tczzz025.attr) & ", '" &
strip$(tczzz025.resp) & "',:1}) "

I have been racking my brains thinking about using DISTINCT in the sql but have hit a dead end. Has anyone got any ideas on this to keep me thinking?

Are you trying to create output for a report, use the SQL in a query.extension declaration for a form, or something else? I think my approach might differ depending on what I was trying to accomplish.

If I was trying to build it into one big SQL statement (perhaps for query.extension, for example), I might keep appending AND EXISTS (SELECT ...) clauses to the where statement. You could do this programmatically. I guess it would depend on how your users entered data. So, you might get something like this:


select tiitm001.*
from tiitm001
where EXISTS (SELECT tczzz016.* FROM tczzz016 WHERE tczzz016._index1 = {tiitm001.item, :attrib1, :value1})
and EXISTS (SELECT tczzz016.* FROM tczzz016 WHERE tczzz016._index1 = {tiitm001.item, :attrib2, :value2})
...
selectdo


The attribute and value variables could be replaced by actual values, especially in the case of query.extension. I also just arbitrarily used tiitm001, but you could use any table.

In other instances, you could use nested SELECTs, where each one would further refine your selection:

select tczzz016.*
from tczzz016
where tczzz016._index3 = {:attrib1, :value1}
selectdo
if not isspace(attrib2) then
select tczzz016.item
from tczzz016
where tczzz016._index1 = {:tczzz016.item, :attrib2, :value2}
as set with 1 rows
selectdo
...
do something with result
endselect
endif
endselect


Ultimately, you may have to do a little testing to see what approach works better. I'm sure there are other ways to do this, too...

Thanks for the help Evesely

This is a subsession that a user enters certain attributes in (up to 5 of them) and it uses the returned items to populate a form with the results.

I see how I can use the solutions you gave me. Our curent script brings in the attributes and responses into an array z.subs(1,ff) as shown in my first post.

I am trying to work out how to use your method with the index equal to the values in an array? I am thinking I will have to create a new array for th attributes and responses to hold them?

Not sure I am making sense?

I still am not certain exactly how your sessions work. However, since the attributes and values they enter on the form are external variables, you may be able to use an import or export to get there values into the other session (I'm not sure if the criteria entry is on the main session or subsession) without creating more arrays. Are you using query.extension or some other method to limit what is displayed on your form?

Good luck!:)

The session displays all the attributes about the item on a form. You select filter and a blank version of the form appears. The user makes the selection of attributes and repsonse he wishes to run a filter from and these get put into a seperate table. The current script then reads these out into a string array to create the sql arguments. When running the sql any qualifying items get inserted into another new table. Upon exiting the filter the main session imports the items from this other table and uses them to populate the main form.

I am trying to use your way to redo the script but am battling as the user can enter anywhere between 1 and 5 responses to filter on.

OK, I got it. I'm not sure why the attributes and values must get stored in a table and not used directly, but you know your script better than I do.

Well, you could use the nested SQL statements I showed in my second example. When you run out of criteria or hit the last selectdo, you add the item to the table. For example:

select tczzz016.*
from tczzz016
where tczzz016._index3 = {:attrib1, :value1}
selectdo
if not isspace(attrib2) then
select tczzz016.item
from tczzz016
where tczzz016._index1 = {:tczzz016.item, :attrib2, :value2}
as set with 1 rows
selectdo
...
<add tczzz016.item to table>
endselect
else
<add tczzz016.item to table>
endif
endselect


A more elegant (and flexible) way to do this would be to use recursion. I know recursion is a somewhat dirty word in Baan, but you can use it under some circumstances (using global variables, no parameters, etc.), and this may be a good case.

Hi Evesely

Thanks for your help I see know how it will work via your solution. The only thing I am struggling with is getting the attributes and responses that the user enters into variables. You do not know how many responses a user will search on so the current script has a select statement extracting them from the filter table to an array. You do not know how many responses there will be and ideally you would want to get the attributes and responses into attrib1, value 1 and attrib2, value 2, etc.

Would I need to extract them into two arrays, one for the attrib and one for the value/response and then extract the array into variables attrib1 and value1, etc?

Sorry for all the questions.

Oh I should have mentioned that the table contains all the attributes on it and the user picks the values he wants from there so you do not know which attribute number he has entered a value for off hand. The table has all the attributes in it and values for those they have entered. eg. The table could be

item 00008
attribute 10
value
if no value was entered or if they did enter one it would read

item 00023
attribute 80
value bottle

so you need to find out what attributes they have entered data for thats whats got me stumped.

Thanks

OK, I think I am confused again. Let's list the tables and sessions to make sure we are clear. Are these right:

Tables:

List of all item/attribute/value combinations (tczzz016?)
User selections/filters (attribute, value)
Selected Items (after querying)


Sessions:

Main session allows filter selection (up to 5 attribute/value pairs)
subsession displays resulting list of filtered items


I think I am missing something based on the last part of your previous post. Please correct my assumptions, and then I may be able to help more.

Sorry if I'm being dense.:p