Results 1 to 2 of 2

Thread: Connect by

  1. #1
    Join Date
    Mar 2012

    Unanswered: Connect by


    Is there any equivalent for "connect by" clause in select query. This is going recursively and memory usage is very bad and it locks up more instances and also the table. Is there a way to rectify this or any other way to build the recursive logic (parent/child relation) - can this be handled in the Java logic part of the code.

    This is our query, we need to fine tune this for performance, can "union all" be used in place of connect by.

    "SELECT DISTINCT p1.product_id AS PARENT, p2.product_id AS CHILD,
    spsa.scndry_article_cnt AS REQUIRED_QUANTITY FROM product p1, product p2 ,
    TABLE(MULTISET( SELECT DISTINCT spsa2.pri_sap_article_nbr,
    spsa2.scndry_sap_article_nbr, spsa2.scndry_article_cnt FROM
    sap_pri_scndry_artcle spsa2 WHERE spsa2.optional_code = 'Y' START WITH
    spsa2.pri_sap_article_nbr IN (SELECT sap_article_nbr FROM product WHERE
    product_id = 381573 AND online_ind = 'D' AND sap_article_nbr IS NOT NULL) AND
    spsa2.optional_code = 'Y' CONNECT BY NOCYCLE spsa2.pri_sap_article_nbr =
    PRIOR spsa2.scndry_sap_article_nbr AND spsa2.optional_code = 'Y')) spsa
    WHERE p2.sap_article_nbr = spsa.scndry_sap_article_nbr AND
    p1.jde_parent_div_nbr = p2.jde_parent_div_nbr AND p1.root_category_id =
    p2.root_category_id AND p1.product_id = 381573 AND p2.product_id <> 381573 AND
    p1.online_ind = 'D' AND p2.online_ind = 'D' AND p2.product_id IN (SELECT
    MIN(product_id) FROM product WHERE sap_article_nbr = p2.sap_article_nbr
    AND online_ind = 'D' AND jde_parent_div_nbr = p2.jde_parent_div_nbr AND
    root_category_id = p2.root_category_id);"
    Last edited by senkum; 03-23-12 at 17:00.

  2. #2
    Join Date
    Sep 2011
    Pont l'Abbé, Brittany, France
    Provided Answers: 1
    Define a datamart and try Informix Warehouse accelerator....

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