Impromptu "not in dataset"

[BlueBrand]

I need to join two tables using a substr() of a column. Using the example below, I want to join TableA and TableB by substr(TableA,1,4)=TableB unless there is a substr(TableA,1,6)=TableB. In that case, I want to use substr(TableA,1,6). I can do that in a SQL statement using a correlated sub-query something like:

select... where substr(TableA,1,6)=TableB or (substr(TableA,1,4)=TableB and not exists (select 1 from TableB(2) where substr(TableA,1,6)=TableB)

However, Impromptu has a mind of its own, and unless I code the SQL myself it doesn't do the join correctly.

TableA TableB

12342913 1234
12340164 123401
12350165 1235

I guess I could create a view, but I'm wondering whether there is another solution. Thanks.

 

[amitkg01]

Hi BlueBrand,

You must have see while creating joins between tables in Impromptu that two option namely definition and diagram come. Select definition while creating the join between the tables. There you can create conditional joins using all of the logical operator. Functions are also available there.

There is a function substring(string_exp, integer_exp1, integer_exp2). You can use this function in creating the join.

If the fields you are talking abt are numbers. You can create the join as follows

(substring(number-to-string(TableA.field),1,6)) = number-to-string(TableB.field) and substring(number-to-string(TableA.field),1,4) <> number-to-string(TableB.field)) or substring(number-to-string(TableA.field),1,4) = number-to-string(TableB.field))


Hope this solves ur problem.

Cheers,
Amit

 

[BlueBrand]

Thanks for the help. One thing that I didn't include is that there are multiple occurrences of the columns in TableA. So going back to the example:

TableA TableB

12342913 1234
12340164 123401
12340164 1235
12350165

When I do the join, I end up with 12340164 joining to both 1234 and 123401 in TableB. To get around that I include &quot;and not exists (select 1 from TableB(2) where substr(TableA,1,6)=TableB). However, I can't do that in the Impromptu join nor in the filter.

 

[amitkg01]

Hi,
Actually I made a mistake in writing the code for the join in the example problem. If you are using the condition I suggested in the join, the problem u r talking abt will obviously come. The condition I suggested is

(substring(number-to-string(TableA.field),1,6)) = number-to-string(TableB.field) and substring(number-to-string(TableA.field),1,4) <> number-to-string(TableB.field)) or substring(number-to-string(TableA.field),1,4) = number-to-string(TableB.field))

This condition will end in joining the records where only first four digits are matching because if first 6 digits are matching, first 4 will also match and the first condition before or operator will never be true. So this join condition will always join the records in which first 4 digits are matching.
I guess u r looking for the records in which if first 6 are matching join should be created using those values otherwise use first 4.

For this correct condition would be
(substring(number-to-string(TableA.field),1,6)) = number-to-string(TableB.field) or substring(number-to-string(TableA.field),1,4) = number-to-string(TableB.field))

This condition will first look for 6 digits to match if not found it will join using first 4 digits.

I think this is the solution to ur problem.

Regards,
Amit