Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I try to join between SQL query and inline when the join I want to execute will be provided that the inline data will be like the number in SQL.
For example:
sql:
type | count |
12333113 | 50 |
425723 | 5 |
644521 | 4444 |
8752211 | 32 |
inline
group type | group number |
1 | AAA |
42 | BBB |
6 | CCC |
8752 | DDD |
join table
type | count | group number |
12333113 | 50 | AAA |
425723 | 5 | BBB |
644521 | 4444 | CCC |
8752211 | 32 | DDD |
Thanks in advance
Hi Amir,
One way to solve it is through an iteration on each "group type" value and using the Wildmatch or SubStringCount functions, building it step by step.
For example:
for each vGroupType in FieldValueList('group type')
Grouping:
Load type,
count,
'$(vGroupType)' as GroupType
Resident sql where wildmatch(type,'$(vGroupType)*')>0;
next;
Drop table sql;
Left Join (Grouping)
Load "group type" as GroupType,
"group number"
Resident inline;
Drop table inline;
That should work.
Regards,
Sorin.
Hi Amir,
One way to solve it is through an iteration on each "group type" value and using the Wildmatch or SubStringCount functions, building it step by step.
For example:
for each vGroupType in FieldValueList('group type')
Grouping:
Load type,
count,
'$(vGroupType)' as GroupType
Resident sql where wildmatch(type,'$(vGroupType)*')>0;
next;
Drop table sql;
Left Join (Grouping)
Load "group type" as GroupType,
"group number"
Resident inline;
Drop table inline;
That should work.
Regards,
Sorin.
thank you very much!
Hi ,
While I was trying to implement the solution (working properly) I encountered another problem.
type | count |
12333113 | 50 |
425723 | 5 |
124444 | 4444 |
8752211 | 32 |
group type | group number |
1 | AAA |
42 | BBB |
1244 | CCC |
8752 | DDD |
type | count | group number |
12333113 | 50 | AAA |
425723 | 5 | BBB |
124444 | 4444 | CCC |
8752211 | 32 | DDD |
You can see that the prefix of the first and third lines is the same prefix but it is about two different group number.
Is it possible that the join between the two queries can be performed according to the correct prefix?
Thanks