Regarding cursor

babuprasad_g · New User Joined: 26 May 2005 Posts: 45 Location: gurgaon

Hi frends,

I have one problem.please help me in this regard.

I have one table like sortcode,branch_no,account_no,Customer_id,Customer_name,....
customer id unique.if it is a join account same customer id for both persons.not only 2 persons it is more than.
if the account is 3 persons join account the customer id is same for three persons.

we have one more file which is having the for each customer id how many accounts.

customer id ----- noof records
---------------------------------
10001 -------- 2
10002 -------- 3
. .
. .
. .
like that
main problem is we have to retrive the all records for each customer id.

i followed i declare the cursor for retriveing records with where condition.
where no of records = ws-a.
first read the file.
move the variable in to ws-a then fetch the records.

my problem is for each time am closing the cursor and fetching again and again.

am looking for one time with out closing the cursor again and again.
Did anybody face the problem ever please let me know.

My mail id is:babuprasad.gumpu@rbs.co.uk

mainframesguru · New User Joined: 24 Jun 2005 Posts: 36 Location: Hyderabad

Hi,

I didn't read the whole question, but it needs refining.

Please improve your Presentation of Question and Vocabulary as well.

Thanks
Vamshi

ovreddy · Posted: Fri Sep 02, 2005 3:25 pm

Hi Prasad,

I am very sorry to tell you that I am unable to understand your question. But as I understood you are having two problems one is with SQl Query and the second one is with CURSOR.

- Better to refine your query using Joins so that you can get the information what you want in one fetch.

- Still If you want to open and close the cursor then use WITH HOLD option while declaring the cursor.

- If u need help regarding WITH HOLD search the forum.

Thanks,
Reddy.

chvj123 · New User Joined: 13 Jun 2005 Posts: 8 Location: hyderabad

first of all to remain the cursor open use with hold
and coming to joint account part
instead of reading the file u declare and start fetching the records.......
this is for only 1 person having one account
but in case of joint account you please check about the customer id part

babuprasad_g · New User Joined: 26 May 2005 Posts: 45 Location: gurgaon

Hi,

First i have one sequential file with CUSTOMER-ID and NO-OF-ACCOUNTS

Second i have one table with some values like

SORT CODE,BRANCH NO,ACCOUNT NO,CUSTOMER ID...Some more columns.

Customer id unique but the table contains more than 1 records because it is the joint accounts.

i want to retrieve all records which is having joint accounts.

This is my question.

ovreddy · Posted: Mon Sep 05, 2005 3:54 pm

Hi Bhanu,

I understood something now. But still I am confusing about your words like CUST_ID is UNIQUE but contains more than one row how???. I am giving a solution to find the details of the customers with two records (I mean with same Cust_Id) in the table.

SELECT * FROM CUST_DETAIL WHERE CUST_ID IN (SELECT CUST_ID FROM CUST_DETAIL GROUP BY CUST_ID HAVING COUNT(*)>1);

Hopes it will work. Please let me know if you need more help on this.

Thanks,
Reddy.