SQL query in Dropdown

Question

Question

Rochdi Badis · Apr 5, 2023

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:

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

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 0

score 0 · Answer 1 · 2023-04-06T01:37:20-04:00

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