You can translate the question and the replies:

SQL Query in a field expression

Hello, I am using Denodo 6.0 and wanted to find out if I can have a SQL query in a Field Expression of the field in a Derived View. I am looking for a instance of certain characters from a separate table (Table B) based on the value in the Field in Table A. Example: Table A has an IP address values in the field (10.174.23.28) and I need to find the Office Name it belongs to from Table B. Thus, in Table B, I have a row that states that any IP addresses that start with "10.174." belong to the "Chicago office". I want the result value of "Chicago office". I tried to using both tables in a Dervied VIew using LEFT OUTER JOIN and the POSITION function, but I got zero rows returned when there were in fact matching rows. Any help will be appreciated. Thanks,
Expression
user
22-09-2018 19:44:28 -0400
code

3 Answers

Hi, I'd recommend you to use the contains operation instead of position in the join condition. Also, take a look to the [Creating Join Views](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_join_views/creating_join_views#) documentation of our knowledge base. Also, you can create a selection view over the first table and specify a where expression using a subquery. Hope this helps!
Denodo Team
24-09-2018 11:46:09 -0400
code
Hello, The link provided is for Denodo 7.0 I am using Denodo 6.0 Does the proposed solution pertain to Denodo 6.0 as weel? Or only 7.0?
user
24-09-2018 11:50:49 -0400
Hi, Yes, you can take a look to the [Creating Join Views](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/creating_derived_views/creating_join_views) document for Denodo 6.0. Hope this helps!
Denodo Team
03-10-2018 04:20:18 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here