Is there any way to make this statement without using IFs?
 

Let(
[
$country = "IT";
$region = "Toscana";
$subregion = "";
$town = "";
$result = ExecuteSQL ( 
"SELECT Ref 
FROM RealEstate
WHERE Country = ? " & 
If(IsEmpty($region);" AND (Region is null)";" AND (Region = '" & $region & "')") & 
If(IsEmpty($subregion);" AND (SubRegion is null OR SubRegion is not null)";" AND (SubRegion = '" & $subregion & "')") & 
If(IsEmpty($town);" AND (Town is null OR Town is not null)";" AND (Town = '" & $town & "')");
"" ;
"" )];
$result
)

 

I use a tbl.Criteria table to store multiple records of rules, that I then use to select various groups of real estate listings for a portal export.

Due to the way the portal is set up I need to match how they define certain places and supply that data. The Country is always required.

However, they define some places without regions or subregions or town names which makes using a parameterised ExecuteSQL hard or impossible it seems.

For instance, if the rule is for Vilamoura I need to select only those listings that are defined as having PT, Algarve, no subregion, Vilamoura.

For the rule for Tuscany, I need to select listings that have IT, Toscana but any subregion or town.

And the rule for Bergerac should only select properties that are explicitly FR, Aquitaine,Dordogne,Bergerac.

 

Here's three records from tbl.Criteria:

Country	Region	SubRegion	Town
FR	Aquitaine	Dordogne	Bergerac
PT	Algarve		Vilamoura
IT	Toscana

As you can see sometimes there are gaps, and they need to be there in order to match to Rightmove's definition of different places.

Here's some records from tbl.RealEstate

Ref	Country	Region	SubRegion	Town
123	FR	Aquitaine	Dordogne	Jarnac
456	PT	Algarve		Vilamoura
789	IT	Toscana

 

I know I can probably set up IFs to check which kind of search it is in order to create multiple ExecuteSQL statements, but maybe there's a more efficient way to do this in one sitting?

I have spent hours fiddling around and Googling.

Let( [  /*presumably these are just placeholders for your fields*/
aCountry = "IT";
aRegion = "Toscana";
aSubregion = "";
aTown = "";

query =  
"SELECT Ref
FROM RealEstate
WHERE Country = " & aCountry &

If( not IsEmpty(aRegion) ; " AND Region = " & aRegion ) &
If( not IsEmpty(aSubregion) ; " AND SubRegion = " & aSubregion ) &
If( not IsEmpty(aTown) ; " AND Town = " & aTown )

; result =

ExecuteSQL( query ; "" ; "" )

];
result
)

Hi,

I've been doing SQL for years and don't understand why I couldn't simplify it to that myself!

I guess working 12-13 hour days have been catching up with me!

However, I was really looking to parameterise it if possible.

Is it possible?

Edited December 16, 20196 yr by V8S

I suppose you could do something like:

Let( [
aCountry = "IT";
aRegion = "Toscana";
aSubregion = "";
aTown = "";

query =  
"SELECT Ref
FROM RealEstate
WHERE Country = ?" &

If( not IsEmpty(aRegion) ; " AND Region = ?" ) &
If( not IsEmpty(aSubregion) ; " AND SubRegion = ?" ) &
If( not IsEmpty(aTown) ; " AND Town = ?" )

; result =

ExecuteSQL( query ; "" ; "" ;
aCountry ;
If( not IsEmpty(aRegion) ; aRegion ) ;
If( not IsEmpty(aSubregion) ; aSubregion ) ;
If( not IsEmpty(aTown) ; aTown )
  )

];
result
)

But I'm not sure you gain anything.

Welcome to the forums by the way!