Solved

PL/SQL - Need to split up a result set and then select it based on the group

Posted on 2007-11-15
5
776 Views
Last Modified: 2013-12-19
I need to group this result set into 4 even groups.  The total rows of this result set
will always be the same number.  At the sametime, I would like to be able
to send in a parameter to ONLY select the group number I need, i.e. category = 1, category = 2,
category = 3.  I need to do this because I will be running this code once a week for the month.
I thought that the Ntile function would do the job but it does nto seem to work, can someone
help me?
SELECT pat.pat_id,

            pat.pat_first_name,

            pat.pat_last_name,

            pat.pat_street_addr,

            pat.pat_city,

            pat.pat_state,

            decode(pat.pat_zip, NULL, '         ', pat.pat_zip),

            pat.pat_cc_nbr,

            ntile(4) over     (order by pat.pat_id) as category

     FROM   tbf0_patient pat

     WHERE  pat.pat_cc_exp_date = 1007

       AND pat.pat_cc_nbr is NOT NULL

      AND category = 1;

Open in new window

0
Comment
Question by:farekat
  • 3
  • 2
5 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 20291668
category isn't known at "where clause" time, it's derived by scanning the result set.  So inline the main query, and then select your category from that.

Try this...  


select * from
(SELECT pat.pat_id, pat.pat_first_name, pat.pat_last_name, pat.pat_street_addr, pat.pat_city, pat.pat_state,
       DECODE(pat.pat_zip, NULL, '      ', pat.pat_zip), pat.pat_cc_nbr,
       NTILE(4) OVER(ORDER BY pat.pat_id) AS CATEGORY
  FROM tbf0_patient pat
 WHERE pat.pat_cc_exp_date = 1007 AND pat.pat_cc_nbr IS NOT NULL)
where CATEGORY = 1;
0
 

Author Comment

by:farekat
ID: 20291773
Cool, that worked, is there another way to be able to do this select statement without using the ntile function
and having to pass in which category I'd like to use?  I'd like to avoid having to pass in this parameter at all
now...that's a double question but i was just wondering if you would know.  Like how Selet TOP (returns the first)
set of rows, is there something similar to that like select first (1/4) of data, then the 2nd (1/4) of data
or is this it??    
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20291818
Do you know going in how many rows there will be?  
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20291855
if you do, you could try something like this...

where category_number and total_number_of_rows are constants you define

SELECT *
  FROM (SELECT pat.pat_id, pat.pat_first_name, pat.pat_last_name, pat.pat_street_addr, pat.pat_city,
               pat.pat_state, DECODE(pat.pat_zip, NULL, '      ', pat.pat_zip), pat.pat_cc_nbr, ROWNUM rn
          FROM tbf0_patient pat
         WHERE pat.pat_cc_exp_date = 1007
           AND pat.pat_cc_nbr IS NOT NULL
           AND ROWNUM <=(category_number * CEIL(total_number_of_rows / 4)))
 WHERE rn > (category_number - 1) * CEIL(total_number_of_rows / 4)
0
 

Author Comment

by:farekat
ID: 20292413
I just figured out that the number of rows are going to change, so I am thinking that I have to change my
approach around this entire thing...you still get the points thank you so much!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now