Topic
  • 2 replies
  • Latest Post - ‏2013-08-05T11:21:50Z by v-michael
v-michael
v-michael
7 Posts

Pinned topic Prohibit TBSM from changing SQL queries

‏2013-07-31T06:21:24Z |

Hello!

When using SQL data fetchers with incoming status rules, I noticed that TBSM tries to embed threshold filters inside SQL logic. It is successfull when SQL is very simple. But when the custom SQL contains grouping function or subquery, the TBSM places its conditions in wrong place of the query.

Because of this, those rules aren't working.

I can't create any views in production system just for simplifying queries.

Is there a method for disabling TBSM to break custom queries?

Mikhail

  • randybrown
    randybrown
    60 Posts
    ACCEPTED ANSWER

    Re: Prohibit TBSM from changing SQL queries

    ‏2013-08-02T12:01:36Z  

    Hello,

    I have seen a problem with the "Preview data..." button for incoming status rules when using an SQL data fetcher. As you have determined, TBSM just adds a where clause expression to to the end of the SQL statement. There is no attempt to parse the SQL to properly place the where clause in this preview function. This is a limitation for preview.

    However, I thought this technique was only used for the "Preview data...". I did not think it affected the rule at runtime, since the data from the fetcher is evaluated using the Impact expression handling which TBSM includes. I need more details to understand if this is causing a loss of function for evaluating the rows from the fetcher at runtime.

    You may want to consider adding a view to your database that captures the complexity of your query and allows the fetcher to make a simple query against the view. Or you could enclose your complex query as a subquery, something like "select * from (select .... from ... group by ...) for a more immediate solution.

    Hope this helps...

     

    Randy Brown

  • randybrown
    randybrown
    60 Posts

    Re: Prohibit TBSM from changing SQL queries

    ‏2013-08-02T12:01:36Z  

    Hello,

    I have seen a problem with the "Preview data..." button for incoming status rules when using an SQL data fetcher. As you have determined, TBSM just adds a where clause expression to to the end of the SQL statement. There is no attempt to parse the SQL to properly place the where clause in this preview function. This is a limitation for preview.

    However, I thought this technique was only used for the "Preview data...". I did not think it affected the rule at runtime, since the data from the fetcher is evaluated using the Impact expression handling which TBSM includes. I need more details to understand if this is causing a loss of function for evaluating the rows from the fetcher at runtime.

    You may want to consider adding a view to your database that captures the complexity of your query and allows the fetcher to make a simple query against the view. Or you could enclose your complex query as a subquery, something like "select * from (select .... from ... group by ...) for a more immediate solution.

    Hope this helps...

     

    Randy Brown

  • v-michael
    v-michael
    7 Posts

    Re: Prohibit TBSM from changing SQL queries

    ‏2013-08-05T11:21:50Z  

    Hello,

    I have seen a problem with the "Preview data..." button for incoming status rules when using an SQL data fetcher. As you have determined, TBSM just adds a where clause expression to to the end of the SQL statement. There is no attempt to parse the SQL to properly place the where clause in this preview function. This is a limitation for preview.

    However, I thought this technique was only used for the "Preview data...". I did not think it affected the rule at runtime, since the data from the fetcher is evaluated using the Impact expression handling which TBSM includes. I need more details to understand if this is causing a loss of function for evaluating the rows from the fetcher at runtime.

    You may want to consider adding a view to your database that captures the complexity of your query and allows the fetcher to make a simple query against the view. Or you could enclose your complex query as a subquery, something like "select * from (select .... from ... group by ...) for a more immediate solution.

    Hope this helps...

     

    Randy Brown

    Hello Randy,

    TBSM has some logic to determine the place where its conditions must be inserted. When I use subquery in data fetcher TBSM tries to insert its text inside internal query, not at the end of the text.

    Anyway, if I skip the error with "Preview data" according your recomendation, incoming status rule seems starts working.

    Thanks for your help!

    Mikhail