Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Bath, England
    Posts
    7

    Unanswered: recursive sql and temporary tables

    Here's a good to see who is awake today!

    I have a stored procedure that is effectively one big SELECT statement. However, one of the values that is to be selected must be obtained using some recursive SQL. My recursive SQL uses a temporary table because I need to sort the recursive SQL's results and only return one value.

    Here is/are my question(s): Is it possible to incorporate 'WITH temp1 AS (SELECT...) SELECT myfield FROM temp1' into the SELECT of my calling SP? If not then presumably I need to call the 'WITH temp1...' code as a nested SP and pick up the result.

    If this can all be done in one SP, what am I doing wrong (ie. why can I not get it to run)? If I need to use a nested SP, how do I call the second SP and get the resulting single value into the SELECT of my calling SP?

    BTW, the calling SP otherwise runs without error, and the called SP also runs without error. It is putting the two together that is causing me problems.

    Thanks for any suggestions.

    Marc

    The calling SP:
    CREATE PROCEDURE USP_AUDITLETTERS ( IN CustomerGroup varchar(4000), IN CustomerName varchar(4000), ExtractDate date)
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT
    TRADEIN1.AUDITLETTER.EXTDATE AS EXTDATE,
    TRADEIN1.AUDITLETTER.TRANSID AS TRANSID,
    TRADEIN1.AUDITLETTER.CUSTID AS CUSTID,
    TRADEIN1.AUDITLETTER.GROUPID AS GROUPID,
    TRADEIN1.AUDITLETTER.SHORTNAME AS SHORTNAME,
    TRADEIN1.AUDITLETTER.TRANSTYPE AS TRANSTYPE,
    TRADEIN1.AUDITLETTER.LIABAMT AS LIABAMT,
    TRADEIN1.AUDITLETTER.LIABCCY AS LIABCCY,
    TRADEIN1.MASTER.STATUS AS STATUS,
    TRADEIN1.MASTER.EXPIRY_DAT AS EXPIRY_DAT,
    TRADEIN1.MASTER.CTRCT_DATE AS CTRCT_DATE,
    TRADEIN1.MASTER.MASTER_REF AS MASTER_REF,
    TRADEIN1.FXRATE86.CODE53,
    (SELECT FXRATE86_2.BUYEXC03 FROM TRADEIN1.FXRATE86 AS FXRATE86_2 WHERE FXRATE86_2.CODE53 = 'SPOT' AND AUDITLETTER.LIABCCY = FXRATE86_2.CURREN49),
    (SELECT FXRATE86_2.SELLEX99 FROM TRADEIN1.FXRATE86 AS FXRATE86_2 WHERE FXRATE86_2.CODE53 = 'SPOT' AND AUDITLETTER.LIABCCY = FXRATE86_2.CURREN49),
    TRADEIN1.GFPF.GFCUN,
    BENPARTYDTLS.ADDRESS1,
    TRADEIN1.TAPF.TAGRD,
    TRADEIN1.LTSBGTYP.GTDESC,
    TRADEIN1.UV_WORKING_DAYS.DAYS1,
    TRADEIN1.UV_WORKING_DAYS.DAYS2,
    -- CALL XANSATEST1('474A')
    (
    WITH PARENT (PKEY, CKEY, LVL) AS
    (
    SELECT PARENT, GROUP, 0
    FROM DLTGROUP
    WHERE GROUP = GroupID
    UNION ALL
    SELECT C.PARENT, GROUP, P.LVL+1
    FROM DLTGROUP C,
    PARENT P
    WHERE P.PKEY = C.GROUP
    AND P.LVL+1 < 50
    )
    SELECT CKEY
    FROM PARENT
    ORDER BY LVL DESC
    FETCH FIRST 1 ROW ONLY
    )
    FROM
    TRADEIN1.AUDITLETTER,
    TRADEIN1.LCMASTER,
    TRADEIN1.MASTER LEFT OUTER JOIN TRADEIN1.EXTMASTER ON MASTER.KEY97 = EXTMASTER.MASTER LEFT OUTER JOIN TRADEIN1.LTSBGTYP ON EXTMASTER.GTYP = LTSBGTYP.GTCDE,
    TRADEIN1.FXRATE86,
    TRADEIN1.PARTYDTLS AS PARTYDTLS,
    TRADEIN1.PARTYDTLS AS BENPARTYDTLS,
    TRADEIN1.GFPF LEFT OUTER JOIN TRADEIN1.TAPF ON GFPF.GFGRP = TAPF.TAGRP,
    TRADEIN1.UV_WORKING_DAYS
    WHERE
    (( TRADEIN1.LCMASTER.KEY97 = TRADEIN1.AUDITLETTER.TRANSID AND
    TRADEIN1.AUDITLETTER.TRANSID = TRADEIN1.MASTER.KEY97 AND
    ((AUDITLETTER.LIABCCY = FXRATE86.CURREN49) OR (AUDITLETTER.LIABCCY = 'GBP')) AND
    AUDITLETTER.CUSTID = PARTYDTLS.KEY97 AND
    LCMASTER.BEN_PTY = BENPARTYDTLS.KEY97 AND
    PARTYDTLS.CUS_MNM = GFPF.GFCUS1
    )
    AND
    (( TRADEIN1.MASTER.STATUS = 'BKF' OR TRADEIN1.MASTER.STATUS = 'CAN' OR TRADEIN1.MASTER.STATUS = 'EXP' OR TRADEIN1.MASTER.STATUS = 'LIV' )
    AND ( TRADEIN1.AUDITLETTER.CODE79 = 'ILC' OR TRADEIN1.AUDITLETTER.CODE79 = 'ISB' OR TRADEIN1.AUDITLETTER.CODE79 = 'IGT' )
    AND (FXRATE86.CODE53 = 'SPOT')
    AND (UV_WORKING_DAYS.CURRENCY = 'GBP')
    AND (AUDITLETTER.GROUPID = CustomerGroup OR CustomerGroup IS NULL)
    AND (GFPF.GFCUN = CustomerName OR CustomerName IS NULL)
    AND (DATE(AUDITLETTER.EXTDATE) <= DATE(ExtractDate))
    AND (DATE(AUDITLETTER.EXTDATE) > DATE(ExtractDate) - 5 DAY)
    AND (INTEGER(UV_WORKING_DAYS.YEAR) = YEAR(ExtractDate)))
    )
    ORDER BY
    TRADEIN1.TAPF.TAGRD ASC,
    TRADEIN1.GFPF.GFCUN ASC,
    TRADEIN1.LTSBGTYP.GTDESC ASC;

    -- Cursor left open for client application
    OPEN cursor1;

    END P1



    The SP containing the recursive SQL:
    CREATE PROCEDURE USP_GET_TOP_ORPHAN (IN GroupID varchar (4000) )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    WITH PARENT (PKEY, CKEY, LVL) AS
    (
    SELECT PARENT, GROUP, 0
    FROM DLTGROUP
    WHERE GROUP = GroupID
    UNION ALL
    SELECT C.PARENT, GROUP, P.LVL+1
    FROM DLTGROUP C,
    PARENT P
    WHERE P.PKEY = C.GROUP
    AND P.LVL+1 < 50
    )
    SELECT CKEY
    FROM PARENT
    ORDER BY LVL DESC
    FETCH FIRST 1 ROW ONLY
    ;
    -- Cursor left open for client application
    OPEN cursor1;

    END P1

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: recursive sql and temporary tables

    As far as I remeber with should be first part of SQL

    with t(...)....
    select

    And I do not think you can use fetch first inside (it can be specified only at the end of sql)

    As an option you can first select your value from "with" sql and use it as parameter for your "big" sql

    regards,
    dmitri

Posting Permissions

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