Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Execute SQL Script returns 0 results on a query when there are results to return

  1. #1
    Join Date
    Nov 2016
    Posts
    3

    Default Execute SQL Script returns 0 results on a query when there are results to return

    I am building a quick job that diffs two PostgreSQL tables using 'except' and inserts the result into a temporary table.
    As I already have the SQL statements I just want to use the Execute SQL Script Object for this temporary work.

    the SQL:
    ------------------------------------------------
    drop table if exists diff_table;
    select * into diff_table from
    (
    select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj
    except
    select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj_prev
    ) as diff_result;

    ------------------------------------------------

    This runs perfectly fine from my DB Admin tool (PGAdmin) creating a diff_table with 1852 records.

    When run from within the Execute SQL Object in Kettle I receive the following error:
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : An error occurred executing this job entry :
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - An error occurred executing SQL:
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - select into diff_table from
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - (
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - except
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj_prev
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - ) as diff_result
    2016/12/08 14:15:19 - SQL - Diff fixt_proj - No results were returned by the query.

    If I run, in Kettle, without the 'into' statement it returns 1852 records in the logging output:
    select * from
    (
    select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj
    except
    select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj_prev
    ) as diff_result;

    If I run a count this is also 1852 as would be expected:
    select count(*) from
    (
    select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj
    except
    select id, prpsl_i, loc_c, proj_type_c, tcpm_proj_i, proj_n, impl_d from fixt_proj_prev
    ) as diff_result;


    I have toggled all combinations of 'Send SQL as a single statement?' and 'Use variable substitution?' as well as trying from a SQL file instead of the SQL Statement Text Area.

    Is there something I am missing with the capabilities of the 'Execute a SQL Script' object or has anyone ran into this before?

    Again, the statements run successfully from my Database Client Query Tool (PGAdmin).

    Thanks,
    Ross
    Last edited by bucros; 12-08-2016 at 04:37 PM.

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    If you are trying to get data from a table into PDI then you have to use Table Input step.
    -- Mick --

  3. #3
    Join Date
    Nov 2013
    Posts
    382

    Default

    Am I reading incorrectly or there is a missing * (between select and into) on your Execute SQL step ?

    AS Mick says if you want to get the records to process them you should use a Table Input Step, but if you want to create the table you can use the Execute SQL step and it should work once the syntax corrected.

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.