Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: show me the root...........

  1. #1

    Default show me the root...........

    Hi all,
    I am new to the kettle tool and i have a problem with given query i have to create new column while creating mapping The discount_amt is the alias name .But in the java script i dont know how to write a script to the given query and the over function is not working in the kettle so please some one help me out form this.At least give me a hint to move forward.

    ROUND((A.PRODUCT_ITEM_PRICE * DECODE(A.DISCOUNT,0,0,(A.DISCOUNT / SUM(A.PRODUCT_ITEM_PRICE)
    OVER (PARTITION BY A.ORDER_ID ORDER BY 1))*100)) / 100,2) DISCOUNT_AMT

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

    Default

    we'll show you a carrot

    Use a table input to put your query in and start from there. Javascript doesn't implement the Oracle functions, nor is it normally used to execute SQL queries.

    Or maybe better start from following page: http://wiki.pentaho.com/display/EAI/...+Documentation
    at e.g. the quick start and overview.

    Regards,
    Sven

  3. #3

    Default

    Hi sven,

    Thanks for your carrot......i have a dead line today to complete this work and i have to convert the following query ROUND((A.PRODUCT_ITEM_PRICE * DECODE(A.DISCOUNT,0,0,(A.DISCOUNT / SUM(A.PRODUCT_ITEM_PRICE)
    OVER (PARTITION BY A.ORDER_ID ORDER BY 1))*100)) / 100,2) DISCOUNT_AMT
    like the above query i have 2 more quries.

    in to the script so i have tried so much to do but i am enable to do WRITE the script.Even i tried u r method by taking these 3 columns in an input table but the problem is while connecting this input table to the existing tranformation its showing an error

    We detected rows with varying number of fields, this is not allowed in a transformation. The first row contained 38 fields, another one contained 3 : [DISCOUNT_AMT BigNumber], [DISCOUNT_RATE BigNumber], [SALE_PRICE BigNumber]

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

    Default

    Well... it's at least cheaper in India ... no idea why they require people without knowledge on a tool to have a deadline on a project using that tool. Your company can hire the Pentaho guys ... reverse off-sourcing

    The error message you get is because every row flowing over a hop should of the same format ... same names, nr of columns, order, ... in your case that's not true apparently. Most likely since you're using something wrong in the transformation.

    Maybe attach the transformation and we can see what you're trying to do.

    Regards,
    Sven

  5. #5

    Default

    Hi sven,

    Thanks for your support, i am sending the transformation what i have done until as a screen shot so please go through the attachment and i am giving the query as well i have done almost but the problem with the 3 columns.
    SELECT
    11792+ROWNUM
    SALES_KEY,
    B.DATE_KEY,
    C.ORDER_KEY,
    A.ORDER_ITEM_ID,
    D.BOOK_KEY,
    E.AUTHOR_KEY,
    F.PUBLISHER_KEY,
    4 DISTRIBUTOR_KEY,
    G.CUSTOMER_KEY,
    H.FORMAT_KEY,
    A.PRODUCT_LANG,
    I.COUPON_KEY,
    99999999 DISCOUNT_KEY,
    A.PRODUCT_QUANTITY UNITS,
    ROUND((A.PRODUCT_ITEM_PRICE * DECODE(A.DISCOUNT,0,0,(A.DISCOUNT / SUM(A.PRODUCT_ITEM_PRICE)
    OVER (PARTITION BY A.ORDER_ID ORDER BY 1))*100)) / 100,2) DISCOUNT_AMT,
    ROUND(DECODE(A.DISCOUNT, 0, 0, (A.DISCOUNT / SUM(A.PRODUCT_ITEM_PRICE) OVER (PARTITION BY A.ORDER_ID ORDER BY 1))*100)) DISCOUNT_RATE,
    A.PRODUCT_ITEM_PRICE,
    (A.PRODUCT_ITEM_PRICE - (ROUND((A.PRODUCT_ITEM_PRICE * DECODE(A.DISCOUNT, 0, 0, (A.DISCOUNT / SUM(A.PRODUCT_ITEM_PRICE)
    OVER (PARTITION BY A.ORDER_ID ORDER BY 1))*100)) / 100,2))) SALE_PRICE,
    SYSDATE, NULL, NULL, D.CATEGORY_NAME

    FROM
    STAG_ITEM A,
    D_DATE B,
    D_ORDER C,
    D_BOOK D,
    D_AUTHOR E,
    D_PUBLISHER F,
    D_CUSTOMER G,
    D_FORMAT H,
    D_COUPON I

    WHERE
    TRUNC(A.ORDERDATE) = B.DT
    AND A.ORDER_ID = C.ORDER_NUMBER
    AND A.PRODUCT_ID = D.BOOK_ID
    AND DECODE(UPPER(TRIM(A.PRODUCT_AUTHOR)), NULL, 'ANONYMOUS', TRIM(UPPER(A.PRODUCT_AUTHOR))) = UPPER(TRIM(E.AUTHOR_NAME))
    AND DECODE(UPPER(TRIM(A.PRODUCT_PUBLISHER)), NULL, 'ANONYMOUS', 'CYBERREAD', 'CYBERREAD PUBLISHING', UPPER(TRIM(A.PRODUCT_PUBLISHER))) = UPPER(TRIM(F.PUBLISHER_NAME))
    AND A.USER_ID = G.CUSTOMER_ID
    AND DECODE(UPPER(TRIM(A.FORMAT)), NULL, 'ANONYMOUS',UPPER(TRIM(A.FORMAT))) = UPPER(TRIM(H.FORMAT_ID))
    AND DECODE(TRIM(A.COUPON_ID), NULL, 0, 52, 0, 64,0,TRIM(A.COUPON_ID)) = TRIM(I.COUPON_ID(+))


    Regards,
    jagadish varma.
    Attached Files Attached Files

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

    Default

    Hard to do it like this...


    Find the step in which you pass as first rows something with meta-data [DISCOUNT_AMT BigNumber], [DISCOUNT_RATE BigNumber], [SALE_PRICE BigNumber]

    That is where your problem is... probably you're doing something fishy in your javascript steps. I don't think you can break the rest looking at the screenshot of your transformation.

    Regards,
    Sven
    Last edited by sboden; 10-31-2008 at 08:46 AM.

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.