QUERY: Using 1 cursor instead of 2?

anshuljain26 · Posted: Sat May 15, 2010 11:24 pm

hi

I came through a problem where I need to enquire the DB2 table for condition:

INPUT TO PROG: ACCOUNT NUMBER AND BRANCH NUMBER

IF LS-Account-no > zero
enquire the respective db2 table for input branch number and account number
ELSE
enquire the respective db2 table ONLY for input branch number.
END-IF.

Solution 1: I can declare two cursors where first cursor will be

QUERY IN FIRST CURSOR:

SELECT ACCOUNT_HOLDER_NAME FROM ABC WHERE ACCOUNT_NUMBER = :ABC.ACCOUNT-NUMBER AND BRANCH_NUMBER = :ABC.BRANCH-NUMBER.

QUERY IN SECOND CURSOR:

SELECT ACCOUNT_HOLDER_NAME FROM ABC WHERE BRANCH_NUMBER = :ABC.BRANCH-NUMBER.

I don't like this solution as opening and closing two cursors is inefficient.
what I thought was for use of casting. where my query is:

IF ACCOUNT-NUMBER > ZERO
MOVE ACCOUNT-NUMBER TO WS-WILDCARD
ELSE
MOVE '%' TO WS-WILDCARD
END-IF.

SELECT ACCOUNT_HOLDER_NAME FROM ABC WHERE BRANCH_NUMBER = :ABC.BRANCH-NUMBER AND CAST(ACCOUNT_NUMBER VARCHAR(08)) LIKE (:WS-WILDCARD).

The above query works fine in SPUFI but when i run this in program using cursor it give a SQL CODE 100.

Kindly tell the problem why is it so happening with cursor and if any one can give me another solution to problem i ll be great.

(NOTE: I am not sure for syntax of CASTING.)

dick scherrer · Posted: Sat May 15, 2010 11:50 pm

Hello and welcome to the forum,

anshuljain26 · Posted: Sat May 15, 2010 11:58 pm

Thanks Dick for the Welcoming note.

I need to read the actual rows data.

Deploying 2 cursors as per the logic provided.

I can't post the sample rows(security issue).

But what is the true reason of difference in behaviour of SPUFI and program?
Is there any?

Please explain.

Thanks.

dick scherrer · Posted: Sun May 16, 2010 12:09 am

Hello,

dbzTHEdinosauer · Posted: Sun May 16, 2010 12:40 am

logic seems a little silly.

either you have account-number and branch number
or
just branch number.

if you just have branch number, use a cursor that has a where clause for branch number
otherwise
use a different cursor where you use branch number and account number in where clause.

if your intent is only to return account-holder-names, then use one or the other.
I don't know your table structure,
but I would think that if you have the branch-number and the account-number,
there would only be one, that's 1,
account holder name returned,
thus no requirement for a cursor;
a singleton select would work.

if you only have branch-number,
then, apparently,
you want to return all account-holder-names in the branch,
thus requiring a cursor.

but you know your data - don't you?
without a better definition from you, we can only surmise.

dbzTHEdinosauer · Posted: Sun May 16, 2010 12:55 am

plus, it is reeeaaaallllly inefficient (that's a word you like to use isn't it?)
to perform a function on every row, (CAST numeric to varchar)
when all you have to do is
where account_number between 0 and 99999999.

anshuljain26 · Posted: Sun May 16, 2010 1:29 am

@Dick: The table has fields like:

Change Timestamp;start date; end date;

about 15 fields more and in my select query i need all of them. I just wrote account_holder_name to give u idea about the query.

Secondly, Thanks for the SEARCH{u enlightened me}. To be Honest, I didn't use.

@ DbzTHEdinosauer: as written to Dick quey won't fetch a single row. table has multiple fields and table that we are enquiring is of amendment history.
(hopw u understand the fields table will have)

for "where account_number between 0 and 99999999."

i dont understand this as it's better not to include this in where clause but then same issue 2 cursors which gonaa make the code inefficient(yes i like the word because inefficiency is what i hate )

dbzTHEdinosauer · Posted: Sun May 16, 2010 4:46 am

Anshul Jain,

As I said, if your input for an account-number is numeric
and your column that contains the account-number is also numeric,
you don't waste time casting columns to alphanumeric (x-type)
so that you can use the LIKE predicate.

If you know the account number, you want:
WHERE ACCOUNT_NUMBER = :ACCOUNT-NUMBER

If you don't know the account number, you want all account numbers
WHERE ACCOUNT_NUMBER >= (GTE - greater than or equal) zero (or 1)
AND ACCOUNT_NUMBER <= (LTE - less than or equal) to <maximum number an account number can be>
e.g. 999999999

So, if you don't have an account number you want all account numbers:
WHERE ACCOUNT_NUMBER >= 1
AND ACCOUNT_NUMBER <= 999999999
which can also be written as:
WHERE ACCOUNT_NUMBER BETWEEN 0 AND 999999999

(I provided a hyperlink to the db2 manual describing the BETWEEN predicate, read it)

Now we can use host variables for this, :LOW-ACCOUNT and :HIGH-ACCOUNT.

So,
If you have an account number as input, you MOVE <input-account-no> to LOW-ACCOUNT, HIGH-ACCOUNT.
If you have no account number as input, you MOVE 0 (or 1) to LOW-ACCOUNT and MOVE 99999999 to HIGH-ACCOUNT.

Since you apparently always have a branch number as input,
you simply MOVE <input-branch-number> to BRANCH-NUMBER

and your 1 cursor WHERE clause (for both situations) is:

anshuljain26 · Posted: Sun May 16, 2010 3:09 pm

[impressed]

@ dbzTHEdinosauer: Thanks.

between clause: I am aware of.

dbzTHEdinosauer · Posted: Sun May 16, 2010 5:23 pm

by the way,
in SPUFI, you coded this: