Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: 'Select Values' step is not mapping

  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Default 'Select Values' step is not mapping

    Hi there!

    I'm making a transformation where i get values from 2 databases.

    I have the values i want in 'Table input 2', this is my query for that step:

    SELECT
    invoice as inv

    FROM

    table1

    WHERE status = 0

    So i have a list of invoices that have status 0, now I want to go to the foreign database and only ask for the values of these invoices, this is my query for the 'Table Input' step:


    SELECT

    transaction_number, date, transaction_type, in_numb, invoice, appointment, license_plate

    FROM

    SYSTEM

    where

    invoice = inv --I'm guessing in this line i have to put the name of the invoice i got in the first query

    and

    date >= sysdate - (3000 * (1/24/60))

    You'll find the window of the 'select values' step at the end of this post, and also the diagram of my transformation.

    Finally there's the Insert/update to my local database, but we'll get to that point later.

    Thanks in advance, if there´s something else you need please tell me.

    Name:  steps.JPG
Views: 32
Size:  7.8 KB
    Attached Images Attached Images  

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Select Values decides which columns you want to continue through the flow of the transform.
    Since you only have one value in your Table Input 2, a select values is superfluous.
    Next, since your Table Input (not Table Input 2) appears to be on the same database, you would actually get better throughput by putting the two selects in one table input as a join, and have your database do it.

    So...
    Your ETL becomes Table Input

    SELECT
    transaction_number, date, transaction_type, in_numb, invoice, appointment, license_plate
    FROM
    SYSTEM
    where invoice IN (Select invoice from table1 where status=0)
    and date >= sysdate - (3000 * (1/24/60))

    followed by Insert/Update
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Feb 2011
    Posts
    2

    Default Solution

    Hey Thanks!! You're right.

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.