Workflow5 equivalent logic to workflow4 sql command statement for number range searches
Hi,
I have an existing workflow4 form where the user can search for an address. The idea being that they enter in 1 number eg 6 for the house number. Behind the scenes, the workflow checked if the number the user entered was odd or even and did a similar filter on the sql table for only odd or even values and then checked if the number the user entered is within the house number range (eg 4-20 Eton Street would be found by a user entering in 4 or 6 or 8 or 20 etc but not 5 or 7).
The table has a house_no from field and a house_no to field so that it can allow for searching on range house numbers like 4-20. If the user types in 6 they don't want to get the odd number matches for the other side of the street. Just for clarity - odd numbers are on one side of the street and even on the other (they can't be mixed) - not sure if this is a worldwide standard.
The workflow4 uses a command as per below in the sql query activity:
select distinct A.master_land_no
from gisesri.ODS_PRP_P_LAND_PROP_ADDRESS A
where
(
(A.Street = @streetname OR @streetname is null) AND
(A.house_no=@starthouseno OR (
(CASE WHEN (CAST((cast(A.house_no as decimal (6,1))/2) as nvarchar(20))) like '%.5%' THEN 'odd' else 'even' end )=
(CASE WHEN (CAST((cast(@starthouseno as decimal (6,1))/2) as nvarchar(20))) like '%.5%' THEN 'odd' else 'even' end ) AND (@starthouseno between A.house_no and A.house_no_to) AND A.house_no_to<>0)
OR @starthouseno is null) AND
(A.locality_name=@Suburb or @suburb is null) AND
)
I am using workflow 5 saas so I don't have access to the sql command and am hoping to use the query layer instead (the table is in a service). How can I implement similar logic for the house number check? The street and suburb part is easy but I'm struggling with the house number.
Belinda
Please sign in to leave a comment.
Comments
0 comments