469,133 Members | 1,125 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

pl/sql coding problem

11
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. CREATE OR REPLACE procedure TEST_EMAIL(errbuf  OUT   VARCHAR2,            
  4.    retcode            OUT   VARCHAR2,
  5. p_from_date date,
  6. p_to_date date,
  7. P_CANDIDATE_NAME VARCHAR2) as
  8.    x_manager_name1     assignment.manager_name%type;
  9.    x_EMAIL_ADDRESS     candidate.EMAIL_ADDRESS%type;               
  10.    x_candidate_name1   assignment.candidate_name%TYPE;
  11.    x_customer_name1    assignment.customer_name%TYPE;
  12.    x_manager_name      assignment.manager_name%TYPE;
  13.    x_time_sheet_id     tps_time_sheet_master.time_sheet_id%TYPE;
  14.    x_assignment_id1    assignment.assignment_id%TYPE;
  15.    x_fromdate          DATE;
  16.    x_todate            DATE;
  17.    x_cdate             date;                   
  18.    x_bh                NUMBER;
  19.    m1                  date;
  20.    m2                  date;
  21. v_date                 date;
  22.    V1                  VARCHAR2(20000); 
  23. V2                  VARCHAR2(20000);
  24.  V3                  VARCHAR2(20000); 
  25. v4                   VARCHAR2(20000); 
  26. v5                   VARCHAR2(20000);
  27. v6                   varchar2(20000);
  28. v7                   varchar2(20000);
  29. v8                   varchar2(20000); 
  30. v9                   varchar2(20000);  
  31. v10                  varchar2(20000);   
  32. x_mail                  varchar2(20000);
  33. v11                   varchar2(20000);
  34. v12                   varchar2(20000);
  35. v13                  varchar2(20000);
  36. v14                  varchar2(20000);
  37. v15                 varchar2(20000);
  38. v16                 varchar2(20000);
  39. v17                varchar2(20000);
  40. v18                varchar2(20000);
  41. cursor c_top is 
  42. sELECT   a.candidate_name, a.customer_name, a.manager_name,
  43.                b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
  44.                SUM (c.billable_hours),e.EMAIL_ADDRESS
  45.           FROM assignment a, tps_time_sheet_master b,
  46.                tps_time_sheet_detail c,
  47.                candidate E
  48.          WHERE a.assignment_id = b.assignment_id
  49.            AND b.time_sheet_id = c.time_sheet_id
  50.           and a.candidate_id=E.candidate_id
  51.            --AND a.assignment_id = p_assignment_id
  52.            AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
  53.            AND calendar_date BETWEEN p_from_date AND p_to_date
  54.       GROUP BY a.candidate_name,
  55.                a.customer_name,
  56.                a.manager_name,
  57.                b.time_sheet_id,
  58.                b.from_date,
  59.                b.TO_DATE,
  60.                a.assignment_id,
  61.                e.EMAIL_ADDRESS
  62.       ORDER BY assignment_id;
  63.    CURSOR c_main(p_assignment_id1 number)
  64.    IS
  65.       SELECT   a.candidate_name, a.customer_name, a.manager_name,
  66.                b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
  67.                SUM (c.billable_hours)
  68.           FROM assignment a, tps_time_sheet_master b,
  69.                tps_time_sheet_detail c
  70.          WHERE a.assignment_id = b.assignment_id
  71.            AND b.time_sheet_id = c.time_sheet_id
  72.           ----and a.candidate_id=E.candidate_id
  73.            AND a.assignment_id = p_assignment_id1
  74.            AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
  75.            AND calendar_date BETWEEN p_from_date AND p_to_date
  76.       GROUP BY a.candidate_name,
  77.                a.customer_name,
  78.                a.manager_name,
  79.                b.time_sheet_id,
  80.                b.from_date,
  81.                b.TO_DATE,
  82.                a.assignment_id
  83.       ORDER BY assignment_id;
  84.    CURSOR c2 (p_assignment_id NUMBER)
  85.    IS
  86.       SELECT MAX (b.mdate), MIN (b.mdate)
  87.         FROM (SELECT mdate
  88.                 FROM xxmissing_time_date
  89.                WHERE mdate BETWEEN p_from_date AND p_to_date
  90.                  AND aid = p_assignment_id) b;
  91. x_rec           c_main%rowtype;
  92. BEGIN
  93. select sysdate into v_date from dual;
  94.    OPEN c_top;
  95. v1:='During last week your time sheet(s) has (have) been received and entered into the system ';
  96. v2:='as follows:';
  97. v8:='Please note that we not received time sheet(s) for periods mention below:';
  98. v9:='Also note that payroll is processed every alternate  Tuesday.Please ensure that your time';
  99. v10:='Sheet reaches us at least 24 hours prior to payroll processing in order to processing in order';
  100. v11:='to process an accurate and timely payroll.';
  101. v12:='In case of any discrepancy/clarification.please send an email to'; 
  102.  
  103. v18:='Date:'||v_date; 
  104. v15:='';
  105. v16:='';
  106. v14:=v9||v10||v11||chr(10)||v15||chr(10)||v16||chr(10)||v12||chr(10)||''||chr(10)||v13||chr(10)||' '||chr(10)||v18;
  107. v3:=v1||''||v2;
  108.    LOOP
  109.       FETCH c_top
  110.        INTO x_candidate_name1, x_customer_name1, x_manager_name,
  111.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh,x_EMAIL_ADDRESS;
  112. x_mail:=x_EMAIL_ADDRESS;
  113.       EXIT WHEN c_top%NOTFOUND;
  114. begin
  115.  open c_main( x_assignment_id1);
  116. loop
  117. fetch c_main 
  118. INTO x_candidate_name1, x_customer_name1, x_manager_name,
  119.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
  120.   v4 := 'Candidate  Name:'||x_candidate_name1;
  121.   v15:= 'Customer   Name:'||x_customer_name1;
  122. v16:=   'Manager    Name:'||x_manager_name;
  123. v17:=x_fromdate;
  124. v5:=rpad('AID',15)||rpad('from date',15)||rpad('to date',15)||rpad('billable hours',15);
  125. v6:=rpad(x_assignment_id1,15)||rpad(x_fromdate,15)||rpad(x_todate,15)||rpad(x_bh,15);
  126. /*v7:=v3||chr(10)||'||chr(10)||v4||chr(10)||''||chr(10)||v15||chr(10)||' '||chr(10)||v16||chr(10)||''||chr(10)||v5||chr(10)||v17;*/
  127. exit when c_main%notfound;
  128. end loop;
  129.  CLOSE c_main;
  130. end;
  131.       BEGIN
  132.          OPEN c2 (x_assignment_id1);
  133.          LOOP
  134.             FETCH c2
  135.              INTO m1, m2;
  136. v9:=rpad(' ',15)||rpad('from date',15)||rpad('to date',15);
  137. v10:=rpad(' ',15)||rpad(m2,15)||rpad(m1,15);
  138. v7:=v3||chr(10)||''||chr(10)||v4||chr(10)||''||chr(10)||v15||chr(10)||' '||chr(10)||v16||chr(10)||''||chr(10)||''||v5||chr(10)||''||chr(10)||''||chr(10)||v6||chr(10)||''||chr(10)||v8||chr(10)||v9||chr(10)||v10||chr(10)||v14;
  139.             EXIT WHEN c2%NOTFOUND;
  140.          END LOOP;
  141.          CLOSE c2;
  142.       END;
  143.    END LOOP;
  144. MAILOUT
  145.   ('x.com', 
  146.  'x.co.in', 
  147.   'x@gmail.com',  
  148.   'Test Mail',  
  149.   V7);
  150.    CLOSE c_top;
  151. COMMIT;
  152. END;
  153. /
  154.  
