Topic
  • 7 replies
  • Latest Post - ‏2013-12-10T19:49:35Z by smithha
shivaparupalli
shivaparupalli
4 Posts

Pinned topic Converting SQL query into IA rule

‏2013-12-05T16:51:20Z |

Hello all,

I am new to IA and I want to convert the below query logic into IA data rule.Please help me out

 select pos_nbr , count( * )

from 

hr.hr_pa_emp_primary_assignment pa

            where trunc( sysdate ) between pa.start_date and pa.end_date

                and pos_nbr not in( '99999999', '0' )

            group by pos_nbr

            having count( * ) > 1

here sysdate in the query is cutternt date in oracle database.

  • smithha
    smithha
    162 Posts

    Re: Converting SQL query into IA rule

    ‏2013-12-06T13:39:30Z  

    SQL is a specific implementation language for retrieving data from a database, not a rule language.  So, what you want to do is to get back to the actual rule and then proceed forward to the IA rule language.  My approach to converting from SQL is to do the following:

    1) step back out of the SQL and define what condition(s) you are evaluating or testing.  In SQL, this is often in the where clause.

    If I interpret your query correctly, what I see are two or three primary conditions:

    • the position number is valid (i.e. not '99999999' or '0')
    • possibly, that the system date must be between a primary assignment start date and a primary assignment end date
    • possibly, that the position number must be unique

    2) identify any filtering or output criteria

    Questions I have on the query above are:

    • is the date condition a filter condition or a validation condition (i.e. do you only care about records where the system date falls in this date range and ignore other records, or must all records meet this condition)?
    • does the position number grouping reflect an output condition or a validation condition (i.e. do you only want to display records where the count is greater than 1 or is the expected condition that there is a unique position number)

    It's important to clarify these as they impact who you write the rule.

    3) once you've gotten the actual criteria or conditions identified, build those up with the IA rule syntax.

    • If you are filtering out records, you need an IF...THEN clause
    • If you are looking at all records, you will be stringing conditions together with AND or OR
    • You need to ensure you are working with the right data types, so differences will require functions to line those up.
    • For your conditions, you might get the following:
      • IF date() >= pa_startdate AND date() <= pa_enddate
      • THEN pos_nbr NOT in_reference_list {'99999999','0'} 
      • AND pos_nbr unique
    • date() in this case is the function that returns the system date
    • there are several options to test for uniqueness - unique is one option, the count function is another, the occurs check is a third.

    4) after you've saved the rule definition, you generate the executable data rule.  This includes binding columns to the definition variables you've created, adding any join criteria needed, and defining the output columns

    • in the above example there are 3 rule variables:  pa_startdate, pa_enddate, and pos_nbr.  You binding those to the columns in the database
    • if you have a join requirement, you add that by identifying the join keys between the tables
    • for output columns, you would include:  pos_nbr and add an expression with the count function for the position number

    At that point you should be ready to run the data rule.

    Hope that helps!

    Harald

     

  • shivaparupalli
    shivaparupalli
    4 Posts

    Re: Converting SQL query into IA rule

    ‏2013-12-06T15:46:18Z  
    • smithha
    • ‏2013-12-06T13:39:30Z

    SQL is a specific implementation language for retrieving data from a database, not a rule language.  So, what you want to do is to get back to the actual rule and then proceed forward to the IA rule language.  My approach to converting from SQL is to do the following:

    1) step back out of the SQL and define what condition(s) you are evaluating or testing.  In SQL, this is often in the where clause.

    If I interpret your query correctly, what I see are two or three primary conditions:

    • the position number is valid (i.e. not '99999999' or '0')
    • possibly, that the system date must be between a primary assignment start date and a primary assignment end date
    • possibly, that the position number must be unique

    2) identify any filtering or output criteria

    Questions I have on the query above are:

    • is the date condition a filter condition or a validation condition (i.e. do you only care about records where the system date falls in this date range and ignore other records, or must all records meet this condition)?
    • does the position number grouping reflect an output condition or a validation condition (i.e. do you only want to display records where the count is greater than 1 or is the expected condition that there is a unique position number)

    It's important to clarify these as they impact who you write the rule.

    3) once you've gotten the actual criteria or conditions identified, build those up with the IA rule syntax.

    • If you are filtering out records, you need an IF...THEN clause
    • If you are looking at all records, you will be stringing conditions together with AND or OR
    • You need to ensure you are working with the right data types, so differences will require functions to line those up.
    • For your conditions, you might get the following:
      • IF date() >= pa_startdate AND date() <= pa_enddate
      • THEN pos_nbr NOT in_reference_list {'99999999','0'} 
      • AND pos_nbr unique
    • date() in this case is the function that returns the system date
    • there are several options to test for uniqueness - unique is one option, the count function is another, the occurs check is a third.

    4) after you've saved the rule definition, you generate the executable data rule.  This includes binding columns to the definition variables you've created, adding any join criteria needed, and defining the output columns

    • in the above example there are 3 rule variables:  pa_startdate, pa_enddate, and pos_nbr.  You binding those to the columns in the database
    • if you have a join requirement, you add that by identifying the join keys between the tables
    • for output columns, you would include:  pos_nbr and add an expression with the count function for the position number

    At that point you should be ready to run the data rule.

    Hope that helps!

    Harald

     

    Thanks for the help  Harald .

    I will follow all these steps and answer for your ques:i have to care about records where the system date falls in this date range and ignore other records and position number grouping reflects the output conditions and i want to display records where count greater than 1.

  • smithha
    smithha
    162 Posts

    Re: Converting SQL query into IA rule

    ‏2013-12-06T16:04:15Z  

    Thanks for the help  Harald .

    I will follow all these steps and answer for your ques:i have to care about records where the system date falls in this date range and ignore other records and position number grouping reflects the output conditions and i want to display records where count greater than 1.

    So there are two options you can use to filter down to the records in the date range.

    1) Use the IF...THEN logic I noted previously. 

    2) Create a Virtual Table to handle the filtering.  The Virtual Table allows input of a Where condition in the SQL retrieving the data for evaluation.  It does require db specific functions or system date references, but can give you a reduced set of records upfront.

    On the output side, your choices are based on whether the rule met the condition or not, so have to reflect that in the rule logic.  Since you want to only display records where count is greater than 1, you need to code the condition: pos_nbr UNIQUE  then in the output select the Records Not Met option.  Depending on which version of IA you have the options to get distinct records per pos_nbr vary.

  • shivaparupalli
    shivaparupalli
    4 Posts

    Re: Converting SQL query into IA rule

    ‏2013-12-09T19:54:34Z  
    • smithha
    • ‏2013-12-06T16:04:15Z

    So there are two options you can use to filter down to the records in the date range.

    1) Use the IF...THEN logic I noted previously. 

    2) Create a Virtual Table to handle the filtering.  The Virtual Table allows input of a Where condition in the SQL retrieving the data for evaluation.  It does require db specific functions or system date references, but can give you a reduced set of records upfront.

    On the output side, your choices are based on whether the rule met the condition or not, so have to reflect that in the rule logic.  Since you want to only display records where count is greater than 1, you need to code the condition: pos_nbr UNIQUE  then in the output select the Records Not Met option.  Depending on which version of IA you have the options to get distinct records per pos_nbr vary.

    Thanks Harald for your reply.

    I tried exactly same logic you mentioned  and changed the logic in many ways but never got results what I want. I have some questions

    1)Is there any order for rules to be executed(i.e,first i am checking date after NOT in_referencce_list and after uniqueness)

    2)how to check duplicates in a table (results from my sql query are duplicates within the date condition)

  • smithha
    smithha
    162 Posts

    Re: Converting SQL query into IA rule

    ‏2013-12-09T22:35:11Z  
    Thanks Harald for your reply.

    I tried exactly same logic you mentioned  and changed the logic in many ways but never got results what I want. I have some questions

    1)Is there any order for rules to be executed(i.e,first i am checking date after NOT in_referencce_list and after uniqueness)

    2)how to check duplicates in a table (results from my sql query are duplicates within the date condition)

    The components of a rule are evaluated together in the engine, though it will assess an IF clause and filter on that before evaluating a THEN condition.  However, functions and aggregations are calculated before evaluation (to understand more on sequencing see this article: Understand the resource usage of data quality rules in IBM InfoSphere Information Analyzer ) which may then impact how the evaluation considers as true or false.

    So, coming back to your results, I'm not sure whether you are getting too many items grouped together and identified as exceptions or too few, but I would suspect it's something with the count of the position #s.  With the Unique & Occurs conditions and with a general Count(pos_nbr), it is probably aggregating by position number first so that the totals do not necessarily reflect the date range.  You could use a Count with a group by option, but I'm not sure you really have an effective field to group by (i.e. you're kind of grouping based on a True/False condition as to whether the position is in a range, not based on a specific field or a function applied to a field, and I don't think there's any function that will give you that to work with).

    Probably two options to work with, both get you a filtered set in advance so that the number of position numbers is based only on the date range selection.

    1) Create a Virtual Table and incorporate the where clause ( trunc( sysdate ) between pa.start_date and pa.end_date) into VT criteria.  This will only pass those records that meet the criteria into the process so that the count of the pos_nbr is only based on the data subset.  No need to use an IF criteria -- just test  pos_nbr NOT in_reference_list {'99999999','0'} AND pos_nbr unique

    2) If you are using IA 9.1 or higher, create a rule definition that evaluates date() >= pa_startdate AND date() <= pa_enddate.  In this case write out the records that MEET the rule to a Named Output Table.  Then use a second rule to work against the Named Output Table.  Again, no need to use an IF criteria at this point as the data is filtered down to the set of interest.

     

  • shivaparupalli
    shivaparupalli
    4 Posts

    Re: Converting SQL query into IA rule

    ‏2013-12-10T19:43:58Z  
    • smithha
    • ‏2013-12-09T22:35:11Z

    The components of a rule are evaluated together in the engine, though it will assess an IF clause and filter on that before evaluating a THEN condition.  However, functions and aggregations are calculated before evaluation (to understand more on sequencing see this article: Understand the resource usage of data quality rules in IBM InfoSphere Information Analyzer ) which may then impact how the evaluation considers as true or false.

    So, coming back to your results, I'm not sure whether you are getting too many items grouped together and identified as exceptions or too few, but I would suspect it's something with the count of the position #s.  With the Unique & Occurs conditions and with a general Count(pos_nbr), it is probably aggregating by position number first so that the totals do not necessarily reflect the date range.  You could use a Count with a group by option, but I'm not sure you really have an effective field to group by (i.e. you're kind of grouping based on a True/False condition as to whether the position is in a range, not based on a specific field or a function applied to a field, and I don't think there's any function that will give you that to work with).

    Probably two options to work with, both get you a filtered set in advance so that the number of position numbers is based only on the date range selection.

    1) Create a Virtual Table and incorporate the where clause ( trunc( sysdate ) between pa.start_date and pa.end_date) into VT criteria.  This will only pass those records that meet the criteria into the process so that the count of the pos_nbr is only based on the data subset.  No need to use an IF criteria -- just test  pos_nbr NOT in_reference_list {'99999999','0'} AND pos_nbr unique

    2) If you are using IA 9.1 or higher, create a rule definition that evaluates date() >= pa_startdate AND date() <= pa_enddate.  In this case write out the records that MEET the rule to a Named Output Table.  Then use a second rule to work against the Named Output Table.  Again, no need to use an IF criteria at this point as the data is filtered down to the set of interest.

     

    Thanks for all the inputs and help Harald.

    from your previous reply ,second option really worked well.

    first we check the current date condition and loaded into named output table.Second rule applied against named output Table and it worked out.

    Thanks

    Shiva

  • smithha
    smithha
    162 Posts

    Re: Converting SQL query into IA rule

    ‏2013-12-10T19:49:35Z  

    Thanks for all the inputs and help Harald.

    from your previous reply ,second option really worked well.

    first we check the current date condition and loaded into named output table.Second rule applied against named output Table and it worked out.

    Thanks

    Shiva

    Hi Shiva,

    Glad to hear that option worked well for you.

    Harald