Skip to main content
Solved

Using multiple SQL queries in Inline Querier

  • August 15, 2017
  • 3 replies
  • 78 views
A

I have two datasets:

1) DBF1 - It contains demand and patterns corresponding to a particular ID

2) DBF2 - It contains the factor corresponding to each PATTERN

I need to create a new column for each type and add the factor corresponding to PATTERN_ID, otherwise the default factor is 0.

The output result result would look as follows:

I tried using Feature Merger, but I felt that it would require multiple Feature Mergers to achieve the same.

I even tried Inline Querier but it does not permit more than one SQL statements.

Is there an alternative of doing the same

Best answer by erik_jan

The InlineQuerier should work, but the SQL is a bit more complex:

Select d1.DEMAND1, d1.PATTERN1

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN1) as PAT1_FACTOR

, d1.DEMAND2, d1.PATTERN2

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN2) as PAT2_FACTOR

etc.

From DBF1 d1

That should give you the right result.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

3 replies

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • Best Answer
  • August 15, 2017

The InlineQuerier should work, but the SQL is a bit more complex:

Select d1.DEMAND1, d1.PATTERN1

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN1) as PAT1_FACTOR

, d1.DEMAND2, d1.PATTERN2

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN2) as PAT2_FACTOR

etc.

From DBF1 d1

That should give you the right result.

erik_jan
Contributor
Forum|alt.badge.img+23
  • erik_janContributor
  • Contributor
  • August 15, 2017

The InlineQuerier should work, but the SQL is a bit more complex:

Select d1.DEMAND1, d1.PATTERN1

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN1) as PAT1_FACTOR

, d1.DEMAND2, d1.PATTERN2

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN2) as PAT2_FACTOR

etc.

From DBF1 d1

That should give you the right result.

And to get the 0 values instead of the Null values for non matching patterns you can follow the InlineQuerier by a NullAttributeMapper.

 

 

takashi
Celebrity
  • takashiCelebrity
  • August 15, 2017

The InlineQuerier should work, but the SQL is a bit more complex:

Select d1.DEMAND1, d1.PATTERN1

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN1) as PAT1_FACTOR

, d1.DEMAND2, d1.PATTERN2

, (Select d2.FACTOR from DBF2 d2 where d2.PATTERN_id = d1.PATTERN2) as PAT2_FACTOR

etc.

From DBF1  d1

That should give you the right result.

Agree. You can accomplish the goal with a single SQL statement. This example assigns 0 to the default factor, although a bit more complex.

 

select
    a.ID,
    a.DEMAND1,
    a.PATTERN1,
    case
        when b.FACTOR is null then 0
        else b.FACTOR
    end as PAT1_FACTOR,
    a.DEMAND2,
    a.PATTERN2,
    case
        when c.FACTOR is null then 0
        else c.FACTOR
    end as PAT2_FACTOR,
    a.DEMAND3,
    a.PATTERN3,
    case
        when d.FACTOR is null then 0
        else d.FACTOR
    end as PAT3_FACTOR,
    a.DEMAND4,
    a.PATTERN4,
    case
        when e.FACTOR is null then 0
        else e.FACTOR
    end as PAT4_FACTOR,
    a.DEMAND5,
    a.PATTERN5,
    case
        when f.FACTOR is null then 0
        else f.FACTOR
    end as PAT5_FACTOR
from DBF1 as a
left outer join DBF2 as b on b.PATTERN_ID = a.PATTERN1
left outer join DBF2 as c on c.PATTERN_ID = a.PATTERN2
left outer join DBF2 as d on d.PATTERN_ID = a.PATTERN3
left outer join DBF2 as e on e.PATTERN_ID = a.PATTERN4
left outer join DBF2 as f on f.PATTERN_ID = a.PATTERN5

 

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.