Solved

Using IN command in InlineQuerier

  • 17 August 2017
  • 2 replies
  • 1 view
A

I have two datasets that I am currently working on:

1) DBF1

2) DBF2

I need to lookup for Description (in DBF1) in DB2 within the columns I1 to I8 and

>> to return the corresponding DMA to Inflow field if a match is found OR

>> to display a space if no match found

The result would look as follows:

Currently, I am using the InlineQuerier with IN statement, but it truncates the result when there is no match. The NOT IN command displayed irrelevant results as well.

Please suggest a solution which may be SQL or Non SQL based.

icon

Best answer by courtney_m 17 August 2017, 22:12

View original

2 replies

C
Badge

I was able to get this to work using an InlineQuerier that has 2 output ports. The DMA port outputs all "matched" records, and the DBF1 port outputs all records from DBF1. I used the following SQL statement for the DMA port:

SELECT "DMA","Description","ID"

FROM "DBF1" JOIN "DBF2"

ON "Description" IN ("I1","I2","I3","I4","I5","I6","I7","I8")

Then I used a FeatureMerger (DBF1 = Requestor, DMA = Supplier) that joined on Description. I connected the Merged port AND the UnmergedRequestor port to an AttributeManager (this gives me 1 stream with my 12 original records) - In the AttributeManager, I created the field Inflow, and set it to DMA, if DMA has a value. Otherwise, Inflow is set to a space. Then I removed the DMA field.

This is the output I got:

Here is a screenshot of the entire workspace:

And a screenshot of the AttributeManager properties:

I have also attached the workspace, in case you want to look at it.

Originally, I was going to output the "non-merged" features through another port of the InLineQuerier, but I also was unable to get the NOT IN statement to work successfully. This process is a little clunkier, but it works.

-Courtney

takashi
Rank
Userlevel 2
Badge +17

This SQL statement could also be a possible way.

select
    a.*,
    case
        when b.DMA is null then ''
        else b.DMA
    end as Inflow
from DBF1 as a
left outer join (select * from (
    select DMA, l1 as Description from DBF2
    union all select DMA, l2 from DBF2
    union all select DMA, l3 from DBF2
    union all select DMA, l4 from DBF2
    union all select DMA, l5 from DBF2
    union all select DMA, l6 from DBF2
    union all select DMA, l7 from DBF2
    union all select DMA, l8 from DBF2
) where Description <> '') as b
on b.Description = a.Description

Alternatively, this Non SQL based workflow could work as well.

0684Q00000ArJtoQAF.png

Reply


Community Stats

28,729
Posts
109,527
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.