Solved

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

Posted on 2007-11-15
5
783 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 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 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

690 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