Hitachi Vantara Pentaho Community Forums
Results 1 to 22 of 22

Thread: Problem with variables

  1. #1

    Default Problem with variables

    Hello,

    I need to do this:

    1.- take some data from table (some dates)
    2.- this data modify to another format
    3.- the new data with the new format is saved to txt
    4.- read the txt from previus step and convert it to variables
    5.- execute some sql using this variables to set part of the table names in sql and run with every row on the file

    I have the steps 1, 2, 3 completed but steps 4 and 5 i cant figure out how to do. I already try with "Set Variables" and other options but i cant get it work.

    I'm very new on the ETL and BI world, so dont be rude with me

    Someone can help me?

    P.S. Attached the .ktr file

    Thanks in advance!!
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Garsan,

    You only posted one KTR file.
    If you are dealing with Variables, you must use more than one KTR (usually within the context of a KJB aka Job)

    To do this well, you'll want to build:
    Master Job
    - Start
    - Transformation - Build Data ( Existing Steps 1 & 2 ), replace step 3 with Copy Rows to Results
    - Job - Process Rows (set the option: Execute for each row)
    - - Transformation - Set Variable
    - - - Get Rows from Results
    - - - Set Variable
    - - Transformation - Execute SQL & Process SQL (similar to step 5 in your post)

    Hope this helps.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3

    Default

    Hello gutlez,

    Thanks for your help.

    I solved part of the problem creating my job on 3 transformation, first read all data from sql, then transform to the desired format to txt and finally read the txt, but on this step i get stuck, i can read the info on the txt file but i cant use it on the sql, i need to use this info to complet part of the table names using on the sql query, like TB?01 but the ? wont get replaced a suppouse to do, i use this sql statement:

    Name:  query.jpg
