myDBR Forums » General discussion

Connected parameter relationship issues

(7 posts) (2 voices)

  1. situ, Member

    Hi

    I'm trying to create connected parameters that independent and related. Let me describe an example to makes this clear. Imagine 4 parameters with the following relationships.

    - Parameter 1 is a simple LOV generated from a query in the database and is required
    - Parameter 2 is defaulted to All but can be filtered by the value of Parameter 1.
    - Parameter 3 is defaulted to All but can be filtered by either Parameter 1 or Parameter 2 or both.
    - Parameter 4 is defaulted to All but can be filtered by any combination of the Parameters 1,2 and 3.

    I believe I can create all the correct parameter queries based on the mydbr_paramX variable.

    ... where if(expr<>0, value=mydbr_param1,1=1) and if(expr<>0, value=mydbr_param2,1=1) .....

    (we pass 0 when All is selected)

    What I am noticing is that in the Parameter entry screen if I select a value for parameter 1 only parameter 2 gets updated. The filter is not pushed down to parameters 3 and 4 even though their queries should allow for this.

    In short parameters have a linear precedence: param 1 controls values in param 2 only, param 2 controls values in param 3 only, param 3 controls values in param 4 only.

    What I am trying to do is have param 1 control values in param 1,2 and 3, param 2 with param 1 control values in params 3 and 4, param 3 with param 1 and and optionally param 2 control values in param 4

    This behaviour can also be seen when modifying the parameter values. If you set all 4 parameters then change the second, the 3rd is cleared and returns to the default and the 4th is unchanged and incorrect with the old value (it is not cleared even though it has a relationship to param 2)

    Maybe this is best described as parameter inheritance.

    Should things work in the way I described?

  2. myDBR Team, Key Master

    The relationships should work as long as you do not have circular referencies. If you have an situation where it does not, let us know.

    --
    myDBR Team

  3. situ, Member

    Having thought about this overnight I think I understand the issue a little more.

    The parameters work correctly. When the filter screen is loaded everything displays correctly with the correct set of values.

    If I change parameter 1 then parameter 2 is updated BUT parameters 3 and 4 are not updated even though they reference parameter 1.

    If I change parameter 2 then parameter 3 is updated BUT parameter 4 is not updated even though it has a reference to parameter 2.

    Param 1:

    select 0,' - select one - ' as ord2,'','2099-12-1' as ord1
    
UNION
    
select lm.id,lm.launch_name as ord2, '', lm.launch_date as ord1 from sipo_db.lm_launches lm
    
order by ord1 desc;

    Param 2:

    select 0,'All' as ord2,'','1' as ord1
    
UNION
    
select dv.business_unit,(select tm.taxvaldesc from sipo_db.tm_m_taxdtls tm where tm.id=dv.business_unit) as ord2,'','2' as ord1 from sipo_db.frf_documents fd
    
JOIN sipo_db.dnrf_prefixes as dp on dp.id=fd.dnrf_id
    
JOIN sipo_db.dnrf_values as dv on dv.id=dp.dnrf_value_id
    
JOIN sipo_db.frf_values as fv1 on fv1.document_id=fd.id and fv1.field_id=140
    
where fv1.value=mydbr_param1
    
group by ord2
    
order by ord1,ord2;

    Param 3:

    select 0,'All' as ord2,'','1' as ord1
    
UNION
    
select dv.major_brand,(select tm.taxvaldesc from sipo_db.tm_m_taxdtls tm where tm.id=dv.major_brand) as ord2,'','2' as ord1 from sipo_db.frf_documents fd
    
JOIN sipo_db.dnrf_prefixes as dp on dp.id=fd.dnrf_id
    
JOIN sipo_db.dnrf_values as dv on dv.id=dp.dnrf_value_id
    
JOIN sipo_db.frf_values as fv1 on fv1.document_id=fd.id and fv1.field_id=140
    
where
    
if(mydbr_param1 <> 0,fv1.value=mydbr_param1,1=1)
    
