Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: a question about nested query

  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Default a question about nested query

    hello,everyone!


    If I don't use a temp table,how can i come true that take the result of a sub query for a main query?
    like this: select colA,colB from (select colA,colB,colC from testTbl2)

    thank you very much!

    My English is very pool,sorry about this...

  2. #2
    Join Date
    Jun 2009
    Posts
    6

    Default

    why don't you go straight forward with "select colA,colB from testTbl2" ?

    In a such case i don't think that you need any nested or correlated query

  3. #3
    Join Date
    Jul 2009
    Posts
    8

    Default

    that's just a sample.

  4. #4
    Join Date
    Feb 2009
    Posts
    296

    Default

    I don't get the point. Do you have a working SQL query that does not work in PDI? If that is the case - which step do you use and what is the behaviour you expected? And what did actually happen? Do you have any kind of error message?

    If this thread has nothing to do with PDI and your query does not work with the native query tool of your database engine... please use the documentation provided with your database.
    Fabian,
    doing ETL with his hands bound on his back

  5. #5
    Join Date
    Jul 2009
    Posts
    8

    Smile

    I usually use the step of "table input " and "merge join" for two tables join in kettle.
    But now the sql like this,

    select a.c_ply_no as PolicyNo ,
    a.c_sls_cde as HandlerCode,
    a.c_sls_cde as Handler1Code,
    a.c_udr_cde as UnderwriteCode
    from (SELECT * FROM t_edr_base , (SELECT c_ply_no max_policyno,MAX(t_Udr_Date) EndorseTimes FROM t_edr_base
    WHERE t_Udr_Date between '2005-01-01' and '&enddate'
    AND t_edr_base.c_udr_mrk in ('1')
    GROUP BY c_ply_no) MaxEndorse WHERE t_edr_base.c_ply_no=MaxEndorse.max_policyno
    AND t_edr_base.t_Udr_Date=MaxEndorse.EndorseTimes
    ) a
    where a.t_udr_date < = date '2009-07-09'


    I don't know which step can I use.


    Thanks for your help
    Last edited by madmangt; 07-13-2009 at 02:57 AM.

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

    Default

    Table input... as long as it's 1 single query it's table input... nested or not

    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.