Below 3 queries i want to run as single query in mysql and i will pass this result to Pentaho query component to plot some graph.
SET @input = select "22:4,33:4" from dual;
SET @count := 0;
select SUBSTRING_INDEX(@input, ' ', (@count) * 2), ' ', -1) as xyz, som_cnt as count from abc;
Sample string is of unknown length (22:4,33:4,96:6....)
expected output
xyz count
----------------
22 4
33 4
96 6
reference - Mysql Query to Separate space delimited String Convert into 2 columns
I want same functionality in mysql link shown below i tried this solution but i am not sure is set_config works in mysql. SET and SELECT within a single query?
or any method to run store procedure in Query component of Pentaho.
You can move the initialization of user-defined session variables to a Derived table, and Cross Join
with your other table(s):
SELECT SUBSTRING_INDEX(@input, ' ', (@count) * 2), ' ', -1) AS xyz,
som_cnt AS `count`
FROM abc
CROSS JOIN (SELECT @count := 0,
@input := '22:4,33:4'
) AS user_init_vars