Loading ...
Sorry, an error occurred while loading the content.

279Re: selecting a multi-row result as part of a select

Expand Messages
  • jaggs0213
    Jul 1 6:44 AM
      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"
      <gerholdt@f...> wrote:
      > 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
      as 6
      > pieces, so order is crucial.
      > Two problems I'm having: First, the db engine sees an 'order by'
      phrase as
      > an end of an sql statement, which it normally is.
      > Second, if I take out the order by (for academic purposes; I need
      to order
      > 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
      break it
      > 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.
      > SELECT
      > 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
    • Show all 3 messages in this topic