Home » Developer & Programmer » Forms » Meassge sending termwise (6i)
Meassge sending termwise [message #659714] Fri, 27 January 2017 21:02 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Respected Sir,
i want to send sms to parents mobile termwise. but when use the following code the parents received message separate separate subject. For example if we have 4 subject the parents received message 4 time not 1 time.
declare 
a number;         -- Student ID
b varchar2(300);  --Student Name
c varchar2(30);  --Mobile
CURSOR cc IS
select distinct stuid,,name,mobile from student where status='PRESENT';
BEGIN
open cc;
LOOP
fetch Cc into A,B,C;
insert into msgout (id,msgto,msg)
values 
(a,c,'Marks Sheet of student '|| b || 'is as under: '|| :SNO ||':'|| :obtmarks ||' / '|| tmarks);
EXIT when Cc%NOTFOUND;
next_record;
END LOOP;
Close c;
end;

Required OutPut Is:
Marks Sheet of ABC is as under:
English: 30/50
urdu: 39/50
Math: 3/50


please view the attached test case.

[Updated on: Fri, 27 January 2017 21:03]

Report message to a moderator

Re: Meassge sending termwise [message #659715 is a reply to message #659714] Fri, 27 January 2017 21:42 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
I must be going blind since I see NOTHING in post above that has anything involving "SUBJECT" or sending SMS message.

I conclude that 1 or more bugs exist in some unposted code.
Re: Meassge sending termwise [message #659716 is a reply to message #659715] Fri, 27 January 2017 21:45 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
(a,c,'Marks Sheet of student '|| b || 'is as under: '|| :SNO ||':'|| :obtmarks ||' / '|| tmarks); 
in this code SNO stands for subject.
Re: Meassge sending termwise [message #659717 is a reply to message #659716] Fri, 27 January 2017 21:49 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
shahzad-ul-hasan wrote on Fri, 27 January 2017 19:45
(a,c,'Marks Sheet of student '|| b || 'is as under: '|| :SNO ||':'|| :obtmarks ||' / '|| tmarks); 
in this code SNO stands for subject.
posted code places rows in table. Full STOP!
What occurs to or with the data remains an unknown mystery.
Re: Meassge sending termwise [message #659719 is a reply to message #659717] Fri, 27 January 2017 22:38 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
please see the attached file.
  • Attachment: 1.jpg
    (Size: 104.07KB, Downloaded 261 times)
Re: Meassge sending termwise [message #659723 is a reply to message #659719] Sat, 28 January 2017 06:40 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
I am surprised that posted code compiles without error, let alone runs; since "tmarks" does not exist except in the INSERT statement.
Re: Meassge sending termwise [message #659725 is a reply to message #659723] Sat, 28 January 2017 07:25 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
(a,c,'Marks Sheet of student '|| b || 'is as under: '|| :SNO ||':'|| :obtmarks ||' / '|| :tmarks); 
sorry for mistake.
Re: Meassge sending termwise [message #659726 is a reply to message #659725] Sat, 28 January 2017 07:35 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Quote:
>>
>> Respected Parents! Marks Sheet of Amina Fardous of class NineS for the DECEMBER TEST 2016 is as under. Math:37 / 50 His/Her Perct. is 74.
>> Respected Parents! Marks Sheet of Amina Fardous of class NineS for the DECEMBER TEST 2016 is as under. Bio/Comp:37 / 50 His/Her Perct. is 74
>> Respected Parents! Marks Sheet of Amina Fardous of class NineS for the DECEMBER TEST 2016 is as under. Phy:27 / 50 His/Her Perct. is 54
this is i got mesage from this query;
DECLARE
	B VARCHAR2(30);
  A VARCHAR2(30);
  C VARCHAR2(500);
  D VARCHAR2(33);
  E VARCHAR2(50);
  F VARCHAR2(30);
CURSOR CC IS
SELECT DISTINCT S.STUID,F.MOBILE,S.NAME,S.CLASS,TS.TSUBJ,T.EXAMC 
       FROM STUDENT S,FAMILY F,TSUBJ TS,TEST T
       WHERE S.CLASS=:TEST.CLASS AND S.SECTION=:TEST.SEC AND S.STATUS='PRESENT'
       AND S.STUID IN (SELECT DISTINCT STUID FROM STUDENT 
       WHERE STATUS='PRESENT') AND F.FAM_ID=S.FAM_ID AND TS.CNAME=:CLASS; 
BEGIN
OPEN CC;
LOOP
 FETCH CC INTO A,B,C,D,E,F;
  	  INSERT INTO MSGOUT (ID,MSGTO,MSG) 
   	  VALUES
   	  (A,'+92'||B,'>> Respected Parents!  Marks Sheet of  '|| C || ' of class ' || d || ' for the '|| F || ' is as under. '|| E ||':' 
   	  || round(:TEST1.OBTMARKS,2) || ' / ' || :totmarks || ' His/Her Perct. is '|| :perct .);
-- NEXT_RECORD;
 EXIT WHEN CC%NOTFOUND;
 END LOOP;
 COMMIT;
 CLOSE CC;
END; 

Actual Result required.

Respected Parents! Marks Sheet of Amina Fardous of class NineS for the DECEMBER TEST 2016 is as under.
Math: 37/50
Bio/Comp: 37/50
Phy: 27/50
His/Her Perct. is 74.

[Updated on: Sat, 28 January 2017 07:42]

Report message to a moderator

Re: Meassge sending termwise [message #659731 is a reply to message #659726] Sat, 28 January 2017 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
37+37+27/150=67%
Re: Meassge sending termwise [message #659738 is a reply to message #659731] Sat, 28 January 2017 10:57 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
any idea
Re: Meassge sending termwise [message #659739 is a reply to message #659738] Sat, 28 January 2017 13:59 Go to previous messageGo to next message
Littlefoot
Messages: 21592
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First, the obvious facts.

This:
Respected Parents! Marks Sheet of Amina Fardous of class NineS for the DECEMBER TEST 2016 is as under. 
should be inserted only once, before the loop.

This:
Math: 37/50
Bio/Comp: 37/50
Phy: 27/50
should be inserted within the loop.

This:
His/Her Perct. is 74.
should be inserted only once, after the loop.

In order to correctly select inserted values from a table, you'll need additional identifier(s), such as ordinal number of the inserted row (so that you could use ORDER BY).

Finally, I'd suggest you to switch to cursor FOR loop; it is easier to handle. If you insist on current solution type, consider naming variables in a meaningful manner (instead of calling variables A, B, C, ...).
Re: Meassge sending termwise [message #659740 is a reply to message #659739] Sat, 28 January 2017 21:33 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
PLEASE VIEW THE ATTACHED FILE.
STUID	NAME	CLASS	SECTION	  MAX(T.EXAMC)	SUBJECT	TOTMARKS	OBTMARKS
560	Sandal	NineA	A	DECEMBER TEST 2016	Eng	50	10
124	Seemab Kanwal	NineA	A	DECEMBER TEST 2016	Isl.O	50	43
140	Aiman Jaleel	NineA	A	DECEMBER TEST 2016	Isl.O	50	0
560	Sandal	NineA	A	DECEMBER TEST 2016	Isl.O	50	44
551	Maryam Maqbool	NineA	A	DECEMBER TEST 2016	Edu	50	37
134	Faiza Iqbal	NineA	A	DECEMBER TEST 2016	G.Sci	50	17
140	Aiman Jaleel	NineA	A	DECEMBER TEST 2016	G.Sci	50	34
560	Sandal	NineA	A	DECEMBER TEST 2016	G.Sci	50	25
8	Husnaina Yousaf	NineA	A	DECEMBER TEST 2016	Math	50	15
124	Seemab Kanwal	NineA	A	DECEMBER TEST 2016	Math	50	25
124	Seemab Kanwal	NineA	A	DECEMBER TEST 2016	Edu	50	41
124	Seemab Kanwal	NineA	A	DECEMBER TEST 2016	G.Sci	50	48
544	Sana Munir	NineA	A	DECEMBER TEST 2016	Math	50	18
560	Sandal	NineA	A	DECEMBER TEST 2016	Math	50	23
8	Husnaina Yousaf	NineA	A	DECEMBER TEST 2016	Edu	50	20
497	Ayesha Noreen	NineA	A	DECEMBER TEST 2016	Edu	50	37
544	Sana Munir	NineA	A	DECEMBER TEST 2016	Eng	50	31
/forum/fa/13437/0/
  • Attachment: 1.png
    (Size: 9.74KB, Downloaded 633 times)

[Updated on: Sun, 29 January 2017 01:53]

Report message to a moderator

Re: Meassge sending termwise [message #659745 is a reply to message #659740] Sun, 29 January 2017 04:12 Go to previous messageGo to next message
Littlefoot
Messages: 21592
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's nothing I'd like to add to my previous message.
Re: Meassge sending termwise [message #659892 is a reply to message #659745] Thu, 02 February 2017 07:35 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
please advised i have created a view and use this following query to build form of master detail using self join.
 CREATE OR REPLACE FORCE VIEW W
(
  STUID, NAME, CLASS, SECTION, T, SUBJECT, TOTMARKS, MOBILE, OBTMARKS
)
AS 
SELECT DISTINCT S.STUID,S.NAME,S.CLASS,S.SECTION,MAX(T.EXAMC) as t,
T.SUBJECT,T.TOTMARKS,F.MOBILE,
       TEST1.OBTMARKS
       FROM STUDENT S,TEST T,TEST1,FAMILY F
       WHERE T.TID=TEST1.TID
       AND T.TDATE=TEST1.TDATE
       AND TEST1.TSTUID=S.STUID
       AND F.FAM_ID=S.FAM_ID
       AND S.STUID=TEST1.TSTUID
       AND T.CLASS=S.CLASS
       AND T.SECTION=S.SECTION
       AND STATUS='PRESENT'
       AND S.CLASS='NineA'
       AND S.SECTION='A'
       AND T.EXAMC='DECEMBER TEST 2016'
       GROUP BY S.CLASS,S.STUID,S.NAME,S.CLASS,S.SECTION,T.SUBJECT,T.TOTMARKS,
       TEST1.OBTMARKS,F.MOBILE
/

or this can be done by nested loops. please help me to build nested loop.

[Updated on: Thu, 02 February 2017 08:05]

Report message to a moderator

Re: Meassge sending termwise [message #659904 is a reply to message #659714] Thu, 02 February 2017 13:51 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
shahzad-ul-hasan wrote on Fri, 27 January 2017 22:02
Respected Sir,
Blah blah blah. I have taken a look at many of your posts. They are all gibberish. You have not once conveyed a coherent question, or even a response.
Re: Meassge sending termwise [message #660120 is a reply to message #659904] Thu, 09 February 2017 04:07 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Solved :
select st.stuid,
       'Respected Parents! Marks Sheet of ' || st.name || 
       ' of class NineS for the DECEMBER TEST 2016 is as under.' || 
       chr(10) || listagg( obtmarks || '/' || tmarks, chr(10)) within group (order by tmarks) sms
from   student st
join   test1 t1
on     st.stuid = t1.tstuid
join   test t
on     t.sno = st.stuid
group  by st.stuid, st.name;

STUID  SMS                                                                                                         
1      Respected Parents! Marks Sheet of ABC of class NineS for the DECEMBER TEST 2016 is as under.
30/50
39/50    
2      Respected Parents! Marks Sheet of A of class NineS for the DECEMBER TEST 2016 is as under.
16.7/50
16.7/50 


Thanks for help
Re: Meassge sending termwise [message #660133 is a reply to message #660120] Thu, 09 February 2017 06:43 Go to previous messageGo to next message
Littlefoot
Messages: 21592
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Can't wait to see you use it in Forms 6i.
Re: Meassge sending termwise [message #660150 is a reply to message #660133] Fri, 10 February 2017 02:19 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
i handle it in database level.
Re: Meassge sending termwise [message #660176 is a reply to message #660150] Fri, 10 February 2017 11:44 Go to previous message
Littlefoot
Messages: 21592
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So it appears I'll wait for quite a long time, eh? OK then, I'm glad you found the solution.
Previous Topic: Distinguish between error messages
Next Topic: How to read java arraylist in pl/sql ?
Goto Forum:
  


Current Time: Mon Sep 21 06:38:14 CDT 2020