Ask Question
Help Others
Home › Blogs › Lepa's blog

Identify Records Behind a Page and Subpage

Mon, 06/09/2008 - 11:15am — Lepa
Your rating: None Average: 3.7 (3 votes)

Use the following query to get records behind not only the page but sub-pages on a page.

WITH my_data AS
     (SELECT   subpnlname
          FROM pspnlfield
         WHERE pnlname = :1 AND subpnlname <> ' '
      GROUP BY subpnlname)
SELECT   recname
    FROM pspnlfield, my_data
   WHERE pnlname = :1 OR pnlname = my_data.subpnlname
GROUP BY recname;

Same query as above but written differently (suggested by a colleague of mine):
SELECT   recname
    FROM pspnlfield a
   WHERE pnlname = :1
      OR EXISTS (
            SELECT 'x'
                FROM pspnlfield b
               WHERE pnlname = :1
                 AND subpnlname <> ' '
                 AND a.pnlname = b.subpnlname)
GROUP BY recname;

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Thu, 02/19/2009 - 1:17pm
#1
kpstechie's picture
kpstechie
User offline. Last seen 7 years 7 weeks ago. Offline
Joined: 02/18/2009
Posts: 3
Re: Identify Records Behind a Page and Subpage

This Query has helped me a lost...thanks

Top
Thu, 11/18/2010 - 4:48pm
#2
Joe Mumbauer's picture
Joe Mumbauer (not verified)
Re: Identify Records Behind a Page and Subpage

The given queries only work for one subpanel level deep. If you want it to recurse and provide as many subpanels as possible, you can use the following (Oracle Syntax) SQL:

SELECT recname
FROM pspnlfield
WHERE recname<>' '
START WITH pnlname = :1
CONNECT BY PRIOR subpnlname = pnlname and PRIOR fieldtype=11
group by recname;

Top