SQL/PL - Difference between OS/390 and UDB?

[alex.mcshane]

Hi - I would be grateful for any advice on the following as I have
exhausted all variants within UDB for LUW.

The following concerns an SQL/PL Stored Procedure that has been built
via SPB.
Initially, it was succesfully Registered on OS/390.
However, after exporting the SP across to UDB for LUW, the SQL
statements below will not compile.
The version of UDB / LUW is 7.1

1) SQL Statement: -
SELECT CNTRCT_NO_PRFX, CNTRCTNO_STRT_RNG, CNTRCT_NO_SFFX
INTO v_cntrct_no_prfx, v_cntrctn_strt_rng, v_cntrct_no_sffx
FROM CN_RNG_PD_SNAME
WHERE PROD_SNAME = p_prod_sname
AND SNAME_RNG_STRDT <= current date
AND SNAME_RNG_STPDT is Null
FETCH FIRST 1 ROW ONLY

2) Error message is: -
SQL0104N An unexpected token "FIRST 1 ROW ONLY" was found following
"E_RNG_STPDT is Null ". Expected tokens may include: "<space>". LINE
NUMBER=43. SQLSTATE=42601

[Larry]

Shouldn't it be "FETCH FIRST 1 ROWS ONLY"?

Larry Edelstein

al**********@btinternet.com wrote:

Hi - I would be grateful for any advice on the following as I have
exhausted all variants within UDB for LUW.

The following concerns an SQL/PL Stored Procedure that has been built
via SPB.
Initially, it was succesfully Registered on OS/390.
However, after exporting the SP across to UDB for LUW, the SQL
statements below will not compile.
The version of UDB / LUW is 7.1

1) SQL Statement: -
SELECT CNTRCT_NO_PRFX, CNTRCTNO_STRT_RNG, CNTRCT_NO_SFFX
INTO v_cntrct_no_prfx, v_cntrctn_strt_rng, v_cntrct_no_sffx
FROM CN_RNG_PD_SNAME
WHERE PROD_SNAME = p_prod_sname
AND SNAME_RNG_STRDT <= current date
AND SNAME_RNG_STPDT is Null
FETCH FIRST 1 ROW ONLY

2) Error message is: -
SQL0104N An unexpected token "FIRST 1 ROW ONLY" was found following
"E_RNG_STPDT is Null ". Expected tokens may include: "<space>". LINE
NUMBER=43. SQLSTATE=42601

[Knut Stolze]

Larry wrote:

Shouldn't it be "FETCH FIRST 1 ROWS ONLY"?
The syntax allows the "ROW" too.
al**********@btinternet.com wrote:

Hi - I would be grateful for any advice on the following as I have
exhausted all variants within UDB for LUW.

The following concerns an SQL/PL Stored Procedure that has been built
via SPB.
Initially, it was succesfully Registered on OS/390.
However, after exporting the SP across to UDB for LUW, the SQL
statements below will not compile.
The version of UDB / LUW is 7.1

I guess that might be the problem. I don't remember if V7 did support the
FETCH FIRST x ROWS clause, but I wouldn't be surprised if it doesn't. So
you might want to consider moving to V8.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany