?
Solved

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

Posted on 2007-11-15
5
Medium Priority
?
784 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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 74

Expert Comment

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

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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

770 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