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 by
> into two queries, but now I really need to combine into one.
> One of the items I need to select as a single select item is
actually a list
> of items tied to the main recordset I'm dealing with and using a
> field to indicate order. It is descriptive text broken into as many
> pieces, so order is crucial.
> Two problems I'm having: First, the db engine sees an 'order by'
> an end of an sql statement, which it normally is.
> 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 one
> 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/SQL
> however, I'm sending it from an asp page at this point.
> 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,
> '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