I am using this pl/sql code to send email.BY using this code mail goes to particular person.but c_main cursor not able to fetch more than one record.and
in databse there is more than one record.
the output like this
During last week your time sheet(s) has (have) been received and entered into the
system as follows:
Candidate Name:
Customer Name:Citigroup Global Markets Inc.
Manager Name:
AID from date to date billable hours
203 07-JAN-08 13-JAN-08 40


Please note that we not received time sheet(s) for periods mention below:
from date to date

Also note that payroll is processed every alternate Tuesday.Please ensure that your
timeSheet reaches us at least 24 hours prior to payroll processing in order to
processing in orderto process an accurate and timely payroll.


In case of any discrepancy/clarification.please send an email to x.com

Corporation

Date:23-MAR-08

i am not able to fetch more than one aid,fromdate,todate

so can u suggest me how to modified this query.
Mar 24 '08 #1
14 2353
amitpatel66
2,367 Expert 2GB
1. The very first thing you need to look at is reducing the usage of so many variables. You can insted use just one variable of type varchar and store the whole body message in to it rather using lots of variables which makes the code look messy.

2. I think your third cursor c2 takes the MAX and MIN of From and To Date, so in that case your cursor will hold only one value in it. So even you have more than one record in your table, it will take the MAX or MIN of it.
Mar 24 '08 #2
ESHA1
11
1. The very first thing you need to look at is reducing the usage of so many variables. You can insted use just one variable of type varchar and store the whole body message in to it rather using lots of variables which makes the code look messy.

