Hi all,

I would like to retrieve all addresses related to an account.

Currently my application uses a cursor to fetch all possible addresses, say something like below :

Exec SQL
Declare address-cursor Cursor for
Select Address-line1,
Addess-line2,
City,
State,
Country
From Address_table
where Account_number = :ws-account-number
End-Exec.

There are a possible of say 4 addresses possible for an account - legal, mailing, business & seasonal.

I am trying to replace the above cursor as a singleton select like

Exec SQL
Select Account_number,
Max(Case Address_type
When 'L'
Then
Address-line1
||Addess-line2
||City
||State
||Country
Else
NULL
END
),
Max(Case Address_type
When 'M'
Then
Address-line1
||Addess-line2
||City
||State
||Country
Else
NULL
END
),
Max(Case Address_type
When 'B'
Then
Address-line1
||Addess-line2
||City
||State
||Country
Else
NULL
END
),
Max(Case Address_type
When 'S'
Then
Address-line1
||Addess-line2
||City
||State
||Country
Else
NULL
END
)
INTO :Account-number,
:Ws-Legal-Address-text:Ws-Legal-Address-len,
:Ws-Mailing-Address:Ws-Mailing-Address-len
:Ws-Business-Addess:Ws-Business-Address-len
:Ws-Seasonal-Address:Ws-Seasonal-Address-len
From Address_table
Where Account_number = :Ws-Account-number
Group by Account_number
End-Exec

The query fetches all addresses into the corresponding columns at a single select.

But my question is will it actually result in performance gains - because we eliminate Cursor (OPEN,FETCH,CLOSE) ?
How is the above singleton select processed internally by DB2 ? Is it processed similar to a cursor internally or like a singleton select ?
Could the Max function here be replaced so that the column function doesnot cause any over load ?

Your suggestions are much appreciated.

Thanks.
Siddharth

DB2 processes all selects using a cursor. So whether you declare it and use it or DB2 does it internally, it is still the same.

Andy

Yes you can !

For this you have to prepare loop.

For example you have table tbl1 with integer id column

You perform LOOP until sqlcode <> +100
then , or something like this.

This method heavy used in CICS, when you perform XCTL and send last id to receiving program.

Will work

Lenny

A loop also uses a cursor under the covers.

But you can forget about...

Lenny

Yes, you won't see it - as is the case for a SELECT INTO statement. But the cursor is still there, of course.

2nd part of question is: "...which result in performance gain" ?

Be sure: Cursor is more efficient.

Use cursors where it possible.

Lenny