Re: selecting a multi-row result as part of a select
- The way I understand your question the part of you query that is
giving you problems in the subquery with the alias "Description" that
is in you main SELECT statement. This subquery may return up to 6
rows with scrtext_text for a given combination of ssbsect_subj_code,
ssbsect_crse_numb, and scrtext_eff_term.
Assuming, for dicussion purposes, that the subquery returns 6 rows,
would you prefer
1. That the final result set have 6 rows, one for each of the rows
returned by the subquery, or
2. That the final result set have 1 row, with the six values of
scrtext_text concatenated into a single string?
--- In SQLQueriesNoCode@yahoogroups.com, "Michael Gerholdt"
> I've got an interesting problem here which before I resolved bysplitting
> into two queries, but now I really need to combine into one.actually a list
> One of the items I need to select as a single select item is
> of items tied to the main recordset I'm dealing with and using asequence
> field to indicate order. It is descriptive text broken into as manyas 6
> pieces, so order is crucial.phrase as
> Two problems I'm having: First, the db engine sees an 'order by'
> an end of an sql statement, which it normally is.to order
> Second, if I take out the order by (for academic purposes; I need
> it!) then it tells me a single-row subquery returns more than onerow.
> I need both to order it and to flatten it into one 'thing.'
> Here's the query; it hits on Oracle. I suppose the best would be to
> back out into two select statements, and put it into a PL/SQLprocedure;
> however, I'm sending it from an asp page at this point.m.scrtext_crse_numb)
> Thanks for any assists.
> stvterm_desc||' '||ssbsect_subj_code||' '||ssbsect_crse_numb||'
> '||nvl(ssbsect_seq_numb,'00') Course,
> ssbsect_subj_code Subj,
> ssbsect_crse_numb Crse,
> nvl(ssbsect_crse_title,y.scbcrse_title) CourseName,
> (SELECT m.scrtext_text
> FROM scrtext m
> WHERE m.scrtext_subj_code = ssbsect_subj_code AND
> m.scrtext_crse_numb = ssbsect_crse_numb AND
> m.scrtext_eff_term = (select max(z.scrtext_eff_term)
> from scrtext z
> where z.scrtext_subj_code =
> m.scrtext_subj_code AND
> z.scrtext_crse_numb =
> Order by m.scrtext_seqno) Description,y.scbcrse_crse_numb)
> 'blue01' button,
> ',' Initial_Announcement
> FROM ssbsect, scbcrse y, stvterm
> WHERE y.scbcrse_subj_code = ssbsect_subj_code
> AND y.scbcrse_crse_numb = ssbsect_crse_numb
> AND ssbsect_term_code = &TermCode
> AND y.scbcrse_eff_term <= ssbsect_term_code
> AND ssbsect_ssts_code = 'A'
> AND ssbsect_prnt_ind = 'Y'
> AND ssbsect_subj_code <> 'GEN'
> AND ssbsect_crse_numb <> 099
> AND stvterm_code = ssbsect_term_code
> AND y.scbcrse_activity_date = (select max(x.scbcrse_activity_date)
> from scbcrse x
> where x.scbcrse_subj_code =
> y.scbcrse_subj_code AND
> x.scbcrse_crse_numb =
> ORDER BY CourseName
> The way I understand your question the part of you query that iscorrect
> giving you problems in the subquery with the alias "Description" that
> is in you main SELECT statement. This subquery may return up to 6
> rows with scrtext_text for a given combination of ssbsect_subj_code,
> ssbsect_crse_numb, and scrtext_eff_term.
> Assuming, for dicussion purposes, that the subquery returns 6 rows,The latter is exactly what I'm after. And the text has to be ordered by the
> would you prefer
> 1. That the final result set have 6 rows, one for each of the rows
> returned by the subquery, or
> 2. That the final result set have 1 row, with the six values of
> scrtext_text concatenated into a single string?
sequence number, which may be a problem.
I may have to write a function that does this and select the function,
passing it course number and section.