Solved

Using multiple SQL queries in Inline Querier

  • 15 August 2017
  • 3 replies
  • 10 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

icon

Best answer by erik_jan 15 August 2017, 15:15

View original

3 replies

erik_jan
Rank
Userlevel 2
Badge +16

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
Rank
Userlevel 2
Badge +16

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
Rank
Userlevel 2
Badge +17

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

 

Reply


Community Stats

28,709
Posts
109,481
Replies
36,554
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.