Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Table Input step running the wrong query

  1. #1

    Default Table Input step running the wrong query

    I have a transformation inside of a job, the job loops for 8 different tables that are stored inside an XML file. Here is the table input step, which takes 3 variables for each table it is to query. The ${TABLE_NAME} is a generic table name, the ${SRC_TABLE_EXP} is the actual table name, and the ${SRC_TIMESTAMP_EXP} is a date field or an expression to generate the date.

    Code:
    SELECT 
        '${TABLE_NAME}' as table_name,
        TRUNC(${SRC_TIMESTAMP_EXP},'DD') as table_date,
        COUNT(*) as src_count 
    FROM  
    ${SRC_TABLE_EXP} 
    WHERE 
    ${SRC_TIMESTAMP_EXP} >= to_date('${MIN_TIMESTAMP}', 'yyyy-mm-dd') 
    and 
    ${SRC_TIMESTAMP_EXP} < to_date('${MAX_TIMESTAMP}', 'yyyy-mm-dd') 
    GROUP BY 
    TRUNC(${SRC_TIMESTAMP_EXP},'DD') 
    ORDER BY 
    TRUNC(${SRC_TIMESTAMP_EXP},'DD')
    An example of a generated query would be:

    Code:
     SELECT 
        'AGENT_SYSTEM' as table_name,
        TRUNC(to_date(a.LOGINDATE || a.logintime,'yyyy/mm/ddhh24:mi:ss'),'DD') as table_date,
        COUNT(*) as src_count , 0 AS DM_COUNT 
    FROM  
    SYSTEM a 
    WHERE 
    to_date(a.LOGINDATE || a.logintime,'yyyy/mm/ddhh24:mi:ss') >= to_date('2013-03-26', 'yyyy-mm-dd') 
    and 
    to_date(a.LOGINDATE || a.logintime,'yyyy/mm/ddhh24:mi:ss') < to_date('2013-06-24', 'yyyy-mm-dd') 
    GROUP BY 
    TRUNC(to_date(a.LOGINDATE || a.logintime,'yyyy/mm/ddhh24:mi:ss'),'DD') 
    ORDER BY 
    TRUNC(to_date(a.LOGINDATE || a.logintime,'yyyy/mm/ddhh24:mi:ss'),'DD')

    If I do detailed logging, I see that the query is correct. The logs will say the query brought back 11 rows, but if I paste the associated query into a query tool it brings back the correct 14 rows, which is correct. I think jumbling up the queries and it is running another query based on another entry in the XML file.

    Any idea why this might be? More info needed?

  2. #2

    Default

    I will attach the ETL. Transformation with the issue is write_table_to_log
    Attached Files Attached Files

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.