and if(mydbr_param2<>0,dv.business_unit=mydbr_param2,1=1)
    
group by ord2
    
order by ord1,ord2;

    Param 4:

    select 0,'All' as ord2,'','1' as ord1
    
UNION
    
select dv.minor_brand,(select tm.taxvaldesc from sipo_db.tm_m_taxdtls tm where tm.id=dv.minor_brand) as ord2,'','2' as ord1 from sipo_db.frf_documents fd
    
JOIN sipo_db.dnrf_prefixes as dp on dp.id=fd.dnrf_id
    
JOIN sipo_db.dnrf_values as dv on dv.id=dp.dnrf_value_id
    
JOIN sipo_db.frf_values as fv1 on fv1.document_id=fd.id and fv1.field_id=140
    
where
    
 if(mydbr_param1 <> 0,fv1.value=mydbr_param1,1=1)
    
 and if(mydbr_param2<>0,dv.business_unit=mydbr_param2,1=1)
    
 and if(mydbr_param3<>0,dv.major_brand=mydbr_param3,1=1)
    
group by ord2
    
order by ord1,ord2;

  4. myDBR Team, Key Master

    What you could do first is to turn the parameter queries into procedures. This would rule out any parsing problem with parameters. Also, it would be easier for us to replicate the problem.

    --
    myDBR Team

  5. situ, Member

    I'ver created a procedure as Follows

    BEGIN

select 0,'All' as ord2,'','1' as ord1
    
UNION
    
select dv.business_unit,(select tm.taxvaldesc from sipo_db.tm_m_taxdtls tm where tm.id=dv.business_unit) as ord2,'','2' as ord1 from sipo_db.frf_documents fd
    
JOIN sipo_db.dnrf_prefixes as dp on dp.id=fd.dnrf_id
    
JOIN sipo_db.dnrf_values as dv on dv.id=dp.dnrf_value_id
    
JOIN sipo_db.frf_values as fv1 on fv1.document_id=fd.id and fv1.field_id=140
    
where fv1.value=mydbr_param1
    
group by ord2
    
order by ord1,ord2;

END

    but when I load the report I get the error

    DB error (1054): Unknown column 'mydbr_param1' in 'where clause' SQL: call set1_1_getBusinessUnit;();

    which is referring to the procedure above. I have not defined any input or output parameters in the procedure, my understanding was mydbr would handle the interpreting the mydbr_param1 value

    The parameter query is defined as a popup that calls this procedure set1_1_getBusinessUnit

  6. myDBR Team, Key Master

    You should declare the mydbr_param1 as a parameter so that you can use it in the procedure. myDBR will read the parameter names and creates the popup update hierarchy based on that:

    create procedure set1_1_getBusinessUnit( mydbr_param1 int )
    
BEGIN

select 0,'All' as ord2,'','1' as ord1
    
UNION
    
select distinct dv.business_unit, tm.taxvaldesc, '', '2'
    
from sipo_db.frf_documents fd
    
    JOIN sipo_db.dnrf_prefixes as dp on dp.id=fd.dnrf_id
    
    JOIN sipo_db.dnrf_values as dv on dv.id=dp.dnrf_value_id
    
    JOIN sipo_db.frf_values as fv1 on fv1.document_id=fd.id and fv1.field_id=140
    
    JOIN sipo_db.tm_m_taxdtls tm on tm.tm.id=dv.business_unit
    
where fv1.value=mydbr_param1
    
order by ord1,ord2;

END

    In addition to adding the parameter, you can remove the inline SQL from ord2 (cleaner code) and remove the unnecessary GROUP BY.

    As a rule of thumb, you only use GROUP BY when you have query which returns regular columns and aggregate functions (sum, min, max, avg, count etc). While MySQL allows using GROUP BY without an aggregate function, but in most cases what distinct keyword is the one that should be used (to eliminate duplicate rows).

    --
    myDBR Team

  7. situ, Member

    Ahhh. Now thats a lot better.

    As procedure calls it is now working. Everything updating correctly.

    Thank you


Reply

You must log in to post.