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
Solved

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

808 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