Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
?
786 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 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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