| Query returning 6 Records [message #355873] |
Tue, 28 October 2008 04:26  |
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  |
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 #355918 is a reply to message #355873] |
Tue, 28 October 2008 09:10 |
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 |
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 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
