0
524

Written by

Nezla
Follow
Question Nezla · Apr 5, 2023

SQL query in Dropdown

#Ensemble

Hi Guys, 

I have two dropdowns where if I select a type from the first dropdown I would like to populate all subtypes in the second dropdown as below (I've set showquery =true to show you what's going on )

but for some reason I'm not getting the query filtering to the required where clause.

eg. by select Bearing from the first dropdown I should only get 6 records in subtype dropdown, but I'm getting all subtypes 

here is my code:


<dataCombo id="Type" name="Type"  align="left" size="25" maxRows="400" 
sql="Select ID,Component from MSDS_Common.ComponentType where Active=1"
searchKeyLen="10"
editable="true"
required="false" 
showEmpty="true"
displayColumns="1,2"
choiceColumn="2"
valueColumn="2"
dropdownWidth="180px" 
onchange="zenPage.cbGetSub(zenThis);">
<parameter value='?' />
</dataCombo>
<dataCombo id="SubType2" name="SubType2"  align="left" size="25" maxRows="400" 
sql="Select ID, Name from MSDS_Common.ComponentSub Where (? is null or %UPPER(Component)[%UPPER(?)) and Active=1"
searchKeyLen="10"
dropdownHeight="600"
editable="true"
required="false" 
showEmpty="true"
displayColumns="1,2"
choiceColumn="2" showQuery="true"
valueColumn="1"
dropdownWidth="180px" onchange="">
<parameter/>
</dataCombo>
 

ClientClassMethod cbGetSub(elm) [ Language = javascript ]
{
        zenPage.getComponentById('SubType2').setProperty('parameters',1,elm.value);
        zenPage.getComponentById('SubType2').setProperty('parameters',2,elm.value);
        zenPage.getComponentById('SubType2').executeQuery();
}
 

So what am I doing wrong?

Thanks

Product version: Caché 2014.1
Discussion (2)1
Log in or sign up to continue

Comments

Timo Lindenschmid · Apr 5, 2023

So just looking at your SQL,. You got 2 parameters in your where clause.

Select ID, Name from MSDS_Common.ComponentSub

 Where 

    (? is null or %UPPER(Component)[%UPPER(?))

    and Active=1

If the first dropdown is null this will always be true if Active is 1

Try changing the where clause to

 Where 

    (? is null AND ( %UPPER(Component)[%UPPER(?))

    and Active=1

This will check if first parameter is empty and if subcomponent contains the second parameter and if its active.

0
Nezla  Apr 6, 2023 to Timo Lindenschmid

Thanks you very much Timo that worked but this is strange it working in the dropdown but if I ran the same query in SMP !, what the first parameter means by empty which field is empty (null)?

Thanks

Thanks

0