Greetings,
In version 4.1.5 there is a limit to the amount of dimension values one can place a query condition on. I believe this limit is 155 values. Does anyone know if this is still the case in version 5?
TIA,
Dave
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-01-26 19:33:34 EST, you write:
In version 4.1.5 there is a limit to the amount of dimension values one can place a query condition on. I believe this limit is 155 values. Does anyone know if this is still the case in version 5?
Interesting… I had never heard of this as a limitation. Just to check, I created a query and was able to create 160 conditions. I got bored at that point and quit, but it appeared that I would have been able to continue…
I am willing to bet that there is not a limit on the number of conditions, but that there is a limit on the size of the SQL that is ultimately generated. I am not aware of any database that would limit the amount of work done in a where clause, but most databases limit the size of a single block of SQL code.
You could check this quite easily. Open your query that has 155 conditions and try to add more result objects. If that is not allowed, then your SQL code has reached the size limit. In other words, you have not reached a limit on conditions, but rather the amount of text that can be generated in the SQL.
If that is the case, then version 5.0 will not improve, as your database engine will be the same.
If this is not the case, I can’t imagine why this limitation would exist.
I’m really curious: what sort of query do you have that requires over 150 conditions? Have you considered making some predefined conditions that might include more complex data logic? Or using a sub-query? Or joining to a lookup table?
I can’t imagine sitting down with a user and telling them they have to create 155 (or more!) conditions to get a correct answer…
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
Dave,
Thanks for your reply. I think my initial posting was a little vague so let me be more clear. I’m not talking about a limit of 155 DIFFERENT conditions. I’m talking about creating ONE condition on multiple values in the dimension. For example, in our warehouse we have a dimension called Case Worker. There are about 1,000 case workers in this dimension table. So I create a query using Case Worker and then right click on the Case Worker object and select Apply Simple Condition. At this point a list of all 1,000 case workers applies. Here is where the limit of 155 values comes into play. If I try to select more than 155 Case Workers to ultimately create one condition it gives me a message that I’ve selected too many values.
I’m not sure if this is a SQL thing or BO?
Listserv Archives (BOB member since 2002-06-25)
This was the question:
Thanks for your reply. I think my initial posting was a little vague so let me be more clear. I’m not talking about a limit of 155 DIFFERENT conditions. I’m talking about creating ONE condition on multiple values
in
the dimension. For example, in our warehouse we have a dimension called
Here is the ans:
The answer is: There is a limit of 99 values in a condition if you have not set any paramater in prm file.
Cause 1 :From a list of values in the Query Panel, you attempted to select more than the
authorized number of values. By default, you can select up to 99 values at the
same time.
Action: Limit the number of values you select to less than 99. Cause 2 You selected less than 99 values but the above error appears. This means that a
restriction has been set in the prm file corresponding to your database. This
restriction appears as the parameter MAX_INLIST_VALUES. Action Remove this parameter, or set its limit to a higher number.
Hope it works.
-Chandra
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-01-28 12:22:32 EST, you write:
I’m not talking about a limit of 155 DIFFERENT conditions. I’m talking about creating ONE condition on multiple values in the dimension. For example, in our warehouse we have a dimension called Case Worker. There are about 1,000 case workers in this dimension table. So I create a query using Case Worker and then right click on the Case Worker object and select Apply Simple Condition. At this point a list of all 1,000 case workers applies. Here is where the limit of 155 values comes into play. If I try to select more than 155 Case Workers to ultimately create one condition it gives me a message that I’ve selected too many values.
I’m not sure if this is a SQL thing or BO?
Ah, 155 different VALUES in one CONDITION… that makes more sense… 
There is a limit - again, it is a SQL limit. For some databases the limit is 255, for others it is less. The limitation is based on the number of values allowed in the “In” clause.
This item can be adjusted in BusObj to match your database. To be honest, I think you are probably supposed to be able to do 255 and someone mis-typed the value as 155 in the config file.
I don’t recall exactly which version allows you to specify this value.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)