Hell Chris,
i have tables called t1 and t2.
Table T1 Script as follows:
DROP TABLE T1 CASCADE CONSTRAINTS;
CREATE TABLE T1
(
ID NUMBER
)
Insert into T1(ID) Values(2001);
Insert into T1(ID) Values(3001);
Insert into T1(ID) Values(5001);
Insert into T1(ID) Values(6001);
Insert into T1(ID) Values(1001);
COMMIT;
Table T2 Script as follows:
DROP TABLE T2 CASCADE CONSTRAINTS;
CREATE TABLE T2
(
ID NUMBER
)
Insert into T2(ID) Values(3);
Insert into T2(ID) Values(4);
Insert into T2(ID) Values(5);
COMMIT;
Now coming to the main thing I want to show 10 blank rows every time for which i have created a Query :
SELECT b.id FROM(SELECT LEVEL id FROM dual CONNECT BY LEVEL <=10) a
LEFT JOIN
(SELECT * FROM t2) b ON a.id = b.id
ID
3
4
5
Which works for table T2.Now same thing i wanted for table t1 which does not work.
When i use same logic for table T1 it has different ID compare to table T2.So when i run :
SELECT b.id FROM(SELECT LEVEL id FROM dual CONNECT BY LEVEL <=10) a
LEFT JOIN
(SELECT * FROM t1) b ON a.id = b.id
It shows me blank results. if i change a.id = 1 then it shows results but if i have 4 ID in my column then it will show 14 rows which i dont want.
It should always display 10 rows.
The values in t1 don't match to any values in the connect by!
So "a.id = b.id" is false for all rows in t1.
Is your requirement:
- Display 10 rows
- First take these from T1
- If there's less than 10 in T1, pad the remaining rows with blanks
?
If so you could implement it by:
- Unioning t1 with the connect by
- Ordering this by id
- Limiting the results to the first 10:
SQL> DROP TABLE T1 CASCADE CONSTRAINTS;
Table dropped.
SQL>
SQL> CREATE TABLE T1
2 (
3 ID NUMBER
4 );
Table created.
SQL>
SQL> Insert into T1(ID) Values(2001);
1 row created.
SQL> Insert into T1(ID) Values(3001);
1 row created.
SQL> Insert into T1(ID) Values(5001);
1 row created.
SQL> Insert into T1(ID) Values(6001);
1 row created.
SQL> Insert into T1(ID) Values(1001);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> with rws as (
2 select id from t1
3 union all
4 SELECT null id FROM dual CONNECT BY LEVEL <=10
5 order by 1
6 )
7 select * from rws
8 where rownum <= 10;
ID
----------
1001
2001
3001
5001
6001
10 rows selected.