Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: [Spoon] Table input step - error using mysql variables

  1. #1

    Default [Spoon] Table input step - error using mysql variables

    Hello,

    reading my data from MySql DB I wanted to add a new column counting row number inside each group on my definded sql, so I made this sql that is working on MySql Workbech:

    Code:
    SET @running = 0;
    SELECT country_id, province, city, rownum
    FROM (
        SELECT country_id, province, city,
            @running := if( @previous=concat(country_id, province), @running, 0) + 1 as rownum,
            @previous := concat(country_id, province)
        FROM (
            SELECT distinct country_id, c.code, province, city
            FROM address a
                JOIN 
                 country c
                ON a.country_id = c.id
            ) origin
    ) origin_with_rownum
    This is the workbench output:
    Name:  mysql.jpg
Views: 141
Size:  28.0 KB

    My problem appears when using this sql inside a 'Table input step'. Log says:
    2013/12/20 13:14:17 - get all cities.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
    2013/12/20 13:14:17 - get all cities.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2013/12/20 13:14:17 - get all cities.0 - An error occurred executing SQL:
    2013/12/20 13:14:17 - get all cities.0 - SET @running = 0;
    2013/12/20 13:14:17 - get all cities.0 - SELECT country_id, province, city, rownum
    2013/12/20 13:14:17 - get all cities.0 - FROM (
    2013/12/20 13:14:17 - get all cities.0 - SELECT country_id, province, city,
    2013/12/20 13:14:17 - get all cities.0 - @running := if( @previous=concat(country_id, province), @running, 0) + 1 as rownum,
    2013/12/20 13:14:17 - get all cities.0 - @previous := concat(country_id, province)
    2013/12/20 13:14:17 - get all cities.0 - FROM (
    2013/12/20 13:14:17 - get all cities.0 - SELECT distinct country_id, c.code, province, city
    2013/12/20 13:14:17 - get all cities.0 - FROM address a
    2013/12/20 13:14:17 - get all cities.0 - JOIN
    2013/12/20 13:14:17 - get all cities.0 - country c
    2013/12/20 13:14:17 - get all cities.0 - ON a.country_id = c.id
    2013/12/20 13:14:17 - get all cities.0 - &nbsp origin
    2013/12/20 13:14:17 - get all cities.0 - ) origin_with_rownum
    2013/12/20 13:14:17 - get all cities.0 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT country_id, province, city, rownum
    2013/12/20 13:14:17 - get all cities.0 - FROM (
    2013/12/20 13:14:17 - get all cities.0 - SELECT country_id, province,' at line 2
    2013/12/20 13:14:17 - get all cities.0 -
    2013/12/20 13:14:17 - get all cities.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1641)
    2013/12/20 13:14:17 - get all cities.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:233)

    but i am unable to find what's wrong with my sintaxis, any ideas? is it possible that variables aren't allowed on 'table input step' sql?

    Regards.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Table Input takes a single statement.

    You should add the rownumber using step "Add Sequence", anyway - because maintainability.
    So long, and thanks for all the fish.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.