2. I think your third cursor c2 takes the MAX and MIN of From and To Date, so in that case your cursor will hold only one value in it. So even you have more than one record in your table, it will take the MAX or MIN of it.
hi
i am having a problem with c_main cursor.
the script giving me right result
when i am using dbms_output.put_line
Mar 24 '08 #3
amitpatel66
2,367 Expert 2GB
hi
i am having a problem with c_main cursor.
the script giving me right result
when i am using dbms_output.put_line
If you see the below code properly:

Expand|Select|Wrap|Line Numbers
  1.  
  2. LOOP
  3.       FETCH c_top
  4.        INTO x_candidate_name1, x_customer_name1, x_manager_name,
  5.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh,x_EMAIL_ADDRESS;
  6. x_mail:=x_EMAIL_ADDRESS;
  7.       EXIT WHEN c_top%NOTFOUND;
  8. BEGIN
  9.  OPEN c_main( x_assignment_id1);
  10. LOOP
  11. FETCH c_main 
  12. INTO x_candidate_name1, x_customer_name1, x_manager_name,
  13.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
  14.   v4 := 'Candidate  Name:'||x_candidate_name1;
  15.   v15:= 'Customer   Name:'||x_customer_name1;
  16. v16:=   'Manager    Name:'||x_manager_name;
  17. v17:=x_fromdate;
  18. v5:=RPAD('AID',15)||rpad('from date',15)||rpad('to date',15)||rpad('billable hours',15);
  19. v6:=RPAD(x_assignment_id1,15)||rpad(x_fromdate,15)  ||rpad(x_todate,15)||rpad(x_bh,15);
  20. /*v7:=v3||chr(10)||'||chr(10)||v4||chr(10)||''||chr  (10)||v15||chr(10)||' '||chr(10)||v16||chr(10)||''||chr(10)||v5||chr(10)  ||v17;*/
  21. EXIT WHEN c_main%notfound;
  22. END LOOP;
  23.  CLOSE c_main;
  24. END;
  25.  
  26.  
The value for the variables v15, v4,v16 gets updated everytime for each value of the loop. So the last record will be stored in these variables. Finally you do an END LOOP and come out of the loop. So finally the variables will store the last record values in it.
Mar 24 '08 #4
ESHA1
11
hi
so plz can u suggest me what to do.because i have to fetch more than one record if available.
waiting for u r suggestion
Mar 25 '08 #5
amitpatel66
2,367 Expert 2GB
hi
so plz can u suggest me what to do.because i have to fetch more than one record if available.
waiting for u r suggestion
If you want all the values to be stored in a single variable, then try something like this:

Expand|Select|Wrap|Line Numbers
  1. declare
  2. v4 varchar2(200) := NULL;
  3. v15 varchar2(200) := NULL;
  4. v16 varchar2(200) := NULL;
  5. v17 varchar2(200) := NULL;
  6. BEGIN
  7. OPEN c_main
  8. LOOP
  9. FETCH c_main 
  10. INTO x_candidate_name1, x_customer_name1, x_manager_name,
  11.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
  12. EXIT WHEN c_main%NOTFOUND
  13. IF(v4 IS NULL) THEN
  14.   v4 := 'Candidate  Name:'||x_candidate_name1;
  15.   v15:= 'Customer   Name:'||x_customer_name1;
  16. v16:=   'Manager    Name:'||x_manager_name;
  17. v17:=x_fromdate;
  18. ELSE
  19. v4:= v4||','||x_candidate_name1;
  20. v15:= v15||','||x_customer_name1;
  21. v16:= v16||','||x_manager_name;
  22. v17:= v17||','||x_fromdate;
  23. END IF
  24. END LOOP
  25.  
  26.  
The above code will concatenate the existing value with the new value in the same variable, This way you will not loose any value
Mar 25 '08 #6
ESHA1
11
If you want all the values to be stored in a single variable, then try something like this:

Expand|Select|Wrap|Line Numbers
  1. declare
  2. v4 varchar2(200) := NULL;
  3. v15 varchar2(200) := NULL;
  4. v16 varchar2(200) := NULL;
  5. v17 varchar2(200) := NULL;
  6. BEGIN
  7. OPEN c_main
  8. LOOP
  9. FETCH c_main 
  10. INTO x_candidate_name1, x_customer_name1, x_manager_name,
  11.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
  12. EXIT WHEN c_main%NOTFOUND
  13. IF(v4 IS NULL) THEN
  14.   v4 := 'Candidate  Name:'||x_candidate_name1;
  15.   v15:= 'Customer   Name:'||x_customer_name1;
  16. v16:=   'Manager    Name:'||x_manager_name;
  17. v17:=x_fromdate;
  18. ELSE
  19. v4:= v4||','||x_candidate_name1;
  20. v15:= v15||','||x_customer_name1;
  21. v16:= v16||','||x_manager_name;
  22. v17:= v17||','||x_fromdate;
  23. END IF
  24. END LOOP
  25.  
  26.  
The above code will concatenate the existing value with the new value in the same variable, This way you will not loose any value
Hi
Thanks for your valuable suggestion its now giving me desire output.
Mar 27 '08 #7
amitpatel66
2,367 Expert 2GB
Hi
Thanks for your valuable suggestion its now giving me desire output.
You are Welcome. Do post back in case of any other issues.

MODERATOR
Mar 27 '08 #8
ESHA1
11
You are Welcome. Do post back in case of any other issues.

MODERATOR
Hi

by running this procedure output is repeating two times like this

Candidate Name:Yogesh Belsare
Customer Name:American Radiologist Network Inc.
Manager Name:Neil Iyer
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40
Candidate Name:Yogesh Belsare
Customer Name:American Radiologist Network Inc.
Manager Name:Neil Iyer
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40


Candidate Name:Vishal Deo
Customer Name:American Radiologist Network Inc.
Manager Name:
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40
Candidate Name:Vishal Deo
Customer Name:American Radiologist Network Inc.
Manager Name:
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40

i need only one record should display.