Views: 317
Size:  30.7 KB

    As you see, i need to replace the ? with the content of the txt file, wich are dates, so this have to be like TB15051301 and this with every row on the txt.

    I get this error:

    2013/06/27 11:23:10 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
    2013/06/27 11:23:10 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : java.lang.ArrayIndexOutOfBoundsException: 21
    2013/06/27 11:23:10 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:167)
    2013/06/27 11:23:10 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2013/06/27 11:23:10 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.lang.Thread.run(Thread.java:722)
    Maybe i'm setting the sql statement wrong?

    Thank you for your help on this.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you are looking to get data back from the DB, you should use the Table Input step, not the Execute SQL step.

    In both steps, for each ? in the SQL statement, you need to have a column to put data into it.
    I see at least 21 ?'s in your SQL, but only 1 value to put in... That's not allowed.

    **EDIT**
    Also, ?'s are TYPE LOCKED... ie. Strings will be quoted, etc. Which means that you aren't allowed (by SQL definition!) to put them in your table names
    **/EDIT**

    Think of them more as sequentially numbered...
    Select TB?(1)01.CLAVE as ClaveTrabajador,
    TB?(2)01.AP_PAT_ ...

    So now you need some values to put into ?(1) and ?(2)

    **EDIT**
    You can't do this!
    You'll end up with:
    Select TB'SomeValue'01.CLAVE as ClaveTrabajador,
    TB'SomeOtherValue'01.AP_PAT_ ...
    **/EDIT**

    However, [Strike]I think[/Strike] you want to do something more along the lines of:
    Select TB${TableName}01.CLAVE as ClaveTrabajador,
    TB${TableName}01.AP_PAT_ ...

    You do this in your set variables section...
    Last edited by gutlez; 06-27-2013 at 01:14 PM.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5

    Default

    Hello gutlez,

    I dont understand you this:
    Also, ?'s are TYPE LOCKED... ie. Strings will be quoted, etc. Which means that you aren't allowed (by SQL definition!) to put them in your table names
    What I need is:

    Take the data from any source, in this example I'm using a TXT file wich contains this:

    300613
    150613
    310513
    150513

    I want to use every line like this to acomplete one sql query like this:

    Code:
    SELECT
    TB30061301.CLAVE AS ClaveTrabajador
    Where 300613 is the data i want to use to acomplete.

    I'm using "Execute SQL Script" because it can take the data from the previous step (read the file) but it throw error about "ArrayIndexOutOfBounds"

    I already tru this query:

    Code:
    SELECT
    TB?01.CLAVE AS ClaveTrabajador
    and
    Code:
    SELECT
    TB'?'01.CLAVE AS ClaveTrabajador
    But this wont want to work.

    I've already tried to "set variables" but neither works, it only generate one variable (the first line on the file) and shows the same error, maybe I'm misconfiguring this step

    Thank you for your patience, English is not my primary language.

    Jorge.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You HAVE to build it as I described in Post #2.
    http://forums.pentaho.com/showthread...370#post345370

    When you have that much built, post it, and we'll go further.

    You **DO NOT** need the text file.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7

    Default

    gutlez,

    This are my current transformations, currently i have no jobs created.

    Step 1: This take the data from a sql table, and write to the txt
    Step 2: This take the data from the txt and try to pass it to the sql statement

    Do i need to create a job before the transformation's work? The job have to be the last step, when everything is running fine, right?

    Thanks.
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You need to use the Job to wire the transformations together.

    This allows you to use variables, and set the "Run for each row" option.

    You don't need the text file.
    You can go directly from the Table Input into the Copy Rows to Results.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  9. #9
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Changes:

    1) Your SQL is a little inefficient.
    Change it to:
    Code:
    SELECT
          dbo.NWNOMINAS01.FECH_NOMI
    FROM NWNOMINAS01
    order by
        [NWNOMINAS01].[FECH_NOMI] DESC
    Then connect it to a select values, changing the meta from Date/Time to String with format ddMMyy

    Then use the copy rows to results instead of Text File Output.

    The above steps take care of the first transformation:
    Master Job
    - Start
    - Transformation - Build Data

    You need to build this Master Job so that you can loop over the rows!
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  10. #10

    Default

    gutlez,

    I made some changes and now works in parts,

    1.- Input table with the query
    2.- copy rows to result
    3.- set variables
    4.- execute sql with the variables
    5.- output to txt (temporary, just to view the results)

    the log shows the variables replaced correctly

    Code:
    SELECT
    TB30061301.CLAVE AS ClaveTrabajador,
    Hurra! the variables get replaced correctly at this point, but the execute sql statement stops:

    Code:
    ...
    2013/06/28 15:16:43 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : WHERE
    2013/06/28 15:16:43 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     ((TB30061301.STATUS ='A') AND
    2013/06/28 15:16:43 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     (PD30061301.TIPO_REG ='0')) OR
    2013/06/28 15:16:43 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     (TB30061301.STATUS ='R')
    2013/06/28 15:16:43 - Execute SQL script.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Cancel has been invoked on this Statement.

    and a few lines up:

    Code:
    2013/06/28 15:16:43 - Set Variables.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
    2013/06/28 15:16:43 - Set Variables.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleStepException: 
    2013/06/28 15:16:43 - Set Variables.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Only 1 input row was expected to set the variables and at least 2 were received.
    2013/06/28 15:16:43 - Set Variables.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/06/28 15:16:43 - Set Variables.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.trans.steps.setvariable.SetVariable.processRow(SetVariable.java:101)
    2013/06/28 15:16:43 - Set Variables.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2013/06/28 15:16:43 - Set Variables.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at java.lang.Thread.run(Unknown Source)
    2013/06/28 15:16:43 - Transformation - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Errors detected!
    I think the set variables step is wrong?

    Name:  set variables.jpg
Views: 168
Size:  24.0 KB

  11. #11
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Sounds like you might have skipped a step :P

    1) Master Job
    1.1) Start
    1.2) Transformation - Build Data ( Existing Steps 1 & 2 ), replace step 3 with Copy Rows to Results
    1.3) Job - Process Rows (set the option: Execute for each row)
    1.3.1) Start
    1.3.2) Transformation - Set Variable
    1.3.2.1) Get Rows from Results
    1.3.2.2) Set Variable
    1.3.3) Transformation - Execute SQL & Process SQL (similar to step 5 in your post)

    If you skip from 1.2 directly to 1.3.2, you will have problems.
    The big key to iterating over your results from 1.2 is the red part in 1.3
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  12. #12

    Default

    gutlez,

    I hope you have a great weekend!

    Well, this is how I have this thing:

    Job:
    Name:  job.jpg
Views: 84
Size:  8.4 KB

    Transformation:
    Name:  step1.jpg
