/**
** mssql DDL generated by MDBTools for view AL_RECONCILE_CRITERIA_VIEW on Dec 4, 2009 1:20:47 PM
**/

CREATE
VIEW al_reconcile_criteria_view ( task_uuid, rule_id, position, own_resource_field_name, discovery_field_name, operator_type_indicator, tenant, trim_disc_left, trim_disc_right, trim_own_left, trim_own_right, ocolumn, dcolumn ) AS
Select
                           rrule.task_uuid task_uuid,
                           crit.criteria_id rule_id,
                           crit.position,
                           crit.attribute_name own_resource_field_name,
                           crit.value discovery_field_name,
                           (Case
                              WHEN Left_Paren = 0 and Right_Paren = 0 and connector_id = 0 then 0
                              WHEN Left_Paren = 0 and Right_Paren = 1 and connector_id = 0 then 5
                              WHEN Left_Paren = 1 and Right_Paren = 0 and connector_id = 0 then 0  --- should not exist
                              WHEN Left_Paren = 1 and Right_Paren = 1 and connector_id = 0 then 0                              
                              WHEN Left_Paren = 0 and Right_Paren = 0 and connector_id = 1 then 1
                              WHEN Left_Paren = 0 and Right_Paren = 1 and connector_id = 1 then 6
                              WHEN Left_Paren = 1 and Right_Paren = 0 and connector_id = 1 then 3
                              WHEN Left_Paren = 1 and Right_Paren = 1 and connector_id = 1 then 1                              
                              WHEN Left_Paren = 0 and Right_Paren = 0 and connector_id = 2 then 2
                              WHEN Left_Paren = 0 and Right_Paren = 1 and connector_id = 2 then 7
                              WHEN Left_Paren = 1 and Right_Paren = 0 and connector_id = 2 then 4
                              WHEN Left_Paren = 1 and Right_Paren = 1 and connector_id = 2 then 2        
                           END) operator_type_indicator,                           
                           t.tenant tenant,
                           rrule.trim_disc_left trim_disc_left,
                           rrule.trim_disc_right trim_disc_right,
                           rrule.trim_own_left trim_own_left,
                           rrule.trim_own_right trim_own_right,
                           ofield.value ocolumn,
                           dfield.value dcolumn  
                          from
                            al_criteria_def crit
                            inner join AL_RECONCILE_RULE_CRITERIA rrule
                                ON crit.criteria_id = rrule.criteria_id
                            JOIN arg_sl_own_match_fields ofield
                                ON (crit.attribute_name = ofield.id)
                            JOIN arg_sl_dis_match_fields dfield
                                ON (crit.value = dfield.id)
                            join arg_reconcile_task t
                                on t.task_uuid = rrule.task_uuid
GO