the pl/sql procedure like this
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE procedure TEST_EMAIL(errbuf  OUT   VARCHAR2,            
  3.    retcode            OUT   VARCHAR2,
  4. p_from_date date,
  5. p_to_date date
  6. ) as
  7.    x_manager_name1     assignment.manager_name%type;
  8.    x_EMAIL_ADDRESS     candidate.EMAIL_ADDRESS%type;               
  9.    x_candidate_name1   assignment.candidate_name%TYPE;
  10.    x_customer_name1    assignment.customer_name%TYPE;
  11.    x_manager_name      assignment.manager_name%TYPE;
  12.    x_time_sheet_id     tps_time_sheet_master.time_sheet_id%TYPE;
  13.    x_assignment_id1    assignment.assignment_id%TYPE;
  14.    x_fromdate          DATE;
  15.    x_todate            DATE;
  16.    x_cdate             date;                   
  17.    x_bh                NUMBER;
  18.    m1                  date;
  19.    m2                  date;
  20. v_date                 date;
  21.    V1                  VARCHAR2(20000); 
  22. V2                  VARCHAR2(20000);
  23.  V3                  VARCHAR2(20000); 
  24. v4                   VARCHAR2(20000); 
  25. v5                   VARCHAR2(20000);
  26. v6                   varchar2(20000);
  27. v7                   varchar2(20000);
  28. v8                   varchar2(20000); 
  29. v9                   varchar2(20000);  
  30. v10                  varchar2(20000);   
  31. x_mail               varchar2(20000);
  32. v11                  varchar2(20000);
  33. v12                   varchar2(20000);
  34. v13                  varchar2(20000);
  35. v14                  varchar2(20000);
  36. v15                 varchar2(20000);
  37. v16                 varchar2(20000);
  38. v17                varchar2(20000);
  39. v18                varchar2(20000);
  40. x_assid               number;
  41. x_from              varchar2(2000);
  42. x_to              varchar2(2000);
  43. x_bilh              number;
  44. i NUMBER := 1;
  45. CURSOR c_top
  46.    IS
  47.       SELECT   a.candidate_name, a.customer_name, a.manager_name,
  48.                b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
  49.                SUM (c.billable_hours),e.EMAIL_ADDRESS
  50.           FROM assignment a, tps_time_sheet_master b,
  51.                tps_time_sheet_detail c,
  52. candidate E
  53.          WHERE a.assignment_id = b.assignment_id
  54.            AND b.time_sheet_id = c.time_sheet_id
  55. and a.candidate_id=E.candidate_id
  56. ---and c.BILLING_FLAG='N'
  57.           ----and a.candidate_id=E.candidate_id
  58.            ---AND a.assignment_id = p_assignment_id1
  59.            ---AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
  60.            AND calendar_date BETWEEN p_from_date AND p_to_date
  61.       GROUP BY a.candidate_name,
  62.                a.customer_name,
  63.                a.manager_name,
  64.                b.time_sheet_id,
  65.                b.from_date,
  66.                b.TO_DATE,
  67.                a.assignment_id,
  68. e.EMAIL_ADDRESS
  69.       ORDER BY assignment_id;
  70. CURSOR c_main (p_assignment_id number)
  71.    IS
  72.       SELECT   a.candidate_name, a.customer_name, a.manager_name,
  73.                b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
  74.                SUM (c.billable_hours)
  75.           FROM assignment a, tps_time_sheet_master b,
  76.                tps_time_sheet_detail c
  77.          WHERE a.assignment_id = b.assignment_id
  78.            AND b.time_sheet_id = c.time_sheet_id
  79. ---and c.BILLING_FLAG='N'
  80.           ----and a.candidate_id=E.candidate_id
  81.            AND a.assignment_id = p_assignment_id
  82.            ----AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
  83.            AND calendar_date BETWEEN p_from_date AND p_to_date
  84.       GROUP BY a.candidate_name,
  85.                a.customer_name,
  86.                a.manager_name,
  87.                b.time_sheet_id,
  88.                b.from_date,
  89.                b.TO_DATE,
  90.                a.assignment_id
  91.       ORDER BY assignment_id;
  92. begin
  93. select sysdate into v_date from dual;
  94. open c_top;
  95. v1:='During last week, your time sheet(s) has (have) been received and entered into the system ';
  96. v2:='as follows:';
  97. v8:='Please note that we have not received time sheet(s) for period(s) mentioned below:';
  98. v10:=' Xoriant employees should note that payroll is processed every alternate Tuesday. Please ensure that your time sheet reaches';
  99. v11:= ' us at least 24 hours prior to payroll processing in order to process an accurate and timely payroll.';
  100. v9:='If you are on vacation, please submit time sheet(s) with zero hours, approved by your manager.';    
  101. v12:='In case of any discrepancy/clarification,please send an email to etimesheet@xoriant.com';
  102. v13:='Xoriant Corporation';
  103. v18:='Date:'||v_date; 
  104. v16:='';
  105. v14:=v9||v10||''||v11||chr(10)||v16||chr(10)||v12||chr(10)||''||chr(10)||v13||chr(10)||' '||chr(10)||v18;
  106. v3:=v1||''||v2;
  107. loop
  108. fetch c_top 
  109. INTO x_candidate_name1, x_customer_name1, x_manager_name,
  110.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh,x_EMAIL_ADDRESS   ;
  111. fnd_file.PUT_LINE (fnd_file.output,'Candidate Name:'||x_candidate_name1);
  112. fnd_file.PUT_LINE (fnd_file.output,'Customer Name:'||x_customer_name1);
  113. fnd_file.PUT_LINE (fnd_file.output,'Manager Name:'||x_manager_name);
  114. fnd_file.PUT_LINE (fnd_file.output,rpad('from date',15)||rpad('to date',15));
  115. x_mail:=x_EMAIL_ADDRESS;
  116.   v4 := 'Candidate Name:'||x_candidate_name1;
  117.   v15:= 'Customer Name:'||x_customer_name1;
  118. v16:=   'Manager Name:'||x_manager_name;
  119. v5:=rpad('From Date',15)||rpad('To Date',15)||rpad('Billable Hours',15);
  120. select sysdate into v_date from dual;
  121. begin
  122. open c_main(x_assignment_id1);
  123. loop
  124. fetch c_main 
  125. INTO x_candidate_name1, x_customer_name1, x_manager_name,
  126.             x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh  ;
  127. ----x_assid := x_assid ||rpad(x_assignment_id1,15);
  128. /*v7:=v3||chr(10)||'||chr(10)||v4||chr(10)||''||chr(10)||v15||chr(10)||' '||chr(10)||v16||chr(10)||''||chr(10)||v5||chr(10)||v17;*/
  129. exit when c_main%notfound;
  130. x_from :=x_fromdate;
  131. x_to :=x_todate;
  132. v6:=v6||rpad(x_fromdate,15)||rpad(x_todate,15)||rpad(x_bh,15)||chr(10);
  133. fnd_file.PUT_LINE (fnd_file.output,rpad(x_fromdate,15)||rpad(x_todate,15)||rpad(x_bh,15));
  134.             EXIT WHEN c_main%NOTFOUND;
  135.          END LOOP;
  136.          CLOSE c_main;
  137. end;
  138. exit when c_top%notfound;
  139. v7:=v3||chr(10)||''||chr(10)||v4||chr(10)||''||chr(10)||v15||chr(10)||''||chr(10)||v16||chr(10)||''||chr(10)||v5||chr(10)||''||chr(10)||v6 ||chr(10)||''||chr(10)||v8||chr(10)||''||chr(10)||v9||chr(10)||''||chr(10)||v10||chr(10)||''||chr(10)||v14;          
  140. end loop;
  141.    CLOSE c_top;
  142. COMMIT;
  143. END;
  144. /
  145.  
