Home » SQL & PL/SQL » SQL & PL/SQL » Query returning 6 Records
Query returning 6 Records [message #355873] Tue, 28 October 2008 04:26 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
hi All ,
suppose i have a table Employee ,
it can have any number of data rows .
i write a query . now i want the query to return
6 rows every tiime , even if there is no data in the table ,then it should return 6 null rows or may be with data in just 1 column as rownum.
or even 12 rows in the table ,then it will display 6 rows data rows only.
if table has 3 rows then rest threee null rows making it 6 again.

NOTE :- i have this requirement in Oracle Reports but easiest way for me to do it is in a QUERY.
NOTE :- "you can use a temporary table and insert sequence values from 1-6 to play with number of rows if u want "

Regards
Imtiaz Shah
Re: Query returning 6 Records [message #355882 is a reply to message #355873] Tue, 28 October 2008 05:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will give you a set of clues and you try to build the query. If you are stuck somewhere come back to us and I am sure somebody will be able to help you.

a) You need a row generator query to meet the required number of records condition
b) Inner query needs to select the record along with row_number analytic function
c) Do an outer join with the row generator with inner query on the row_number column

But I am not sure which sure which 6 records you want to select from the table ?

Hope his helps.

Regards

Raj
Re: Query returning 6 Records [message #355892 is a reply to message #355873] Tue, 28 October 2008 06:02 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sispk6,

sispk6 wrote on Tue, 28 October 2008 14:56

NOTE :- "you can use a temporary table and insert sequence values from 1-6 to play with number of rows if u want "



You have given one clue yourself for your problem. So what is stopping you to implement it? http://img2.mysmiley.net/imgs/smile/confused/confused0024.gif

Regards,
Jo
Re: Query returning 6 Records [message #355918 is a reply to message #355873] Tue, 28 October 2008 09:10 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
I hope this will work.

Quote:
create view emp_10 as select * from emp where deptno=10;
create view emp_20 as select * from emp where deptno=20
create table emp_none as select * from emp;
delete from emp_none;
--I inserted 6 Null rows into emp_none

select * from (select * from emp_10
union all
select * from emp_none) where rownum <=6 order by deptno nulls last;

select * from (select * from emp_20
union all
select * from emp_none) where rownum <=6 order by deptno nulls last;



Please suggest any better way to do this using Analytics.

Re: Query returning 6 Records [message #355982 is a reply to message #355918] Tue, 28 October 2008 21:23 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
thanx S.Rajaram for your Suggestions , im trying it from yesterday in different ways.
kindly post a test solution for me.
Thanks in advance
Regards
Imtiaz
Re: Query returning 6 Records [message #355984 is a reply to message #355982] Tue, 28 October 2008 22:01 Go to previous message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
SQL> desc TBL_EXAMINATION_PASSED;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RECORD_ID                                 NOT NULL VARCHAR2(21)
 PNO                                       NOT NULL VARCHAR2(10)
 DEGREE_CODE                               NOT NULL VARCHAR2(4)
 GRADE_CODE                                         VARCHAR2(4)
 EXAM_YEAR                                          NUMBER(4)
 PERCENTAGE                                         NUMBER(3)
 RECORD_ENTRY_DATE                         NOT NULL DATE
 RECORD_STATUS                             NOT NULL VARCHAR2(1)
 RECORD_ENTERED_BY                         NOT NULL VARCHAR2(10)
 POSITION                                           VARCHAR2(1)
 HIGHEST_QUALIFICATION                     NOT NULL VARCHAR2(1)
 ADMIN_CHECK                               NOT NULL VARCHAR2(1)
 MAJOR_SUBJECTS                            NOT NULL VARCHAR2(500)
 GPA                                                NUMBER(3,2)
 COUNTRY_CODE                              NOT NULL VARCHAR2(4)
 REGISTRATION_NO                                    VARCHAR2(30)
 COMPLETED                                 NOT NULL VARCHAR2(1)
 NAME_INSTITUTE                            NOT NULL VARCHAR2(100)
 ADDRESS_INSTITUTE                         NOT NULL VARCHAR2(100)
 BOARD_UNI_NAME                            NOT NULL VARCHAR2(100)
 FINANCED_BY                                        VARCHAR2(50)
 ACTIVE                                    NOT NULL VARCHAR2(1)
 RECORD_UPDATE_DATE                        NOT NULL DATE
 SPECIFICATION                                      VARCHAR2(60)

-------------------------------------------------------------------
SQL> desc temp_imtiaz;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PNO                                                VARCHAR2(10)
 RECORDS                                            NUMBER
------------------------------------------------------------------
select a.* , rownum  from  (select t.* , rownum from TBL_EXAMINATION_PASSED T
                 full outer join temp_imtiaz on t.pno=nvl(temp_imtiaz.pno,t.pno) )a

this onw is working allright butttttt
but i dont want it to do in a subquery caz it voil8ates my restrictions

regards
Imtiaz

[Updated on: Tue, 28 October 2008 22:02]

Report message to a moderator

Previous Topic: Autonomous transaction on trigger
Next Topic: Performance Issue...
Goto Forum:
  


Current Time: Thu Dec 08 19:59:37 CST 2016

Total time taken to generate the page: 0.09072 seconds