Prev: using List Tablespaces Show Detail to free pages
Next: Fetching MAX rows with one index scan.
From: Frank Swarbrick on 15 Jan 2010 19:17
Riddle me this, Batman...

Take the following:

EXEC SQL
SELECT ACCOUNT_NUM
INTO :GL-REF-ACCOUNT-NUM
FROM FB_LN_ACCT_PROD_REF
WHERE LOAN_TYPE = :GL-REF-LOAN-TYPE
AND CATEGORY = :GL-REF-CATEGORY
AND CALL_TYPE = :GL-REF-CALL-TYPE
AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
ORDER BY FAMILY_1ST_DOT DESC
FETCH FIRST 1 ROWS ONLY
END-EXEC.

Even though I have "FETCH FIRST 1 ROWS ONLY", this returns with an SQLCODE =
-811.
"SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES INTO statement is more than one row."

If I remove the ORDER BY then it correctly returns just the one row with no
error.

If I make it in to a cursor it only returns one row (first fetch SQLCODE =
000, send fetch SQLCODE = 100)

Any idea why this is doesn't work with SELECT INTO?

My workaround is this:

EXEC SQL
SELECT ACCOUNT_NUM
INTO :GL-REF-ACCOUNT-NUM
FROM (
SELECT ACCOUNT_NUM
FROM FB_LN_ACCT_PROD_REF
WHERE LOAN_TYPE = :GL-REF-LOAN-TYPE
AND CATEGORY = :GL-REF-CATEGORY
AND CALL_TYPE = :GL-REF-CALL-TYPE
AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
ORDER BY FAMILY_1ST_DOT DESC
) AS T
FETCH FIRST 1 ROWS ONLY
END-EXEC.

This works. But it really puzzles me why the first way does not work.

My server is DB2/LUW 9,5.
My client is DB2/VSE 7.5.

I don't believe it's a client issue because it appears to be the server that
is returning the SQLCODE -811. But who knows...

On a related matter, I am wondering if even the first way is the best.
Ideally I'd like a query that doesn't need the FETCH FIRST 1 ROWS ONLY
clause and yet always returns only 1 row. The deal is, for each LOAN_TYPE,
CATEGORY and CALL_CODE combination there can be one to three rows. If there
is only one row then FAMILY_1ST_DOT must be set to spaces. With two rows
FAMILY_1ST_DOT will be either 'Y' or 'N' for the second one, and with three
rows FAMILY_1ST_DOT will be 'Y' for one of the other rows and 'N' for the
other.

The host variable GL-REF-FAMILY-1ST-DOT is always 'Y' or 'N'. So if a row
where all of the other parts of the predicate match, we always want to
select the row that actually matches GL-REF-FAMILY-1ST-DOT. But if there is
no row where :GL-REF-FAMILY-1ST-DOT = FAMILY_1ST_DOT then we want the one
where FAMILY_1ST_DOT = ' ' to be returned.

By ordering it so the 'Y' or 'N' come before the ' ', and then fetching only
the first row we get the right result. But is there a better query?

Thanks,
Frank


--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403

From: --CELKO-- on 15 Jan 2010 20:02
SELECT ..INTO.. is called a singleton select in the Standards. It
works with a single row result set and has nothing to do with a
cursor. But you can use the cursor to build a single row result set
then do the INTO.

From: Tonkuma on 17 Jan 2010 22:23
I doubted a possibility of DB2 bug, especially related to federation.
But, I can't check it more.

A workaround came up in my mind was...

SELECT COALESCE(fb_gl_ref.ACCOUNT_NUM , fb_blank.ACCOUNT_NUM)
FROM FB_LN_ACCT_PROD_REF fb_blank
LEFT OUTER JOIN
FB_LN_ACCT_PROD_REF fb_gl_ref
ON fb_gl_ref.LOAN_TYPE = :GL-REF-LOAN-TYPE
AND fb_gl_ref.CATEGORY = :GL-REF-CATEGORY
AND fb_gl_ref.CALL_TYPE = :GL-REF-CALL-TYPE
AND fb_gl_ref.FAMILY_1ST_DOT = :GL-REF-FAMILY-1ST-DOT
WHERE fb_blank. LOAN_TYPE = :GL-REF-LOAN-TYPE
AND fb_blank. CATEGORY = :GL-REF-CATEGORY
AND fb_blank. CALL_TYPE = :GL-REF-CALL-TYPE
AND fb_blank. FAMILY_1ST_DOT = ' '


From: Tonkuma on 17 Jan 2010 23:29
or