Views: 85
Size:  10.5 KB

    Strange thing, this morging running the job for testing, it does not replace the variable anymore!

    Thanks!

  13. #13

    Default

    Forget "set Variables"

    If I use table input instead sql statement I get replaced the variables but now this error:

    Code:
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : An error occurred executing SQL in part [P Set values]:
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : SELECT
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     TB30061301.CLAVE AS ClaveTrabajador,
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : TB30061301.AP_PAT_ + ' ' + TB30061301.AP_MAT_ + ' ' + TB30061301.NOMBRE AS NombreCompleto,
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     TB30061301.STATUS AS StatusTrabajador,
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     PD30061301.NOMBRE AS NombrePercepDeduc,
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     AC30061301.BIM_PESOS AS Monto 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : FROM
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     dbo.PD30061301 PD30061301
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :         INNER JOIN dbo.AC30061301 AC30061301
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :         ON PD30061301.PER_O_DED = AC30061301.PER_O_DED AND
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :         PD30061301.NUM_PERDED = AC30061301.NUM_PERDED 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :             INNER JOIN dbo.TB30061301 TB30061301 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :             ON AC30061301.CLAVE_TRAB = TB30061301.CLAVE 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : WHERE
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     ((TB30061301.STATUS ='A') AND
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     (PD30061301.TIPO_REG ='0')) OR
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     (TB30061301.STATUS ='R');
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : offending row : [FechaCompletaNomina String(34)]
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error setting value #1 [FechaCompletaNomina String(34)] on prepared statement (String)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : java.sql.SQLException: Invalid parameter index 1.
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Invalid parameter index 1.
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.core.database.Database.openQuery(Database.java:1863)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:235)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at java.lang.Thread.run(Unknown Source)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : offending row : [FechaCompletaNomina String(34)]
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error setting value #1 [FechaCompletaNomina String(34)] on prepared statement (String)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : java.sql.SQLException: Invalid parameter index 1.
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Invalid parameter index 1.
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.core.database.Database.setValues(Database.java:1130)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.core.database.Database.setValues(Database.java:871)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.core.database.Database.openQuery(Database.java:1800)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     ... 4 more
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error setting value #1 [FechaCompletaNomina String(34)] on prepared statement (String)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : java.sql.SQLException: Invalid parameter index 1.
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Invalid parameter index 1.
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.core.database.Database.setValue(Database.java:1103)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.core.database.Database.setValues(Database.java:1126)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     ... 6 more
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: java.sql.SQLException: Invalid parameter index 1.
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.getParameter(JtdsPreparedStatement.java:340)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(JtdsPreparedStatement.java:409)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setString(JtdsPreparedStatement.java:689)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     at org.pentaho.di.core.database.Database.setValue(Database.java:978)
    2013/07/01 13:17:20 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :     ... 7 more

  14. #14
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    As stated earlier in the thread, you cannot set variables in the same transformation that you want to use them in.

    You need to have a Job that does:
    Start -> Transformation (1) -> Job (2) (this job needs the execute for each row option set)

    A Tranformation (1) that does:
    Table Input -> Copy Rows to Results

    Job (2) that does:
    Start -> Transformation (3) -> Transformation (4)

    Transformation (3) does:
    Get Rows From Results -> Set Variables

    Transformation (4) does:
    Table Input (with Variables) -> Text File Output (For Testing only!)


    I've presented this in as many ways as I can think of short of posting pictures and the files - which I don't want to do because the storage on this forum is limited.

    There may be other ways to do this, but this is the most straightforward.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  15. #15
    Join Date
    Feb 2013
    Posts
    7

    Default

    Hi, I use PDI 4.4.0 just few days and I have problem with variables. I want use variable exactly as shown below - I have all steps as posted @gutlez but when I want to use
    Transformation(4): Table Input (with Variables) -> Text File Output (For Testing only!) I have an error.
    In Table input I have statement : SELECT id FROM table_${number}_name LIMIT 1 - where table${number}name is table i.e. table_1_name, table_2_name etc. In this step I have check "Replace variable in script?". When I start Job1 I see in Logging Tab value of variable ${number} - that variable changed and it's ok - JOB is success but with empty text file.I get error when I try save the output to a Text File (TextFile Output and Get Field option):

    Unable to get queryfields for SQL:
    SELECT id FROM table__name LIMIT 1

    Couldn't get field info from [SELECT id FROM table__name LIMIT 1]

    ERROR: relation "table__name" does not exist

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

    Default

    At the time you press "Get Fields" the variable is not yet defined.
    Press Ctrl-T first, and select page "Parameters".
    Enter variable "number" and give it a default value.
    Then, try to get the fields again.
    So long, and thanks for all the fish.

  17. #17

    Default

    gutlez,

    Thank you! IT WORKS NOW!!! I just could not visualize your instructions until your last post. Thanks!!

    Now I just have to figure out how to set the same variable as value in the output table, obviously is not comming from the input table.

  18. #18
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Can you phrase that last line differently?

    I'm not understanding your requirement.

    Table Name supports using the variable, so if you have rows hitting your copy rows like:
    Source Table Name | Destination Table Name
    130531 | 201305
    130601 | 201306
    130602 | 201306
    130603 | 201306

    Then before you set your variables, create the full table names:
    Source Table Name | Destination Table Name | TBDestination
    130531 | 201305 | TB201305
    130601 | 201306 | TB201306
    130602 | 201306 | TB201306
    130603 | 201306 | TB201306

    Then set a variable (eg TBDest) and in the Table Output for table name, you can put ${TBDest} in the table name, and it will change automatically from TB201305 to TB201306

    You also can use the "Table Name is specified in a field" box to set where it's going!
    Last edited by gutlez; 07-03-2013 at 06:35 PM.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  19. #19

    Default

    Yes gutlez, thank you.

    On the original source tables for example TB300613, PD300613, AC300613 on their data, there is no field containing the date or timestamp for the row, because the developers uses one table for every month on the payroll, for example, TB300613 is the table where are stored all the workers on Jun 30 2013, TB150613 is for the workers on Jun 15 2013 and so on with every date, so on the tables there are not fields with dates.

    What I need is to use the same variable on your "Transformation (3)" line to insert on the output table on the last field, for identification, because without the date, on the output table are being stored names, amounts without the period.

    For example:

    The output for the script:

    Code:
    SELECT
        TB30061301.CLAVE AS ClaveTrabajador,
    TB30061301.AP_PAT_ + ' ' + TB30061301.AP_MAT_ + ' ' + TB30061301.NOMBRE AS NombreCompleto,
        TB30061301.STATUS AS StatusTrabajador,
        PD30061301.NOMBRE AS NombrePercepDeduc,
        AC30061301.BIM_PESOS AS Monto 
    FROM
        AspelNOIQ.dbo.PD30061301 PD30061301
            INNER JOIN AspelNOIQ.dbo.AC30061301 AC30061301
            ON PD30061301.PER_O_DED = AC30061301.PER_O_DED AND
            PD30061301.NUM_PERDED = AC30061301.NUM_PERDED 
                INNER JOIN AspelNOIQ.dbo.TB30061301 TB30061301 
                ON AC30061301.CLAVE_TRAB = TB30061301.CLAVE 
    WHERE
        ((TB30061301.STATUS ='A') AND
        (PD30061301.TIPO_REG ='0')) OR
        (TB30061301.STATUS ='R');
    Is:

    ClaveTrabajador NombreCompleto StatusTrabajador NombrePercepDeduc Monto
    2 FULL NAME 1 A SUELDO 41719.4
    2 FULL NAME 1 A PRIMA VACACIONAL 0
    2 FULL NAME 1 A DESPENSA EN EFCTIVO 830
    2 FULL NAME 1 A PREMIOS DE ASISTENCIA 646
    2 FULL NAME 1 A GRATIFICACION 0
    2 FULL NAME 1 A ISR 9277.52
    2 FULL NAME 1 A IMSS 1188.37
    2 FULL NAME 1 A DESC DESPENSA EN EFECTIVO 2
    3 FULL NAME 2 A SUELDO 38358.06
    3 FULL NAME 2 A DESPENSA EN EFCTIVO 400
    3 FULL NAME 2 A PREMIOS DE ASISTENCIA 400
    3 FULL NAME 2 A ISR 8066.32
    3 FULL NAME 2 A IMSS 1087.46
    3 FULL NAME 2 A DESC DESPENSA EN EFECTIVO 2


    As you can see, there is not date on the result of the script, because is already on the name of the tables.
    Last edited by garsan; 07-03-2013 at 06:53 PM. Reason: adding examples

  20. #20
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    The step "Get Variables" is your friend - you can put it inline into your flow, and it will add a column for the variable.

    You could also do:

    SELECT '${Variable}' AS period,
    TB${Variable}.CLAVE AS ClaveTrabajador,
    TB
    ${Variable}.AP_PAT_ + ' ' + TB${Variable}.AP_MAT_ + ' ' + TB${Variable}.NOMBRE AS NombreCompleto,
    TB
    ${Variable}.STATUS AS StatusTrabajador,
    PD
    ${Variable}.NOMBRE AS NombrePercepDeduc,
    AC
    ${Variable}.BIM_PESOS AS Monto
    etc...
    Last edited by gutlez; 07-03-2013 at 07:12 PM.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  21. #21
    Join Date
    Feb 2013
    Posts
    7

    Default

    @marabu
    your's tip helped to resolve half of the problem. I have set "Parameter" in master job and subjob. Sometimes I see variable when I use "Get Fields" and I can see value of variable when I use "Preview" on "Text File Output" sometime i don't - I've not changed anything between executing first time and second or third - after which time it just works.
    Another problem is still unsolved, after executing job with success output, file doesn't create or is empty (when I've create output file before executing job).
    Last edited by nora; 07-04-2013 at 08:50 AM.

  22. #22

    Default

    Thank you gutlez,

    I will try your suggest.

    Thank you very much for all your help!!

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.