Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Using output from a sql query

  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Default Using output from a sql query

    Hi,

    Is there any way that I could use the output from a sql query in the next steps of the ETL?

    What I am trying to do is :
    I have a "SELECT count(*) PERSON" query which I want to execute against the table. And according to if the result is 0 or not, I want to decide the next steps of my ETL logic.
    Please let me know.

    Thanks.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    There is a job entry called "Evaluate rows number in a table" that should do the trick.

  3. #3
    Join Date
    Jul 2010
    Posts
    4

    Default

    Thanks Matt, but the Evaluate rows number in a table doesn't help me my case fully. I think I was a little vague with my question. This is what I am exactly trying to achieve:
    1. I want to run a bunch of count(*) queries on a table - these queries come from another database table.
    2. Then I record the output from each of these queries into an excel file to generate a QA report.

    Currently I am using the following sequence in my transformation:
    1. "Table Input step" (which fetches the queries from the SQL table)
    2. "Execute row SQL script" that runs each of these.
    (I have presently attached an Update query in each of these count(*) queries to write the result into a dummy column in the table).
    3. I fetch the result of the query from the dummy column using a "Database Lookup step".
    4. Create a report using the "Excel Output file".

    So, my question is - Is there any way that I can directly put this count(*) sql query result on the stream instead of having to update it into a dummy column each time and then having to fetch it in the next step using the DB lookup?

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    You are right, you were a little vague.
    The other way that comes to mind is by looping in a job over the queries.
    Then you can define a variable to contain the SQL and execute it in the "Table Input" step.

    Since that obviously doesn't solve your original problem you could try the fireToDB function in JavaScript or something like that.

  5. #5
    Join Date
    Jul 2010
    Posts
    4

    Default

    That was a great idea, it works perfectly! Thank you very much MattCasters!

  6. #6
    Join Date
    Jul 2010
    Posts
    4

    Default

    Hi, I am caught in an issue with the looping approach.

    The logic works fine, until if there is a disruption in the flow of the loop. for example say a query failure - in this case, even if there were queries that already executed before the failing query (which is followed by the
    output excel step), no data appears in the excel file on abortion. I think the handle to the file is not closed (as the file gives an 'in use' error message and opens only in read-only mode after the job stops).
    Is there any way to commit the file closure each time a query result is written into it?

    Also, the append feature for excel file isn't working. Any existing information is erased and new data is written into the file, even if 'append' is checked. Is this a bug? I could be missing something too?

Tags for this Thread

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.