by this procedure two times data is comming.
so plz help me how to avoid this


Thanks and Regards
Esha
Apr 7 '08 #9
amitpatel66
2,367 Expert 2GB
Hi,

Could you please try executing the queries of the CURSORS in TOAD / SQLPLUS and check which cursor is returining two records.
Run query by hard coding the values that you have as a sample output for which it is repeating.
Apr 7 '08 #10
ESHA1
11
Hi,

Could you please try executing the queries of the CURSORS in TOAD / SQLPLUS and check which cursor is returining two records.
Run query by hard coding the values that you have as a sample output for which it is repeating.
Hi,
i put assignment_id=203 in first and second cursor
but still it fetching 4 records for assignment_id=203
Apr 7 '08 #11
amitpatel66
2,367 Expert 2GB
Hi,
i put assignment_id=203 in first and second cursor
but still it fetching 4 records for assignment_id=203
So there are more than one record for a single assignment id is it?
So, you need to make your cursor query to fetch one record so that it does not repeat.
Apr 7 '08 #12
ESHA1
11
So there are more than one record for a single assignment id is it?
So, you need to make your cursor query to fetch one record so that it does not repeat.
Hi
actualy for every candidate there is a assignment_id and that is unique
for every week we enter a time sheet for that paricular candidate
say from 1-mar-08 to 6-mar-08 then 7-mar-08 to 13-mar-08 etc
.when we do data entery for every week, candidate name and assignment id will same only time sheet id will vary.
so i have to display data in such a way that when we choose from date and to date (for example 1-march-08 to 13-march-08) the output should like this

candidate name:xxxx
customer name:yyyy
from date to date billable hours
1-mar-08 6-mar-08 40
7-mar-08 13-mar-08 32


for all candidate
Apr 7 '08 #13
ESHA1
11
So there are more than one record for a single assignment id is it?
So, you need to make your cursor query to fetch one record so that it does not repeat.
Hi
actualy for every candidate there is a assignment_id and that is unique
for every week we enter a time sheet for that paricular candidate
say from 1-mar-08 to 6-mar-08 then 7-mar-08 to 13-mar-08 etc
.when we do data entery for every week, candidate name and assignment id will same only time sheet id will vary.
so i have to display data in such a way that when we choose from date and to date (for example 1-march-08 to 13-march-08) the output should like this

candidate name:xxxx
customer name:yyyy
from date to date billable hours
1-mar-08 6-mar-08 40
7-mar-08 13-mar-08 32


for all candidate
Apr 7 '08 #14
amitpatel66
2,367 Expert 2GB
Try this query:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT b.candidate_name, b.customer_name, b.manager_name, x.assignment_id,x.hrs FROM
  3. (SELECT a.assignment_id,SUM (c.billable_hours) hrs
  4.           FROM assignment a, tps_time_sheet_master b,
  5.                tps_time_sheet_detail c
  6. WHERE a.assignment_id = b.assignment_id
  7.            AND b.time_sheet_id = c.time_sheet_id
  8. AND a.assignment_id = p_assignment_id1
  9. AND b.from_date >= p_from_date 
  10. AND b.TO_DATE <= p_to_date
  11. AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
  12. GROUP BY a.assignment_id) x, assignment b
  13. WHERE x.assignment_id = b.assignment_id
  14. order by x.assignment_id
  15.  
Apr 8 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Berthold Höllmann | last post: by
2 posts views Thread by manning_news | last post: by
144 posts views Thread by Natt Serrasalmus | last post: by
20 posts views Thread by TJ Doherty | last post: by
8 posts views Thread by Ilias Lazaridis | last post: by
7 posts views Thread by Robert Seacord | last post: by
8 posts views Thread by =?ISO-8859-1?Q?Arnaud_Carr=E9?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.