Sorry for misunderstanding: fields CountryName and CityName are in single table
SQL:
select country_id,
dbo.GetCountryName(country_ID),
dbo.GetCityName(country_ID)
from country
Physically there are two tables, but we need to use user functions to retreive city caption, so we can’t write SQL like this:
select t1.countryName, t2.cityName
from country t1, city t2
where t1.country_id=t2.country_id
We need exactly this structure of universe (city names are selected by some criteria in our GetCityName function)
I have a similar situation in my BU. I utilize “Cascading Prompts” which should work for you as well.
Our structure is Business Units -> Market Units -> Locations. I built these prompts to be used in conjunction with an “ALL” option. However, I will just cover cascading prompts. Here’s how to do Cascading Prompts:
[list]In the second level of your hierarchy build a SQL-driven LOV in Designer. In my case, MU would have a condition of BU. Here’s the SQL:
SELECT DISTINCT
FLGL.FLGL_PBG_STRUCT5D.FLEX_VALUE_MU,
FROM
FLGL.FLGL_PBG_STRUCT5D
WHERE
(
FLGL.FLGL_PBG_STRUCT5D.FLEX_VALUE_BU = @variable('Enter BU')
AND (( FLGL.FLGL_PBG_STRUCT5D.FLEX_VALUE_5 In ('USAT','UNID','USA1') ))
)
)[/list]
[list]For you next level, you would do the same but this time add the BU and the MU as prompted conditions, BU and MU.[/list]
To an end-user it may appear a little screwy because they would have to reselect BU for for a list of MU’s and MU for a list of locations, but with a little education, my user base was able to deal with it.