Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Question Unanswered: Help needed with Stored Proc syntax on AS400

    I am trying to build a stored procedure on AS400 that will except one variable (BLDG) and output a NUMERIC. Essentially I am building 2 scalars and trying scalar/scalar = output.

    I found another SP on the system and tried to mimic its syntax. I'm from the MS SQL world so I am having trouble with this new syntax. Can someone help me fix this up???

    BEGIN

    DECLARE Occupied NUMERIC(10,0);

    DECLARE Total NUMERIC(10,0);

    DELCARE Percent NUMERIC(5,4);

    DECLARE SQLSTMT VARCHAR(100);

    DECLARE C1 CURSOR WITH RETURN FOR S1;

    SET TOTAL =
    (
    SELECT NWPMU1/100 as NWPMU1FROM PRODDTA.F1514 WHERE TRIM(NWMCU) = '24461'AND PRODDTA.F1514.NWEFTB <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))AND (PRODDTA.F1514.NWEFTE = 0 OR PRODDTA.F1514.NWEFTE >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))AND PRODDTA.F1514.NWARTY = 'RSF'AND PRODDTA.F1514.NWARLL = 'B'AND PRODDTA.F1514.NWUNIT = ''
    );

    SET Occupied =
    (
    SELECT SUM(NWPMU1/100)

    FROM PRODDTA.F1514 t1WHERE TRIM(NWMCU) = '24461'AND NWEFTB <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))AND (NWEFTE = 0 OR NWEFTE >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))AND EXISTS(SELECT PRODDTA.F15017.NWDOCO AS LEASE, PRODDTA.F15017.NWLSVR AS STATUS, PRODDTA.F15017.NWUNIT, PRODDTA.F1501B.NEDL01 AS TENANTNAME, PRODDTA.F1501B.NEAN8 AS TENANT, PRODDTA.F15017.NWMIDT AS RENTDATE, PRODDTA.F15017.NWSPAD AS EXPDATE, PRODDTA.F1501B.NEEFTB AS BEGDATE FROM PRODDTA.F15017, PRODDTA.F1501B WHERE TRIM(PRODDTA.F15017.NWMCU) = '24461'AND PRODDTA.F15017.NWLSVR = PRODDTA.F1501B.NELSVR AND PRODDTA.F15017.NWDOCO = PRODDTA.F1501B.NEDOCOAND PRODDTA.F15017.NWMIDT <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))AND (PRODDTA.F15017.NWMODT = 0 OR PRODDTA.F15017.NWMODT >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))AND PRODDTA.F1501B.NEVREF <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))AND (PRODDTA.F1501B.NEVRED = 0 OR PRODDTA.F1501B.NEVRED >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))
    AND PRODDTA.F1501B.NEEFTB <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))AND (PRODDTA.F1501B.NEEFTE = 0 OR PRODDTA.F1501B.NEEFTE >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))AND PRODDTA.F1501B.NELSST <> 'L'AND t1.NWMCU=NWMCU AND t1.NWUNIT=NWUNIT)
    );

    SET Percent = Occupied/Total;

    SET SQLSTMT = 'SELECT Percent';

    PREPARE S1 FROM SQLSTMT;

    OPEN C1 USING Occupied, Total, Percent;

    SET RESULT SETS CURSOR C1;

    END;

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you post what the error is ? Also, include the full SP statement, not just the SP body ..

    One 'bug' is that there is no FROM clause in 'SET TOTAL ='

    I'm not sure if
    OPEN C1 USING Occupied, Total, Percent;

    SET RESULT SETS CURSOR C1;

    are valid either

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    39
    Sathyaram, thanks for you response. Yes, the lack of a from is definitely giving me problems that I couldn't get arround. Therefore, I changed the statement to 1 query as opposed to using variables. This revised statement is:

    SELECT CAST(SUM(CASE FLAG WHEN 2 THEN cast(NWPMU1 as float) END)/SUM(CASE FLAG WHEN 1 THEN cast(NWPMU1 as float) END)as FLOAT)
    FROM
    ((SELECT 1 as flag, NWPMU1/100 as NWPMU1
    FROM PRODDTA.F1514 WHERE TRIM(NWMCU) = '24461'
    AND PRODDTA.F1514.NWEFTB <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))
    AND (PRODDTA.F1514.NWEFTE = 0 OR PRODDTA.F1514.NWEFTE >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))
    AND PRODDTA.F1514.NWARTY = 'RSF'
    AND PRODDTA.F1514.NWARLL = 'B'
    AND PRODDTA.F1514.NWUNIT = '')
    UNION ALL
    (SELECT 2 as flag, SUM(NWPMU1/100)
    FROM PRODDTA.F1514 t1
    WHERE TRIM(NWMCU) = '24461'
    AND NWEFTB <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))
    AND (NWEFTE = 0 OR NWEFTE >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))
    AND EXISTS
    (SELECT PRODDTA.F15017.NWDOCO AS LEASE, PRODDTA.F15017.NWLSVR AS STATUS, PRODDTA.F15017.NWUNIT, PRODDTA.F1501B.NEDL01 AS TENANTNAME, PRODDTA.F1501B.NEAN8 AS TENANT, PRODDTA.F15017.NWMIDT AS RENTDATE, PRODDTA.F15017.NWSPAD AS EXPDATE, PRODDTA.F1501B.NEEFTB AS BEGDATE
    FROM PRODDTA.F15017, PRODDTA.F1501B
    WHERE TRIM(PRODDTA.F15017.NWMCU) = '24461'
    AND PRODDTA.F15017.NWLSVR = PRODDTA.F1501B.NELSVR
    AND PRODDTA.F15017.NWDOCO = PRODDTA.F1501B.NEDOCO
    AND PRODDTA.F15017.NWMIDT <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))
    AND (PRODDTA.F15017.NWMODT = 0 OR PRODDTA.F15017.NWMODT >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))
    AND PRODDTA.F1501B.NEVREF <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))
    AND (PRODDTA.F1501B.NEVRED = 0 OR PRODDTA.F1501B.NEVRED >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))
    AND PRODDTA.F1501B.NEEFTB <= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP))
    AND (PRODDTA.F1501B.NEEFTE = 0 OR PRODDTA.F1501B.NEEFTE >= ((1000 * (100 + (YEAR(CURRENT TIMESTAMP) - 2000))) + DAYOFYEAR(CURRENT TIMESTAMP)))
    AND PRODDTA.F1501B.NELSST <> 'L'
    AND t1.NWMCU=NWMCU AND t1.NWUNIT=NWUNIT))) as VT

    __________________________________________

    The above statement works perfectly. If I run it in the SQL scripts interface in iSeries navigator I get the value back I need.

    The only thing I need to do now is change the '24461' value to a building number parameter that gets passed to the query/procedure.

    Can you help me with the CREATE procedure syntax? I have spent hours on that one part and can't find the correct combination. For example, in MS SQL I can execute the SQL directly in the procedure. In the AS400 proc examples I found on the web, they are using cursors. Is that required?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you help me with the CREATE procedure syntax?
    Check the manuals for your platform for the CREATE PROCEDURE Syntax ..

    For example, in MS SQL I can execute the SQL directly in the procedure. In the AS400 proc examples I found on the web, they are using cursors. Is that required?
    Depends on what you want to do .If you wish to return the resultset to the calling appl, then no cursor is necessary .. If you intend to process row-by-row, then cursor is necessary ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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