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;

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

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?

Check the manuals for your platform for the CREATE PROCEDURE Syntax ..

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