SELECT COALESCE(f_gl_ref.ACCOUNT_NUM , f_blank.ACCOUNT_NUM)
FROM FB_LN_ACCT_PROD_REF f_blank
LEFT OUTER JOIN
FB_LN_ACCT_PROD_REF f_gl_ref
ON f_gl_ref.LOAN_TYPE = f_blank. LOAN_TYPE
AND f_gl_ref.CATEGORY = f_blank. CATEGORY
AND f_gl_ref.CALL_TYPE = f_blank. CALL_TYPE
AND f_gl_ref.FAMILY_1ST_DOT = :GL-REF-FAMILY-1ST-DOT
WHERE f_blank. LOAN_TYPE = :GL-REF-LOAN-TYPE
AND f_blank. CATEGORY = :GL-REF-CATEGORY
AND f_blank. CALL_TYPE = :GL-REF-CALL-TYPE
AND f_blank. FAMILY_1ST_DOT = ' '
From: Serge Rielau on 18 Jan 2010 16:11
On 1/15/2010 7:17 PM, Frank Swarbrick wrote:
> Riddle me this, Batman...
>
> Take the following:
>
> EXEC SQL
> SELECT ACCOUNT_NUM
> INTO :GL-REF-ACCOUNT-NUM
> FROM FB_LN_ACCT_PROD_REF
> WHERE LOAN_TYPE = :GL-REF-LOAN-TYPE
> AND CATEGORY = :GL-REF-CATEGORY
> AND CALL_TYPE = :GL-REF-CALL-TYPE
> AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
> ORDER BY FAMILY_1ST_DOT DESC
> FETCH FIRST 1 ROWS ONLY
> END-EXEC.
>
> Even though I have "FETCH FIRST 1 ROWS ONLY", this returns with an SQLCODE =
> -811.
> "SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
> VALUES INTO statement is more than one row."
>
> If I remove the ORDER BY then it correctly returns just the one row with no
> error.
>
> If I make it in to a cursor it only returns one row (first fetch SQLCODE =
> 000, send fetch SQLCODE = 100)
>
> Any idea why this is doesn't work with SELECT INTO?
>
> My workaround is this:
>
> EXEC SQL
> SELECT ACCOUNT_NUM
> INTO :GL-REF-ACCOUNT-NUM
> FROM (
> SELECT ACCOUNT_NUM
> FROM FB_LN_ACCT_PROD_REF
> WHERE LOAN_TYPE = :GL-REF-LOAN-TYPE
> AND CATEGORY = :GL-REF-CATEGORY
> AND CALL_TYPE = :GL-REF-CALL-TYPE
> AND FAMILY_1ST_DOT IN (:GL-REF-FAMILY-1ST-DOT, ' ')
> ORDER BY FAMILY_1ST_DOT DESC
> ) AS T
> FETCH FIRST 1 ROWS ONLY
> END-EXEC.
>
> This works. But it really puzzles me why the first way does not work.
>
> My server is DB2/LUW 9,5.
> My client is DB2/VSE 7.5.
>
> I don't believe it's a client issue because it appears to be the server that
> is returning the SQLCODE -811. But who knows...
>
> On a related matter, I am wondering if even the first way is the best.
> Ideally I'd like a query that doesn't need the FETCH FIRST 1 ROWS ONLY
> clause and yet always returns only 1 row. The deal is, for each LOAN_TYPE,
> CATEGORY and CALL_CODE combination there can be one to three rows. If there
> is only one row then FAMILY_1ST_DOT must be set to spaces. With two rows
> FAMILY_1ST_DOT will be either 'Y' or 'N' for the second one, and with three
> rows FAMILY_1ST_DOT will be 'Y' for one of the other rows and 'N' for the
> other.
>
> The host variable GL-REF-FAMILY-1ST-DOT is always 'Y' or 'N'. So if a row
> where all of the other parts of the predicate match, we always want to
> select the row that actually matches GL-REF-FAMILY-1ST-DOT. But if there is
> no row where :GL-REF-FAMILY-1ST-DOT = FAMILY_1ST_DOT then we want the one
> where FAMILY_1ST_DOT = ' ' to be returned.
>
> By ordering it so the 'Y' or 'N' come before the ' ', and then fetching only
> the first row we get the right result. But is there a better query?
This is pretty basic....
The first thing I'd do is to exclude the client program (perhaps the VSE
PREPARE does not expect n ORDER BY FETCH FIRST and loses it?
(You could try to find the statement in the SYSCAT.STATEMENTS view to
validate)

You could also use a regular DB2 9.5 client and see what that does.

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

 |  Next  |  Last
Pages: 1 2
Prev: using List Tablespaces Show Detail to free pages
Next: Fetching MAX rows with one index scan.