Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

QUERY: Using 1 cursor instead of 2?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Sat May 15, 2010 11:24 pm    Post subject: QUERY: Using 1 cursor instead of 2?
Reply with quote

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.)
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat May 15, 2010 11:50 pm    Post subject:
Reply with quote

Hello and welcome to the forum,

Quote:
I need to enquire the DB2 table for condition:
Do you want to determine if any rows exist or do you want to actually read the rows?

Why are there 2 cursors?

If you post a few sample rows and the result youn want from the query it may help someone help you.

Possibly the spufi and the program run differently due to the wildcard specification.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Sat May 15, 2010 11:58 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun May 16, 2010 12:09 am    Post subject:
Reply with quote

Hello,

Quote:
I can't post the sample rows(security issue).
Sure you can - just don't use "real" values. . . Replace anything that needs to be protected with made-up values icon_wink.gif

I believe the difference is the way the way the wildcard is being handled. Then code may need to be different within a program. I'm not connected today, so i can't run a test, but i believe there have been other similar topics in the forum. There is a forum SEARCH link above.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun May 16, 2010 12:40 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun May 16, 2010 12:55 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Sun May 16, 2010 1:29 am    Post subject:
Reply with quote

@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. icon_wink.gif

@ 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)
icon_wink.gif

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 ) icon_smile.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun May 16, 2010 4:46 am    Post subject:
Reply with quote

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:
Code:

WHERE BRANCH_NUMBER     =    :BRANCH-NUMBER
  AND ACCOUNT_NUMBER BETWEEN :LOW-ACCOUNT AND :HIGH-ACCOUNT


In the event that you can have an account number as input and no branch number,
you can set up the same low/high branch-number logic.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Sun May 16, 2010 3:09 pm    Post subject:
Reply with quote

[impressed]

@ dbzTHEdinosauer: Thanks.


between clause: I am aware of.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun May 16, 2010 5:23 pm    Post subject:
Reply with quote

by the way,
in SPUFI, you coded this:
Code:

WHERE ... LIKE '%'


yet in COBOL, the SQL was:
Code:

WHERE ... LIKE '%       '
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us