Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Newbie qustion RE approach and variables

  1. #1
    Join Date
    Dec 2007
    Posts
    7

    Default Newbie qustion RE approach and variables

    I am attempting to extract data from a remote server that places restrictions on the amount of data that can be returned in a SELECT statement and has a very long connection time. Therefore, making as few connections to this remote server will be a huge performance benefit.

    The approach that I was thinking of is to perform one transformation that retrieves only the primary key from the source table and adds some other fields for tracking purposes, then using a second transformation to get all the needed data for each row.

    The first transformation works and I was able to retrieve about 29,000 rows of data. It is the second that I am having trouble with. This transformation will only return about 200 rows at a time. This second transformation is set up with the first two steps being:

    Step 1
    This is a Table input step with the following:

    Step name: Step 1

    SQL:
    SELECT
    pkey
    FROM local_lookup_table
    WHERE
    updated = 'N'
    ORDER BY pkey
    limit 50

    Step 2
    The second step is another table Input step with the following SQL:

    SELECT pkey, f1, f2, ... f99
    FROM remote_fact_table
    WHERE pkey IN ('?')

    Settings:
    Enable lazy conversion: disabled
    Replace variables in script: enabled
    Insert data from step: Step 1
    Execute for each row: disabled

    The problem that I am facing is I get a SQL error of unrecognized token (?).

    Do I need to perform some other step between these two to create one large list -or- use some other approach?

    Many thanks in advance.

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

    Default

    You can't use "pkey in ( '?' )"


    The first table input will not give you all data in one go, it will supply you wil several rows (if there are more than 1 that match of course)...

    So your second table input should be "pkey = ?" (no 's around the ?) and execute for each should be on.

    Regards,
    Sven

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.