To homepage To homepage
Solved

Range lookup

  • 21 February 2023
  • 6 replies
  • 105 views
L
Rank

Hi,

I would like to do a “range lookup”. Because I can't solve it using a conditional lookup, I used a custom field transformation. My code looks like this:

CASE 
   WHEN substring([SBIcode_cleansed], 1, 1) like '[^0-9]' THEN 
                   (SELECT [kvk_groep_omschrijving]
                     FROM  [dsa].[SBIcode_groepering] 
                            where [SBIcode] = [SBIcode_cleansed]
                   ) 
--'test1'
   ELSE
                   (SELECT [kvk_groep_omschrijving]
                     FROM  [dsa].[SBIcode_groepering]
                            where substring([SBIcode_cleansed], 1, 2) >= [min_sbi_code]
                              and substring([SBIcode_cleansed], 1, 2) <= [max_sbi_code]
                   )
--'test2'
   END 

It seems that a subquery is not possible because I get an error message that more than one value is retrieved using the subquery but that is not possible.

Testing it in SSMS works fine. 

(Not using between because above seems to work faster when testing it in SSMS)

Perhaps I need to use a view in these cases but I would like to know what options I have.

I contacted support but they mentioned that I should asked the question in the community.

So hopefully someone can help.

icon

Best answer by tld 21 February 2023, 13:09

View original

6 replies

tld
Rank
Userlevel 1
Badge +1

Not sure why you could not use a conditional lookup?

  • Create a field “Lookupmethod” with a transformation, Value 1 if your fist character in SBIcode_cleansed like 0-9 and Value 2 as the default value.
  • Create a field “LookupKey” with a transformation “Left 2” of SBIcode_cleansed if Lookupmethod=2
  • Create your first lookup with a condition “Lookupmethod = 1”
  • Create another lookup (on the same conditional) lookup with condition “Lookupmethod = 2”. Use your LookupKey in the join.


The two lookups needs different joins like in your solution.

Let me know if you need to see a screenshot of the solution - then I can build it for you.
 

Thomas Duun, Atlytix - https://www.linkedin.com/in/thomasduun/
rvgfox
Rank
Userlevel 3
Badge +1

@Lorenzo Try using TOP 1 in the select statements

L
Rank

Many thanks Thomas.

The first solution with the “comprehensive” lookup works.

Unfortunately the second suggestion (using TOP 1) does not work for my second condition. It does not fail anymore but no result in those case.

So my problem is solved but still would like to know if a sub query is allowed in custom field transformation. Looks like it is not.  
 

rory.smith
Rank
Userlevel 5
Badge +7

You are getting the error on the subquery because it is possible that your subquery returns more than one result. Using an aggregation function like rvgfox suggests will make it so that you force only one record.

This is also why conditional lookups use an aggregation function like max() to ensure only one result is looked up even if more options are available. It is only when there is a 1-1 cardinality that TimeXtender will suggest the 'None’ operator.

In situations like yours I would lookup both alternatives from the source table to the target table and create a new field with a conditional transformation in the target table that copies the lookup that matches the conditions into the field. That way it is also easier to debug later.

L
Rank

For sure only one result is possible, also tested this in SSMS as I stated before. 
Be aware that the lookup suggestion works, it is only the solution with the custom transformation rule using subqueries (my own solution I used) does not work (There is where I used the TOP function).

Anyhow I switched to the lookup solution Thomas provided but wanted to have confirmed whether the use of sub-queries in Custom transformation rules is permitted. 

rory.smith
Rank
Userlevel 5
Badge +7

Testing in SSMS only shows you that the data you have results in one result. Your SQL syntax does not enforce only one result and that is why you are getting an error. The query engine will not allow you to write a subquery that leaves the possibility for multiple return records as this would result in an undefined result.

Your transformation is run as an expression defining a field value inside the transformation view and this requires a single value. From the documentation: "A subquery can appear anywhere an expression can be used, if it returns a single value.”, Subqueries (SQL Server) - SQL Server | Microsoft Learn . 

Running that query in SSMS removes the context of running it inside a view, and SQL Server will not look at your data to evaluate whether it would return one result only.

Reply


Terms and ConditionsCookie settings