Results 1 to 2 of 2

Thread: TSQL Help

  1. #1
    Join Date
    Mar 2009
    Posts
    27

    Unanswered: TSQL Help

    I'm importing data off of a series of FY snapshots from an informix db. The older snapshots do not support the following syntax:

    SELECT
    A.propno,
    ,NVL(D.spon_amt, 0) AS spon_amt
    ,NVL(D.cospon_amt, 0) AS cospon_amt
    ,NVL(D.costsh_amt, 0) AS costsh_amt
    FROM cm_prop_mstr A
    LEFT OUTER JOIN
    (SELECT propno, MAX(version) AS MaxVersion
    FROM cm_prop_fund
    GROUP BY propno) B
    ON A.propno = B.propno
    LEFT OUTER JOIN cm_prop_fund D
    ON A.propno = D.propno
    AND B.MaxVersion = D.version

    It seems that any form of collection query in the FROM clause is verboten. Do any of you have a clever way around this? I need data from the row with the max value of version to join to the master table.

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    27
    This seems to solve the need:

    SELECT DISTINCT
    TRIM(A.propno) AS propno
    ,NVL(B.spon_amt, 0) AS spon_amt
    ,NVL(B.cospon_amt, 0) AS cospon_amt
    ,NVL(B.costsh_amt, 0) AS costsh_amt
    -- select count(*)
    -- select b.*
    FROM cm_prop_mstr A
    LEFT OUTER JOIN cm_prop_fund B
    ON A.propno = B.propno
    LEFT OUTER JOIN cm_adv_notice C
    ON A.propno = C.propno
    WHERE (A.propno || '*' || B.version) IN (SELECT DISTINCT propno || '*' || max(version) FROM cm_prop_fund b GROUP BY propno)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •