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

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

Expand Messages
  • jaggs0213
    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
    Message 1 of 3 , Jul 1, 2003
    • 0 Attachment
      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
      splitting
      > 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
      sequence
      > 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
      row.
      >
      > 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
      procedure;
      > 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 =
      m.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 =
      y.scbcrse_crse_numb)
      > ORDER BY CourseName
    • Michael Gerholdt
      ... correct ... The latter is exactly what I m after. And the text has to be ordered by the sequence number, which may be a problem. I may have to write a
      Message 2 of 3 , Jul 3, 2003
      • 0 Attachment
        > 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.
        >

        correct



        > 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?
        >

        The latter is exactly what I'm after. And the text has to be ordered by the
        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.
      Your message has been successfully submitted and would be delivered to recipients shortly.