Question

Select all records from on table where an attritbute is contained in a field in a second table

  • 12 March 2019
  • 2 replies
  • 8 views

Badge +4

I have two tables, one which hold records of planning cases that need to be monitored (Table A) and another which is the master list of all planning cases (Table B). In Table B, there is a string field called 'Proposal' which contains a description that may include the reference number of one of the cases in Table A (there may in fact be many records that relate to a case in the Table A).

I would like select all of the records in Table B that contain the case reference from Table A in the Proposal field. I don't think I can use the feature merger as the information in Table B is contained within a Proposal field and is not in a field itself.

I thought about trying to extract the case number from the Proposal field but, whilst it is normally at the end of the string, this is not always the case. It is also not a standard length.

Can anyone think of a different transformer I could use to achieve this?


2 replies

Badge +10

Are your planning reference numbers in a format where you could extract them from the proposal field first before matching, e.g. if they are in a format such as PL/2018/0123 you can probably use a string searcher with some regex to find them then you could use the featuremerger.

It's probably also possible to use an inlinequerier

Userlevel 2
Badge +12

If table A and B are in a database, you could use the SQLCreator transformer.

The SQL would be somthing like this:

Select tableB.* from tableB, tableA

Where tableB.Proposal like '%'||tableA.field||'%'

 

Hope this helps

Reply