US and Worldwide: +1 (866) 660-7555
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 39

Thread: Generate SQL for table input

  1. #1

    Unhappy Generate SQL for table input

    Is it possible to do some scripting to generate a SQL statement for a table input? Here's what I want to do:

    -Run an SQL statement to lookup the year
    -Script a for loop that generates a SQL statement based on an offset from that year
    -Run the SQL as a table input
    -Feed the rows to a table output

    Is this possible? Thanks in advance.

  2. #2
    Join Date
    Dec 2005
    Posts
    531

    Default

    Yes it is, but you need a job an two transformations.

    The first transformation does the lookup, creates the SQL script (e.g. with the JS step) and puts it in a variabe, e.g. ${MY_SQL_SCRIPT}.

    The second transformation has the Table Input step with a valid connection, the ${MY_SQL_SCRIPT} in the SQL box and the replace variable option activated.

    Regards,
    Ingo

  3. #3

    Default

    So what would that look like? Would the javascript just be a string declaration, ex:

    var SQL
    SQL = "SELECT * FROM mytable"

    and then put this in the table input:

    ${SQL}

    ???

  4. #4
    Join Date
    Dec 2005
    Posts
    531

    Default

    No, you have to put SQL field in the stream (press Get variables at the bottom of the JS step dialog) and the put it into a KETTLE variable using the Set Variable step.

    The table input should be ok.

  5. #5

    Default

    I did this, and got the error:

    "There was an error executing SQL: ${SQL}
    Syntax error for DATE escape sequence '{SQL}'"

  6. #6
    Join Date
    Dec 2005
    Posts
    531

    Default

    Did you activate the replace variable option?

    If yes, try to output the SQL statement created and check the syntax. If possible post your 2nd transformation.

    Regards,
    Ingo

  7. #7

    Default

    I'm just working on the second transformation right now, using a static value for the year. This is how I have it structured for the time being.


  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    sigh ... you can't reliable set variables in the same transformation as where you use them.

    It's not the first time this is mentioned... it probably won't be the last.

    Regards,
    Sven

  9. #9

    Default

    So I need two transformations and a job to do this?

  10. #10

    Default

    I split it out, and I still get the same error

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •