Home » SQL & PL/SQL » SQL & PL/SQL » Loops
| Loops [message #11036] |
Tue, 02 March 2004 09:46  |
Brandis
Messages: 11
Registered: February 2004
|
Junior Member |
|
|
I am trying to write a loop that will take a list of different values under the same field and put them on one row. I am really new at PL/SQL and need the most basic script to be able to understand this or if it is a difficult script I will probably need notes explaining what all is going on in the script. For instance, I am trying to put a student's class schedule on one line rather than a list.
I know how to get this:
Jane Doe 120 MTH
Jane Doe 210 ENG
Jane Doe 101 CSC
I am trying to get this:
Jane Doe 120 MTH 210 ENG 101 CSC
Can anyone help me with this? If so please email me!!!
|
|
|
|
| Re: Loops [message #11043 is a reply to message #11036] |
Tue, 02 March 2004 19:40  |
resy
Messages: 86
Registered: December 2003
|
Member |
|
|
try out this
declare
sss varchar2(1000);
begin
for i in (select distinct name from t_marks) loop
sss := ' ';
for j in (select mark, subject from t_marks where name = i.name) loop
sss := sss||' '||j.mark||' '||j.subject;
end loop;
dbms_output.put_line(i.name||' '||sss);
end loop;
end;
/
output will be like
Jane Doe 120 MTH 210 ENG 101 CSC
aLEX 101 ENG 230 MTH 300 CSC
|
|
|
|
| Re: Loops [message #11048 is a reply to message #11043] |
Wed, 03 March 2004 10:43 |
Brandis
Messages: 11
Registered: February 2004
|
Junior Member |
|
|
For some reason I am having a hard time getting my script to loop when I try to use more than one table. For instance mark would come from SSBSECT, subject would come from SCBCRSE, and name would come from SPRIDEN. Let's say we needed to add the studentid an that came from SFRSTCR. I can't get it to loop correctly. This is the script that I have:
Declare
studentinfo varchar2(1000);
Begin
For i in (select distinct sfrstcr_pidm, spriden_id, spriden_last_name, sfrstcr_crn
from
spriden, sfrstcr
where
spriden_change_ind is null
and sfrstcr_pidm = spriden_pidm
and sfrstcr_term_code = '200410') loop
studentinfo := ' ';
For j in (select ssbsect_subj_code, scbcrse_cipc_code, ssbsect_crse_numb,
ssbsect_seq_numb, substr(scbcrse_cont_hr_low,1,2) cont_hr_low
from
ssbsect, scbcrse
where
ssbsect_crn = i.sfrstcr_crn
and ssbsect_term_code = '200410'
and ssbsect_subj_code = scbcrse_subj_code
and ssbsect_crse_numb = scbcrse_crse_numb) loop
studentinfo := studentinfo||j.ssbsect_subj_code||j.scbcrse_cipc_code||
j.ssbsect_crse_numb||j.ssbsect_seq_numb||j.cont_hr_low;
end loop;
dbms_output.put_line(i.spriden_id||i.spriden_last_name||studentinfo);
end loop;
end;
/
This is the output that I am getting:
999999999Mendenhall SHMT1020013
999999999Mendenhall SHMT2060013
999999999Mendenhall ALLH2400016
Does anyone know why? I used the script given to me using one table and it worked exactly the way it was supposed to, but not when I apply the method to a little more complex code...can anyone help me?? Thanks a bunch resy!!! :)
|
|
|
|
| Re: Loops [message #11059 is a reply to message #11048] |
Thu, 04 March 2004 03:56 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I will demonstrate three methods below.
In the first method, I have modified it so that so that only spriden_id and spriden_last_name are selected in your outer loop. It was the inclusion of the extra columns in the outer loop that have multiple values per name that was causing the name to be repeated on a separate line.
In the second method, I have used only one loop, using an order by clause within the loop, and some variables to check if the spriden_id and spriden_last_name are the same.
In the third method, I have created a user-defined aggregate function, using code from Tom Kyte. I prefer this method. Although it is a lot of code, you don't have to write it, just run it to create the type and function. Then, using it is easy in just one simple select statement. You can read more about Tome's stragg function by clicking on the link below. You can also research the individual components in the Oracle on-line documentation.
scott@ORA92> DECLARE
2 studentinfo VARCHAR2(1000);
3 BEGIN
4 FOR i IN
5 (SELECT DISTINCT sp.spriden_id, sp.spriden_last_name
6 FROM spriden sp, sfrstcr sf
7 WHERE sp.spriden_change_ind IS NULL
8 AND sf.sfrstcr_pidm = sp.spriden_pidm
9 AND sf.sfrstcr_term_code = '200410')
10 LOOP
11 studentinfo := ' ';
12 FOR j IN
13 (SELECT DISTINCT ss.ssbsect_subj_code, sc.scbcrse_cipc_code, ss.ssbsect_crse_numb,
14 ss.ssbsect_seq_numb, SUBSTR (sc.scbcrse_cont_hr_low, 1, 2) cont_hr_low
15 FROM ssbsect ss, scbcrse sc, sfrstcr sf, spriden sp
16 WHERE ss.ssbsect_crn = sf.sfrstcr_crn
17 AND ss.ssbsect_term_code = '200410'
18 AND ss.ssbsect_subj_code = sc.scbcrse_subj_code
19 AND ss.ssbsect_crse_numb = sc.scbcrse_crse_numb
20 AND sp.spriden_change_ind IS NULL
21 AND sf.sfrstcr_pidm = sp.spriden_pidm
22 AND sf.sfrstcr_term_code = '200410'
23 AND sp.spriden_id = i.spriden_id
24 AND sp.spriden_last_name = i.spriden_last_name)
25 LOOP
26 studentinfo := studentinfo || j.ssbsect_subj_code || j.scbcrse_cipc_code
27 || j.ssbsect_crse_numb || j.ssbsect_seq_numb || j.cont_hr_low;
28 END LOOP;
29 DBMS_OUTPUT.PUT_LINE (i.spriden_id || i.spriden_last_name || studentinfo);
30 END LOOP;
31 END;
32 /
999999999Mendenhall ALLH240016SHMT1020013SHMT2060013
PL/SQL procedure successfully completed.
scott@ORA92> DECLARE
2 studentinfo VARCHAR2(1000);
3 v_spriden_id spriden.spriden_id%TYPE;
4 v_spriden_last_name spriden.spriden_last_name%TYPE;
5 BEGIN
6 FOR i IN
7 (SELECT DISTINCT sp.spriden_id, sp.spriden_last_name,
8 ss.ssbsect_subj_code, sc.scbcrse_cipc_code, ss.ssbsect_crse_numb,
9 ss.ssbsect_seq_numb, SUBSTR (sc.scbcrse_cont_hr_low, 1, 2) cont_hr_low
10 FROM ssbsect ss, scbcrse sc, sfrstcr sf, spriden sp
11 WHERE ss.ssbsect_crn = sf.sfrstcr_crn
12 AND ss.ssbsect_term_code = '200410'
13 AND ss.ssbsect_subj_code = sc.scbcrse_subj_code
14 AND ss.ssbsect_crse_numb = sc.scbcrse_crse_numb
15 AND sp.spriden_change_ind IS NULL
16 AND sf.sfrstcr_pidm = sp.spriden_pidm
17 AND sf.sfrstcr_term_code = '200410'
18 ORDER BY sp.spriden_id, sp.spriden_last_name)
19 LOOP
20 IF i.spriden_id <> v_spriden_id OR i.spriden_last_name <> v_spriden_last_name
21 OR v_spriden_id IS NULL OR v_spriden_last_name IS NULL
22 THEN
23 DBMS_OUTPUT.PUT_LINE (studentinfo);
24 studentinfo := i.spriden_id || i.spriden_last_name;
25 v_spriden_id := i.spriden_id;
26 v_spriden_last_name := i.spriden_last_name;
27 END IF;
28 studentinfo := studentinfo || i.ssbsect_subj_code || i.scbcrse_cipc_code
29 || i.ssbsect_crse_numb || i.ssbsect_seq_numb || i.cont_hr_low;
30 END LOOP;
31 DBMS_OUTPUT.PUT_LINE (studentinfo);
32 END;
33 /
999999999MendenhallALLH240016SHMT1020013SHMT2060013
PL/SQL procedure successfully completed.
scott@ORA92> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 );
25 /
Type created.
scott@ORA92> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total
18 || ','
19 || value;
20 return ODCIConst.Success;
21 end;
22
23 member function ODCIAggregateTerminate(self IN string_agg_type,
24 returnValue OUT varchar2,
25 flags IN number)
26 return number
27 is
28 begin
29 returnValue := ltrim(self.total,',');
30 return ODCIConst.Success;
31 end;
32
33 member function ODCIAggregateMerge(self IN OUT string_agg_type,
34 ctx2 IN string_agg_type)
35 return number
36 is
37 begin
38 self.total := self.total || ctx2.total;
39 return ODCIConst.Success;
40 end;
41
42
43 end;
44 /
Type body created.
scott@ORA92> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /
Function created.
scott@ORA92> COLUMN studentinfo FORMAT A50
scott@ORA92> SELECT sp.spriden_id, sp.spriden_last_name,
2 STRAGG ( DISTINCT ss.ssbsect_subj_code
3 || sc.scbcrse_cipc_code
4 || ss.ssbsect_crse_numb
5 || ss.ssbsect_seq_numb
6 || SUBSTR (sc.scbcrse_cont_hr_low, 1, 2))
7 AS studentinfo
8 FROM ssbsect ss, scbcrse sc, sfrstcr sf, spriden sp
9 WHERE ss.ssbsect_crn = sf.sfrstcr_crn
10 AND ss.ssbsect_term_code = '200410'
11 AND ss.ssbsect_subj_code = sc.scbcrse_subj_code
12 AND ss.ssbsect_crse_numb = sc.scbcrse_crse_numb
13 AND sp.spriden_change_ind IS NULL
14 AND sf.sfrstcr_pidm = sp.spriden_pidm
15 AND sf.sfrstcr_term_code = '200410'
16 GROUP BY sp.spriden_id, sp.spriden_last_name
17 ORDER BY sp.spriden_id, sp.spriden_last_name
18 /
SPRIDEN_ID SPRIDEN_LA STUDENTINFO
---------- ---------- --------------------------------------------------
999999999 Mendenhall ALLH240016,SHMT1020013,SHMT2060013
|
|
|
|
| Re: Loops [message #11096 is a reply to message #11059] |
Fri, 05 March 2004 05:28 |
Brandis
Messages: 11
Registered: February 2004
|
Junior Member |
|
|
Thanks Barbara. I have been reading on the stagg approach and found it very interesting. I haven't tried it yet, however since I am familiar with C++ and Java I see the similarities. I'll let you know how the report came out after I run it.
Thanks again,
Brandis
|
|
|
|
| Re: Loops [message #11097 is a reply to message #11059] |
Fri, 05 March 2004 07:36 |
Brandis
Messages: 11
Registered: February 2004
|
Junior Member |
|
|
Is there anyway I can combine everything. I am using the aggregate function that Barbara gave me previously on the posting and I want the output to be:
999999999MendenhallALL240016SHMT1020013SHMT2060013
But when I try to concatenate everything like:
COLUMN studentinfo FORMAT A50
SELECT sp.spriden_id||sp.spriden_last_name||
STRAGG ( DISTINCT ss.ssbsect_subj_code
|| sc.scbcrse_cipc_code
|| ss.ssbsect_crse_numb
|| ss.ssbsect_seq_numb
|| SUBSTR (sc.scbcrse_cont_hr_low, 1, 2))
AS studentinfo
FROM ssbsect ss, scbcrse sc, sfrstcr sf, spriden sp
WHERE ss.ssbsect_crn = sf.sfrstcr_crn
AND ss.ssbsect_term_code = '200410'
AND ss.ssbsect_subj_code = sc.scbcrse_subj_code
AND ss.ssbsect_crse_numb = sc.scbcrse_crse_numb
AND sp.spriden_change_ind IS NULL
AND sf.sfrstcr_pidm = sp.spriden_pidm
AND sf.sfrstcr_term_code = '200410'
GROUP BY sp.spriden_id, sp.spriden_last_name
ORDER BY sp.spriden_id, sp.spriden_last_name
/
I get the error that FROM cannot be found.
How would I combine all of this together???
Thanks in advance,
Brandis
|
|
|
|
| Re: Loops [message #11099 is a reply to message #11097] |
Fri, 05 March 2004 21:29 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You will need to comment out or remove the line in the type body that concatenates the comma (line 18 in the example below) to get rid of the commas. You will also need to use the entire original query as an in-line view and contenate the columns together in an outer select statement. Please see the example below.
scott@ORA92> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 );
25 /
Type created.
scott@ORA92> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total
18 -- || ','
19 || value;
20 return ODCIConst.Success;
21 end;
22
23 member function ODCIAggregateTerminate(self IN string_agg_type,
24 returnValue OUT varchar2,
25 flags IN number)
26 return number
27 is
28 begin
29 returnValue := ltrim(self.total,',');
30 return ODCIConst.Success;
31 end;
32
33 member function ODCIAggregateMerge(self IN OUT string_agg_type,
34 ctx2 IN string_agg_type)
35 return number
36 is
37 begin
38 self.total := self.total || ctx2.total;
39 return ODCIConst.Success;
40 end;
41
42
43 end;
44 /
Type body created.
scott@ORA92> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /
Function created.
scott@ORA92> COLUMN studentinfo FORMAT A60
scott@ORA92> SELECT spriden_id || spriden_last_name || studentinfo AS studentinfo
2 FROM (SELECT sp.spriden_id, sp.spriden_last_name,
3 STRAGG (DISTINCT ss.ssbsect_subj_code
4 || sc.scbcrse_cipc_code
5 || ss.ssbsect_crse_numb
6 || ss.ssbsect_seq_numb
7 || SUBSTR (sc.scbcrse_cont_hr_low, 1, 2))
8 AS studentinfo
9 FROM ssbsect ss, scbcrse sc, sfrstcr sf, spriden sp
10 WHERE ss.ssbsect_crn = sf.sfrstcr_crn
11 AND ss.ssbsect_term_code = '200410'
12 AND ss.ssbsect_subj_code = sc.scbcrse_subj_code
13 AND ss.ssbsect_crse_numb = sc.scbcrse_crse_numb
14 AND sp.spriden_change_ind IS NULL
15 AND sf.sfrstcr_pidm = sp.spriden_pidm
16 AND sf.sfrstcr_term_code = '200410'
17 GROUP BY sp.spriden_id, sp.spriden_last_name
18 ORDER BY sp.spriden_id, sp.spriden_last_name)
19 /
STUDENTINFO
------------------------------------------------------------
999999999MendenhallALLH240016SHMT1020013SHMT2060013
|
|
|
|
| Re: Loops [message #11113 is a reply to message #11099] |
Mon, 08 March 2004 04:17 |
Brandis
Messages: 11
Registered: February 2004
|
Junior Member |
|
|
|
Thanks Barbara. I had figured out another way around that. I had already taken out the commas so I had the courses together. What I did was I SET SPACE 0 and COLSEP ''. That way I didn't have to go back and change the function, since I will need to use it again for another department. Thank you though so much. I didn't realize PL/SQL was so complex!!! :-)
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 21:50:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
