Skip to main content
Solved

Using multiple SQL queries in Inline Querier


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.

View original
Did this help you find an answer to your question?

3 replies

erik_jan
Contributor
Forum|alt.badge.img+18
  • 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+18
  • Contributor
  • August 15, 2017
erik_jan wrote:

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
Evangelist
  • August 15, 2017
erik_jan wrote:

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

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings