selecting sequences
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: selecting sequences

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    selecting sequences

    I have a query that selects sequences of paragraphs based on their order, i want to know if there is a better performing way of doing the same thing. any help or comments is appreciated.

    tables:

    OMS_MENUITEMS
    -------------
    ID
    NAMEE
    NAMEF

    OMS_MENUITEMREFERENCES
    ----------------------
    MENUITEM_ID
    SECTION_ID
    FIRSTPARAGRAPH_ID
    LAST_PARAGRAPH_ID

    OMS_SECTIONS
    ------------
    ID
    NAMEE
    NAMEF
    SECTIONORDER

    OMS_PARAGRAPHS
    --------------
    ID
    SECTION_ID
    TEXTE
    TEXTF
    NAMEE
    NAMEF
    PARAGRAPHORDER
    NAMEF


    The basic jist is that you have sections (chapters of a manual) which contain paragraphs. The sections and paragraphs or ordered by a numeric key in the SECTIONORDER or PARAGRAPHORDER columns. There is also a menu that lists the sections, when a menuitem is clicked on i need to get the paragraphs referenced by that menuitem. In the OMS_MENUITEMREFERENCES table the firstparagraph_id can be null or point to a paragraph id, same for the lastparagraph_id. Based on the id of the paragraphs in the first and last columns i need to get all paragraphs that are between the order of the specified paragraphs. If the firstparagraph_id is null it means get them from order 0 to the lastparagraph_id, if the lastparagraph_id is null it means get them from the first to the last one (thats where i used the decode from 0 to 1000000).

    SELECT OMS_PARAGRAPHS.ID,
    OMS_PARAGRAPHS.SECTION_ID,
    OMS_PARAGRAPHS.NAMEE
    OMS_PARAGRAPHS.TEXTE OMS_PARAGRAPHS.PARAGRAPHORDER,
    OMS_SECTIONS.SECTIONORDER
    FROM OMS_PARAGRAPHS,
    OMS_SECTIONS,
    OMS_MENUITEMREFERENCES
    WHERE OMS_MENUITEMREFERENCES.MENUITEM_ID = pi_menuitem_id
    AND OMS_SECTIONS.ID = OMS_MENUITEMREFERENCES.SECTION_ID
    AND OMS_PARAGRAPHS.SECTION_ID = OMS_SECTIONS.ID
    AND OMS_PARAGRAPHS.ID IN (SELECT PRGH.ID
    FROM OMS_PARAGRAPHS PRGH
    WHERE PRGH.PARAGRAPHORDER >= (SELECT a.PARAGRAPHORDER FROM OMS_PARAGRAPHS a WHERE a.id = DECODE(OMS_MENUITEMREFERENCES.FIRSTPARAGRAPH_ID, null, 0, OMS_MENUITEMREFERENCES.FIRSTPARAGRAPH_ID))
    AND PRGH.PARAGRAPHORDER <= (SELECT b.PARAGRAPHORDER FROM OMS_PARAGRAPHS b WHERE b.id = DECODE(OMS_MENUITEMREFERENCES.LASTPARAGRAPH_ID, null, 1000000, OMS_MENUITEMREFERENCES.LASTPARAGRAPH_ID)))
    ORDER BY OMS_SECTIONS.SECTIONORDER, OMS_PARAGRAPHS.PARAGRAPHORDER;

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    Try it without this part:
    Code:
    ...
    AND OMS_PARAGRAPHS.ID IN (SELECT PRGH.ID
    FROM OMS_PARAGRAPHS PRGH
    WHERE PRGH.PARAGRAPHORDER >= (SELECT a.PARAGRAPHORDER FROM
    OMS_PARAGRAPHS a WHERE a.id = 
    DECODE(OMS_MENUITEMREFERENCES.FIRSTPARAGRAPH_ID, null, 0,
     OMS_MENUITEMREFERENCES.FIRSTPARAGRAPH_ID))
    AND PRGH.PARAGRAPHORDER <= (SELECT b.PARAGRAPHORDER FROM 
    OMS_PARAGRAPHS b WHERE b.id = 
    DECODE(OMS_MENUITEMREFERENCES.LASTPARAGRAPH_ID, null, 1000000,
    OMS_MENUITEMREFERENCES.LASTPARAGRAPH_ID)))
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    90
    well i can remove that part, but then the query will return all paragraphs with section_id the same as the OMS_MenuItems.section_id, and not only the paragraphs in the sequence specified by the firstparagraph_id and lastparagraph_id.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width