Home » RDBMS Server » Performance Tuning » Need help with Queries
| Need help with Queries [message #554917] |
Sat, 19 May 2012 06:25  |
 |
supiash
Messages: 70
Registered: May 2012
Location: bangalore
|
Member |
|
|
Hi,
I need a help on PL/SQL
I have A table EN and few columns and its values are as below
SQL> select distinct(cpstpn),concno from en ORDER BY CPSTPN;
CPSTPN CONCNO counnt(concno)
---------- ---------- ---------------
0 0 1234
0 2 1578
0 4 1238
0 5 800
0 7 567
1 2 1234
1 4 1587
3 0 1489
3 1 1287
select cpstpn,concno,enum,lctype,synthetic from en where cpstpn=0 and concno=0 and abbrnac=10 order by enum asc;
CPSTPN CONCNO ENUM LCTYPE SYNTHETIC
---------- ---------- ---------- -------- --------------------------
0 0 0 MK3MCI 00POOLE1000MK3MCI20000000
0 0 1 MK3STSP
0 0 2 MK3STSP
0 0 3 MK3STSP 00POOLE100MK3STSP45571400
0 0 4 MK3STSP
0 0 5 MK3STSP
0 0 6 MK3STSP
0 0 7 MK3STSP 00POOLE100MK3STSP19969600
0 0 8 MK3STSP
0 0 9 MK3STSP 00POOLE100MK3STSP45549500
0 0 10 MK3STSP
CPSTPN CONCNO ENUM LCTYPE SYNTHETIC
---------- ---------- ---------- -------- --------------------------
0 0 11 MK3STSP
0 0 12 MK3STSP
0 0 13 MK3STSP 00POOLE100MK3STSP45476100
0 0 14 MK3STSP
0 0 15 MK3STSP
0 0 16 MK3MCI
0 0 17 MK3STSP 00POOLE100MK3STSP19852600
0 0 18 MK3STSP
My query
1 cpstpn has many Concno
1 concno has upto 2048 ENUM
There is one more Column Name Recstat
if the recstat column is 0 consider it is free else not free
Conside 1 cpstpn has 4 concno 1 concno has 1500 enum
1 cpstpn=4*1500=600Enum
I want to pick up 24 enum's
such that it goes as below
1) FIND A CPSTPN,CONCNO by listing cpstpn having least free device and again in that concno having least free devices
Ex: consder cpstpn1 has concno 1 and 2 and count of free device(recstat=0) is 510
cpstpn concno free_count
1 0 210
1 1 300
---------------------
total: 1 510
consder cpstpn 2 has concno 1 and 2 and count of free device(recstat=0) is 710
cpstpn concno count(concno)
2 1 250
2 2 460
---------------------
total: 1 710
My final list should look like this
that is in the order of concno having least free equipment
(Need the query for the below output)
cpstpn concno count(concno)
1 0 210
2 1 250
1 1 310
2 2 460
so that i can pick the cpstpn=1 and concno=0 in case i find the a single concentrator having free equipments greater than requested ENUM(i.e in this case 24)
all the concno has free equipments greater than 24
*******************************************************************************************************
select count(*) froom en where cpstn=1 and concno=0 gives me 780 records(which include recstat=0 and recstat!=0)
780/64=12.18
i need to split the 780 into rows of 64 if there are remaining records that needs to go to next element(Here it is LCT Column which is imaginary)
like below
CPSTPN CONCNO ENUM LCTYPE SYNTHETIC LCT
---------- ---------- ---------- -------- ---------------------------------
0 0 0 MK3MCI 00POOLE1000MK3MCI20000000 1
0 0 1 MK3STSP 1
. . .
. . .
0 0 63 MK3STSP 00POOLE100MK3STSP45571400 2
. . .
. . .
0 0 127 MK3STSP 2
0 0 128 MK3STSP 3
0 0 129 MK3STSP 3
Again in this i need to find the count of free devices(recstat=0 or synnthrtic field is not null) in each LCT
LCT FREE COUNT
1 20
2 30
3 1
SO i wnat to now to dstribute the input 24 Equipments in the above free count rows in round robin fashion
(free count rows is noth but row having snthetic field as null)
i.e as below
CPSTPN CONCNO ENUM LCTYPE SYNTHETIC LCT
0 0 2 MK3MCI 1
0 0 65 MK3STSP 2
0 0 128 MK3STSP 3
my 24 enum will be as belo
2
65
128
.
if the count of free devices becomes zero during picking of enum from skip & go to next lct
********************************************************************************************
in case i dint find a single concno having enough free equiments as requested i need to pick the enum across concentrators(as min imu as possible) under single cpstpn having free quipments greater than >= my request
requested enum=24 and free counts in conco is as below
cpstpn concno count(concno)
1 0 8
1 1 3
2 1 13
2 2 6
2 3 4
i need to pick up cpstpn 2 and conco=1 and alaccote= 13 enum as explained up then
i need to pick up cpstpn 2 and conco=2 and alaccote= 6 enum as explained up then
i need to pick up cpstpn 2 and conco=2 and alaccote= 3 enum as explained up
so that input request of 24 ENUM is satisfied
ccondition: while picking the cpstpn here i will have to ensue that i pick the cpstpn having least free devices first > my input request always first
Also when two or three jobs comes @ once for 24(or any num) as request there should not be any collision
[Updated on: Sat, 19 May 2012 06:59] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need help with Queries [message #554945 is a reply to message #554941] |
Sat, 19 May 2012 18:37  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is what I think I understand so far:
Your problem only involves one table and you have provided a create table statement for that. It is en1 in the example in your most recent post and en in the example in your first post.
You have provided insert and update statements to populate the en1 table in your most recent post. This data does not seem to match the selects in your previous posts, so I guess they should be ignored and the latest post should be considered a complete restatement of the problem. Additionally, the first select that I started to look at in your first post was invalid, as it selected two columns and the alleged output showed three columns, so I didn't even look at the rest. It might help if you could provide a smaller test case, but this will do as long as you can make the problem clear.
You have multiple places in your latest post where you refer to desired output, so I can't tell which you want or if you want the results of multiple queries or if you are trying to describe intermediary steps that you think you need to take or what. You need to provide one clear example of ultimate output that you want. If there are multiple problems, then I suggest that you start with just one.
Since I can't tell what you want, it is even harder to tell the requirements that define it. You have listed some steps. I can't tell if those are intended as sub-queries or what. If they don't return the desired results, then you can't go on to the next step. So, you might want to just start with presenting your problem as the first step, whatever that is.
I would recommend a format like the following to enable us to understand the problem, clearly labeling each thing:
-- creation of table:
-- population of table:
insert into en1 ...;
update en1 ...;
-- current data in table after population:
-- desired output of query:
column1 column2 ...
------- ------- ...
col1data col2data ...
...
-- explanation of requirements that define the output:
?
-- what you tried so far:
?
|
|
|
|
| Re: Need help with Queries [message #554946 is a reply to message #554945] |
Sat, 19 May 2012 22:58 |
|
supiash
Messages: 70
Registered: May 2012
Location: bangalore
|
Member |
|
|
-- Creation of table
CREATE TABLE en1
(
CLUSTR NUMBER(2),
SITEID VARCHAR2(5),
ABBRNAC NUMBER(2),
CPSTPN NUMBER(2),
CONCNO NUMBER(2),
ENUM NUMBER(4),
LCTYPE VARCHAR2(8),
SYNTHETIC VARCHAR2(26)
);
-- Population of Data into the Table
1)Loading data onto CLUSTR=0 and CONCNO=0
insert into en1 values (0,'POOLE',1,0,0,0,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,1,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,2,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,3,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,4,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,5,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,6,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,7,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,8,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,9,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,10,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,11,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,12,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,13,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,14,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,15,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,16,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,17,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,18,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,19,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,20,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,21,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,22,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,23,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,24,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,25,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,26,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,27,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,28,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,29,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,30,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,31,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,32,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,33,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,34,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,35,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,36,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,37,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,38,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,39,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,40,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,41,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,42,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,43,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,44,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,45,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,46,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,47,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,48,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,49,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,50,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,51,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,52,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,53,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,54,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,55,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,56,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,57,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,58,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,59,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,60,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,61,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,62,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,63,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,64,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,65,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,66,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,67,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,68,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,69,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,70,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,71,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,72,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,73,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,74,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,75,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,76,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,77,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,78,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,79,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,80,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,81,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,82,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,83,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,84,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,85,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,86,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,87,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,88,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,89,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,90,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,91,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,92,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,93,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,94,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,95,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,96,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,97,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,98,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,99,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,100,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,101,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,102,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,103,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,104,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,105,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,106,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,107,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,108,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,109,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,110,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,111,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,112,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,113,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,114,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,115,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,116,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,117,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,118,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,119,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,120,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,121,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,122,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,123,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,124,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,125,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,126,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,127,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,128,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,129,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,130,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,131,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,132,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,133,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,134,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,135,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,136,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,137,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,138,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,139,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,140,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,141,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,142,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,143,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,144,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,145,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,146,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,147,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,148,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,149,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,150,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,151,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,152,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,153,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,154,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,155,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,156,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,157,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,158,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,159,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,160,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,161,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,162,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,163,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,164,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,165,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,166,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,167,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,168,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,169,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,170,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,171,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,172,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,173,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,173,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,174,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,175,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,176,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,177,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,178,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,179,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,180,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,0,181,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,182,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,183,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,0,184,'MK3STSP','');
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 10 AND 50
AND CPSTPN = 0
AND CONCNO = 0;
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 80 AND 130
AND CPSTPN = 0
AND CONCNO = 0;
2)Loading data onto CLUSTR=0 and CONCNO=0
insert into en1 values (0,'POOLE',1,0,1,0,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,1,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,2,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,3,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,4,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,5,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,6,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,7,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,8,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,9,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,10,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,11,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,12,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,13,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,14,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,15,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,16,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,17,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,18,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,19,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,20,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,21,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,22,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,23,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,24,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,25,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,26,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,27,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,28,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,29,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,30,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,31,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,32,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,33,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,34,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,35,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,36,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,37,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,38,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,39,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,40,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,41,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,42,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,43,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,44,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,45,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,46,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,47,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,48,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,49,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,50,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,51,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,52,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,53,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,54,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,55,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,56,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,57,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,58,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,59,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,60,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,61,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,62,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,63,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,64,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,65,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,66,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,67,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,68,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,69,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,70,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,71,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,72,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,73,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,74,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,75,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,76,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,77,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,78,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,79,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,80,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,81,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,82,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,83,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,84,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,85,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,86,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,87,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,88,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,89,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,90,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,91,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,92,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,93,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,94,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,95,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,96,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,97,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,98,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,99,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,100,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,101,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,102,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,103,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,104,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,105,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,106,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,107,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,108,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,109,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,110,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,111,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,112,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,113,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,114,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,115,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,116,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,117,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,118,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,119,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,120,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,121,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,122,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,123,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,124,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,125,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,126,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,127,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,128,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,129,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,130,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,131,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,132,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,133,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,134,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,135,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,136,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,137,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,138,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,139,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,140,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,141,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,142,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,143,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,144,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,145,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,146,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,147,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,148,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,149,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,150,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,151,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,152,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,153,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,154,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,155,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,156,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,157,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,158,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,159,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,160,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,161,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,162,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,163,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,164,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,165,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,166,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,167,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,168,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,169,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,170,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,171,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,172,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,173,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,173,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,174,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,175,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,176,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,177,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,178,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,179,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,180,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,1,181,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,182,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,183,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,184,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,1,185,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,186,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,187,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,188,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,189,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,190,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,191,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,192,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,193,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,194,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,195,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,200,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,201,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,202,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,203,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,204,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,205,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,206,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,207,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,208,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,209,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,210,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,211,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,212,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,213,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,214,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,215,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,216,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,217,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,218,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,219,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,220,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,221,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,222,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,223,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,224,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,225,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,226,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,227,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,228,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,229,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,230,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,231,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,232,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,233,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,234,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,235,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,236,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,237,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,238,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,239,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,240,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,241,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,242,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,243,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,244,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,245,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,246,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,247,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,248,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,249,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,250,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,251,'MK2ORD','');
insert into en1 values (0,'POOLE',1,0,1,252,'MK2ORD','');
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 10 AND 20
AND CPSTPN = 0
AND CONCNO = 1;
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 70 AND 80
AND CPSTPN = 0
AND CONCNO = 1;
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 200 AND 240
AND CPSTPN = 0
AND CONCNO = 1;
3)LOADING THE DATA ON TO CPSTPN=0 AND CONCNO=2
insert into en1 values (0,'POOLE',1,0,2,0,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,1,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,2,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,3,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,4,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,5,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,6,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,7,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,8,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,9,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,10,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,11,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,12,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,13,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,14,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,15,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,16,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,17,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,18,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,19,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,20,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,21,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,22,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,23,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,24,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,25,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,26,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,27,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,28,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,29,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,30,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,31,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,32,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,33,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,34,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,35,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,36,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,37,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,38,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,39,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,40,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,41,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,42,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,43,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,44,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,45,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,46,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,47,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,48,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,49,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,50,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,51,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,52,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,53,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,54,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,55,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,56,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,57,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,58,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,59,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,60,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,61,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,62,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,63,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,64,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,65,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,66,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,67,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,68,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,69,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,70,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,71,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,72,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,73,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,74,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,75,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,76,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,77,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,78,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,79,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,80,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,81,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,82,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,83,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,84,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,85,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,86,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,87,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,88,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,89,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,90,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,91,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,92,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,93,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,94,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,95,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,96,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,97,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,98,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,99,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,100,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,101,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,102,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,103,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,104,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,105,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,106,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,107,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,108,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,109,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,110,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,111,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,112,'MK3MCI','');
insert into en1 values (0,'POOLE',1,0,2,113,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,114,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,115,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,116,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,117,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,118,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,119,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,120,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,121,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,122,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,123,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,124,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,125,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,126,'MK3STSP','');
insert into en1 values (0,'POOLE',1,0,2,127,'MK3STSP','');
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 0 AND 63
AND CPSTPN = 0
AND CONCNO = 2;
4)LOADING THE DATA ON TO CPSTPN=1 AND CONCNO=1
insert into en1 values (0,'POOLE',1,1,1,0,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,1,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,2,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,3,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,4,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,5,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,6,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,7,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,8,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,9,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,10,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,11,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,12,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,13,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,14,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,15,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,16,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,17,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,18,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,19,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,20,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,21,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,22,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,23,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,24,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,25,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,26,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,27,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,28,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,29,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,30,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,31,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,32,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,33,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,34,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,35,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,36,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,37,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,38,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,39,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,40,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,41,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,42,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,43,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,44,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,45,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,46,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,47,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,48,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,49,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,50,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,51,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,52,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,53,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,54,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,55,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,56,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,57,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,58,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,59,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,60,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,61,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,62,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,63,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,64,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,65,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,66,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,67,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,68,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,69,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,70,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,71,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,72,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,73,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,74,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,75,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,76,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,77,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,78,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,79,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,80,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,81,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,82,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,83,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,84,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,85,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,86,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,87,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,88,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,89,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,90,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,91,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,92,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,93,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,94,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,95,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,96,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,97,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,98,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,99,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,100,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,101,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,102,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,103,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,104,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,105,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,106,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,107,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,108,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,109,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,110,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,111,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,112,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,1,113,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,114,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,115,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,116,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,117,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,118,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,119,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,120,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,121,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,122,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,123,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,124,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,125,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,126,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,1,127,'MK3STSP','');
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 0 AND 63
AND CPSTPN = 1
AND CONCNO = 1;
5)LOADING THE DATA ON TO CPSTPN=1 AND CONCNO=3
insert into en1 values (0,'POOLE',1,1,3,0,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,1,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,2,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,3,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,4,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,5,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,6,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,7,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,8,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,9,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,10,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,11,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,12,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,13,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,14,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,15,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,16,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,17,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,18,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,19,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,20,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,21,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,22,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,23,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,24,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,25,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,26,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,27,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,28,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,29,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,30,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,31,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,32,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,33,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,34,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,35,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,36,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,37,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,38,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,39,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,40,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,41,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,42,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,43,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,44,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,45,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,46,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,47,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,48,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,49,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,50,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,51,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,52,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,53,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,54,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,55,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,56,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,57,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,58,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,59,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,60,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,61,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,62,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,63,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,64,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,65,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,66,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,67,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,68,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,69,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,70,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,71,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,72,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,73,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,74,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,75,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,76,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,77,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,78,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,79,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,80,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,81,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,82,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,83,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,84,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,85,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,86,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,87,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,88,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,89,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,90,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,91,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,92,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,93,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,94,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,95,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,96,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,97,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,98,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,99,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,100,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,101,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,102,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,103,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,104,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,105,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,106,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,107,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,108,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,109,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,110,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,111,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,112,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,113,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,114,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,115,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,116,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,117,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,118,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,119,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,120,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,121,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,122,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,123,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,124,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,125,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,126,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,127,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,128,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,129,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,130,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,131,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,132,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,133,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,134,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,135,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,136,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,137,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,138,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,139,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,140,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,141,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,142,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,143,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,144,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,145,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,146,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,147,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,148,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,149,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,150,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,151,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,152,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,153,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,154,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,155,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,156,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,157,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,158,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,159,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,160,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,161,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,162,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,163,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,164,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,165,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,166,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,167,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,168,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,169,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,170,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,171,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,172,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,173,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,173,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,174,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,175,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,176,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,177,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,178,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,179,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,180,'MK3MCI','');
insert into en1 values (0,'POOLE',1,1,3,181,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,182,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,183,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,184,'MK3STSP','');
insert into en1 values (0,'POOLE',1,1,3,185,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,186,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,187,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,188,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,189,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,190,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,191,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,192,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,193,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,194,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,195,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,200,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,201,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,202,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,203,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,204,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,205,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,206,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,207,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,208,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,209,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,210,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,211,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,212,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,213,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,214,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,215,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,216,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,217,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,218,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,219,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,220,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,221,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,222,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,223,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,224,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,225,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,226,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,227,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,228,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,229,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,230,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,231,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,232,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,233,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,234,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,235,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,236,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,237,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,238,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,239,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,240,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,241,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,242,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,243,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,244,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,245,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,246,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,247,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,248,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,249,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,250,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,251,'MK2ORD','');
insert into en1 values (0,'POOLE',1,1,3,252,'MK2ORD','');
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 10 AND 20
AND CPSTPN = 1
AND CONCNO = 3;
UPDATE en1
SET synthetic = Lpad(To_char(clustr), 2, 0)
||siteid
||Lpad(To_char(abbrnac), 2, 0)
||Lpad(To_char(lctype), 8, 0)
||Lpad(To_char(cpstpn), 2, 0)
WHERE enum BETWEEN 70 AND 80
AND CPSTPN = 1
AND CONCNO = 3;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need help with Queries [message #554958 is a reply to message #554955] |
Sun, 20 May 2012 02:05 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your misuse of terminology is a little confusing. The create table and insert statements constitute a test case. What you are calling a test case is a problem or desired query. So, for what you call test case 2, the following would be the desired query. You can use COUNT in it's analytic form to get a rolling count, ordered by enum, then divide that by 64, and use CEIL to round up. The result is a derived, not imaginary, column lct.
SCOTT@orcl_11gR2> SELECT cpstpn, concno, enum, synthetic,
2 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct
3 FROM en1
4 WHERE cpstpn = 0
5 AND concno = 1
6 AND (LCTYPE='MK3STSP' OR LCTYPE='MK3MCI')
7 /
CPSTPN CONCNO ENUM SYNTHETIC LCT
---------- ---------- ---------- -------------------------- ----------
0 1 0 1
0 1 1 1
0 1 2 1
0 1 3 1
0 1 4 1
0 1 5 1
0 1 6 1
0 1 7 1
0 1 8 1
0 1 9 1
0 1 10 00POOLE010MK3STSP00 1
0 1 11 00POOLE010MK3STSP00 1
0 1 12 00POOLE010MK3STSP00 1
0 1 13 00POOLE010MK3STSP00 1
0 1 14 00POOLE010MK3STSP00 1
0 1 15 00POOLE010MK3STSP00 1
0 1 16 00POOLE0100MK3MCI00 1
0 1 17 00POOLE010MK3STSP00 1
0 1 18 00POOLE010MK3STSP00 1
0 1 19 00POOLE010MK3STSP00 1
0 1 20 00POOLE010MK3STSP00 1
0 1 21 1
0 1 22 1
0 1 23 1
0 1 24 1
0 1 25 1
0 1 26 1
0 1 27 1
0 1 28 1
0 1 29 1
0 1 30 1
0 1 31 1
0 1 32 1
0 1 33 1
0 1 34 1
0 1 35 1
0 1 36 1
0 1 37 1
0 1 38 1
0 1 39 1
0 1 40 1
0 1 41 1
0 1 42 1
0 1 43 1
0 1 44 1
0 1 45 1
0 1 46 1
0 1 47 1
0 1 48 1
0 1 49 1
0 1 50 1
0 1 51 1
0 1 52 1
0 1 53 1
0 1 54 1
0 1 55 1
0 1 56 1
0 1 57 1
0 1 58 1
0 1 59 1
0 1 60 1
0 1 61 1
0 1 62 1
0 1 63 1
0 1 64 2
0 1 65 2
0 1 66 2
0 1 67 2
0 1 68 2
0 1 69 2
0 1 70 00POOLE010MK3STSP00 2
0 1 71 00POOLE010MK3STSP00 2
0 1 72 00POOLE010MK3STSP00 2
0 1 73 00POOLE010MK3STSP00 2
0 1 74 00POOLE010MK3STSP00 2
0 1 75 00POOLE010MK3STSP00 2
0 1 76 00POOLE010MK3STSP00 2
0 1 77 00POOLE010MK3STSP00 2
0 1 78 00POOLE010MK3STSP00 2
0 1 79 00POOLE010MK3STSP00 2
0 1 80 00POOLE0100MK3MCI00 2
0 1 81 2
0 1 82 2
0 1 83 2
0 1 84 2
0 1 85 2
0 1 86 2
0 1 87 2
0 1 88 2
0 1 89 2
0 1 90 2
0 1 91 2
0 1 92 2
0 1 93 2
0 1 94 2
0 1 95 2
0 1 96 2
0 1 97 2
0 1 98 2
0 1 99 2
0 1 100 2
0 1 101 2
0 1 102 2
0 1 103 2
0 1 104 2
0 1 105 2
0 1 106 2
0 1 107 2
0 1 108 2
0 1 109 2
0 1 110 2
0 1 111 2
0 1 112 2
0 1 113 2
0 1 114 2
0 1 115 2
0 1 116 2
0 1 117 2
0 1 118 2
0 1 119 2
0 1 120 2
0 1 121 2
0 1 122 2
0 1 123 2
0 1 124 2
0 1 125 2
0 1 126 2
0 1 127 2
0 1 128 3
0 1 129 3
0 1 130 3
0 1 131 3
0 1 132 3
0 1 133 3
0 1 134 3
0 1 135 3
0 1 136 3
0 1 137 3
0 1 138 3
0 1 139 3
0 1 140 3
0 1 141 3
0 1 142 3
0 1 143 3
0 1 144 3
0 1 145 3
0 1 146 3
0 1 147 3
0 1 148 3
0 1 149 3
0 1 150 3
0 1 151 3
0 1 152 3
0 1 153 3
0 1 154 3
0 1 155 3
0 1 156 3
0 1 157 3
0 1 158 3
0 1 159 3
0 1 160 3
0 1 161 3
0 1 162 3
0 1 163 3
0 1 164 3
0 1 165 3
0 1 166 3
0 1 167 3
0 1 168 3
0 1 169 3
0 1 170 3
0 1 171 3
0 1 172 3
0 1 173 3
0 1 173 3
0 1 174 3
0 1 175 3
0 1 176 3
0 1 177 3
0 1 178 3
0 1 179 3
0 1 180 3
0 1 181 3
0 1 182 3
0 1 183 3
0 1 184 3
186 rows selected.
|
|
|
|
|
|
| Re: Need help with Queries [message #554960 is a reply to message #554957] |
Sun, 20 May 2012 02:36 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
For your case 4, where you said, "00POOLE0100MK3STSP00", it looks like you have an extra 0 and it should have been "00POOLE010MK3STSP00", based on your desired results. You can use the queries from your case 2 and case 3 as sub-queries and use UNION ALL to combine the result sets. You can then select the columns from that, applying your filter conditions, and ordering by column position, as shown below.
SCOTT@orcl_11gR2> SELECT cpstpn, concno, enum, synthetic
2 FROM (SELECT cpstpn, concno, enum, synthetic,
3 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct
4 FROM en1
5 WHERE cpstpn = 0
6 AND concno = 1
7 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
8 UNION ALL
9 SELECT cpstpn, concno, enum, synthetic,
10 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct
11 FROM en1
12 WHERE cpstpn = 0
13 AND concno = 1
14 AND lctype = 'MK2ORD')
15 WHERE synthetic IN
16 ('00POOLE010MK3STSP00',
17 '00POOLE0100MK3MCI00',
18 '00POOLE0100MK2ORD00')
19 AND lct IN (1, 2, 3)
20 ORDER BY 3
21 /
CPSTPN CONCNO ENUM SYNTHETIC
---------- ---------- ---------- --------------------------
0 1 10 00POOLE010MK3STSP00
0 1 11 00POOLE010MK3STSP00
0 1 12 00POOLE010MK3STSP00
0 1 13 00POOLE010MK3STSP00
0 1 14 00POOLE010MK3STSP00
0 1 15 00POOLE010MK3STSP00
0 1 16 00POOLE0100MK3MCI00
0 1 17 00POOLE010MK3STSP00
0 1 18 00POOLE010MK3STSP00
0 1 19 00POOLE010MK3STSP00
0 1 20 00POOLE010MK3STSP00
0 1 70 00POOLE010MK3STSP00
0 1 71 00POOLE010MK3STSP00
0 1 72 00POOLE010MK3STSP00
0 1 73 00POOLE010MK3STSP00
0 1 74 00POOLE010MK3STSP00
0 1 75 00POOLE010MK3STSP00
0 1 76 00POOLE010MK3STSP00
0 1 77 00POOLE010MK3STSP00
0 1 78 00POOLE010MK3STSP00
0 1 79 00POOLE010MK3STSP00
0 1 80 00POOLE0100MK3MCI00
0 1 200 00POOLE0100MK2ORD00
0 1 201 00POOLE0100MK2ORD00
0 1 202 00POOLE0100MK2ORD00
0 1 203 00POOLE0100MK2ORD00
0 1 204 00POOLE0100MK2ORD00
0 1 205 00POOLE0100MK2ORD00
0 1 206 00POOLE0100MK2ORD00
0 1 207 00POOLE0100MK2ORD00
0 1 208 00POOLE0100MK2ORD00
0 1 209 00POOLE0100MK2ORD00
0 1 210 00POOLE0100MK2ORD00
0 1 211 00POOLE0100MK2ORD00
0 1 212 00POOLE0100MK2ORD00
0 1 213 00POOLE0100MK2ORD00
0 1 214 00POOLE0100MK2ORD00
0 1 215 00POOLE0100MK2ORD00
0 1 216 00POOLE0100MK2ORD00
0 1 217 00POOLE0100MK2ORD00
0 1 218 00POOLE0100MK2ORD00
0 1 219 00POOLE0100MK2ORD00
0 1 220 00POOLE0100MK2ORD00
0 1 221 00POOLE0100MK2ORD00
0 1 222 00POOLE0100MK2ORD00
0 1 223 00POOLE0100MK2ORD00
0 1 224 00POOLE0100MK2ORD00
0 1 225 00POOLE0100MK2ORD00
0 1 226 00POOLE0100MK2ORD00
0 1 227 00POOLE0100MK2ORD00
0 1 228 00POOLE0100MK2ORD00
0 1 229 00POOLE0100MK2ORD00
0 1 230 00POOLE0100MK2ORD00
0 1 231 00POOLE0100MK2ORD00
0 1 232 00POOLE0100MK2ORD00
0 1 233 00POOLE0100MK2ORD00
0 1 234 00POOLE0100MK2ORD00
0 1 235 00POOLE0100MK2ORD00
0 1 236 00POOLE0100MK2ORD00
0 1 237 00POOLE0100MK2ORD00
0 1 238 00POOLE0100MK2ORD00
0 1 239 00POOLE0100MK2ORD00
0 1 240 00POOLE0100MK2ORD00
63 rows selected.
|
|
|
|
|
|
|
|
|
|
| Re: Need help with Queries [message #554967 is a reply to message #554966] |
Sun, 20 May 2012 03:40 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I think I see what you mean by round robin now. It looks like you are rotating through the lct values. I have added a row_number partitioned by lct for that, so that you can order by the combination of that and lct. I have eliminated the filter condition for the lct values, so it is more generic. I don't currently have any ideas to simplify the query or do it without a sub-query. Perhaps someone else will have an idea. Or perhaps this is as good as it gets.
SCOTT@orcl_11gR2> SELECT cpstpn, concno, enum, synthetic, test_case,
2 ROW_NUMBER () OVER (PARTITION BY lct ORDER BY enum) rn, lct
3 FROM (SELECT cpstpn, concno, enum, synthetic,
4 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
5 2 AS test_case
6 FROM en1
7 WHERE cpstpn = 0
8 AND concno = 1
9 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
10 UNION ALL
11 SELECT cpstpn, concno, enum, synthetic,
12 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
13 3 AS test_case
14 FROM en1
15 WHERE cpstpn = 0
16 AND concno = 1
17 AND lctype = 'MK2ORD')
18 WHERE synthetic IN
19 ('00POOLE010MK3STSP00',
20 '00POOLE0100MK3MCI00',
21 '00POOLE0100MK2ORD00')
22 ORDER BY test_case, rn, lct
23 /
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 10 00POOLE010MK3STSP00 2 1 1
0 1 70 00POOLE010MK3STSP00 2 1 2
0 1 11 00POOLE010MK3STSP00 2 2 1
0 1 71 00POOLE010MK3STSP00 2 2 2
0 1 12 00POOLE010MK3STSP00 2 3 1
0 1 72 00POOLE010MK3STSP00 2 3 2
0 1 13 00POOLE010MK3STSP00 2 4 1
0 1 73 00POOLE010MK3STSP00 2 4 2
0 1 14 00POOLE010MK3STSP00 2 5 1
0 1 74 00POOLE010MK3STSP00 2 5 2
0 1 15 00POOLE010MK3STSP00 2 6 1
0 1 75 00POOLE010MK3STSP00 2 6 2
0 1 16 00POOLE0100MK3MCI00 2 7 1
0 1 76 00POOLE010MK3STSP00 2 7 2
0 1 17 00POOLE010MK3STSP00 2 8 1
0 1 77 00POOLE010MK3STSP00 2 8 2
0 1 18 00POOLE010MK3STSP00 2 9 1
0 1 78 00POOLE010MK3STSP00 2 9 2
0 1 19 00POOLE010MK3STSP00 2 10 1
0 1 79 00POOLE010MK3STSP00 2 10 2
0 1 20 00POOLE010MK3STSP00 2 11 1
0 1 80 00POOLE0100MK3MCI00 2 11 2
0 1 200 00POOLE0100MK2ORD00 3 12 1
0 1 201 00POOLE0100MK2ORD00 3 13 1
0 1 202 00POOLE0100MK2ORD00 3 14 1
0 1 203 00POOLE0100MK2ORD00 3 15 1
0 1 204 00POOLE0100MK2ORD00 3 16 1
0 1 205 00POOLE0100MK2ORD00 3 17 1
0 1 206 00POOLE0100MK2ORD00 3 18 1
0 1 207 00POOLE0100MK2ORD00 3 19 1
0 1 208 00POOLE0100MK2ORD00 3 20 1
0 1 209 00POOLE0100MK2ORD00 3 21 1
0 1 210 00POOLE0100MK2ORD00 3 22 1
0 1 211 00POOLE0100MK2ORD00 3 23 1
0 1 212 00POOLE0100MK2ORD00 3 24 1
0 1 213 00POOLE0100MK2ORD00 3 25 1
0 1 214 00POOLE0100MK2ORD00 3 26 1
0 1 215 00POOLE0100MK2ORD00 3 27 1
0 1 216 00POOLE0100MK2ORD00 3 28 1
0 1 217 00POOLE0100MK2ORD00 3 29 1
0 1 218 00POOLE0100MK2ORD00 3 30 1
0 1 219 00POOLE0100MK2ORD00 3 31 1
0 1 220 00POOLE0100MK2ORD00 3 32 1
0 1 221 00POOLE0100MK2ORD00 3 33 1
0 1 222 00POOLE0100MK2ORD00 3 34 1
0 1 223 00POOLE0100MK2ORD00 3 35 1
0 1 224 00POOLE0100MK2ORD00 3 36 1
0 1 225 00POOLE0100MK2ORD00 3 37 1
0 1 226 00POOLE0100MK2ORD00 3 38 1
0 1 227 00POOLE0100MK2ORD00 3 39 1
0 1 228 00POOLE0100MK2ORD00 3 40 1
0 1 229 00POOLE0100MK2ORD00 3 41 1
0 1 230 00POOLE0100MK2ORD00 3 42 1
0 1 231 00POOLE0100MK2ORD00 3 43 1
0 1 232 00POOLE0100MK2ORD00 3 44 1
0 1 233 00POOLE0100MK2ORD00 3 45 1
0 1 234 00POOLE0100MK2ORD00 3 46 1
0 1 235 00POOLE0100MK2ORD00 3 47 1
0 1 236 00POOLE0100MK2ORD00 3 48 1
0 1 237 00POOLE0100MK2ORD00 3 49 1
0 1 238 00POOLE0100MK2ORD00 3 50 1
0 1 239 00POOLE0100MK2ORD00 3 51 1
0 1 240 00POOLE0100MK2ORD00 3 52 1
63 rows selected.
|
|
|
|
|
|
|
|
| Re: Need help with Queries [message #554982 is a reply to message #554973] |
Sun, 20 May 2012 12:31 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I used Tom Kyte's stragg function in the second query below:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
SCOTT@orcl_11gR2> SELECT cpstpn, concno,
2 COUNT (concno) count_concno
3 FROM en1
4 WHERE cpstpn = 0
5 AND (synthetic LIKE '00POOLE010MK3STSP%' OR
6 synthetic LIKE '00POOLE0100MK3MCI%' OR
7 synthetic LIKE '00POOLE0100MK2ORD%')
8 GROUP BY cpstpn, concno
9 ORDER BY count_concno
10 /
CPSTPN CONCNO COUNT_CONCNO
---------- ---------- ------------
0 1 63
0 2 64
0 0 92
3 rows selected.
SCOTT@orcl_11gR2> COLUMN concnos FORMAT A10
SCOTT@orcl_11gR2> SELECT cpstpn, concnos, count_concnos
2 FROM (SELECT cpstpn, concnos, count_concnos
3 FROM (SELECT cpstpn,
4 stragg (concno) OVER (ORDER BY count_concno) concnos,
5 SUM (count_concno) OVER (ORDER BY count_concno) count_concnos
6 FROM (SELECT cpstpn, concno,
7 COUNT (concno) count_concno
8 FROM en1
9 WHERE cpstpn = 0
10 AND (synthetic LIKE '00POOLE010MK3STSP%' OR
11 synthetic LIKE '00POOLE0100MK3MCI%' OR
12 synthetic LIKE '00POOLE0100MK2ORD%')
13 GROUP BY cpstpn, concno))
14 WHERE count_concnos >= 100
15 ORDER BY count_concnos)
16 WHERE ROWNUM = 1
17 /
CPSTPN CONCNOS COUNT_CONCNOS
---------- ---------- -------------
0 1,2 127
1 row selected.
|
|
|
|
| Re: Need help with Queries [message #554984 is a reply to message #554973] |
Sun, 20 May 2012 12:42 |
|
supiash
Messages: 70
Registered: May 2012
Location: bangalore
|
Member |
|
|
Hi,
Once found the CPSTPN and CONCNO's i was trying to arrive at the result of Test Case 4 but am not finding the output expected that is the Rows are not rotating on LCT's
Actual Output:
SQL> SELECT cpstpn, concno, enum, synthetic, test_case,
2 ROW_NUMBER () OVER (PARTITION BY lct ORDER BY enum) rn, lct
3 FROM (SELECT cpstpn, concno, enum, synthetic,
4 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
5 2 AS test_case
6 FROM en1
7 WHERE cpstpn = 0
8 AND concno = 1
9 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
10 UNION ALL
11 SELECT cpstpn, concno, enum, synthetic,
12 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
13 3 AS test_case
14 FROM en1
15 WHERE cpstpn = 0
16 AND concno = 2
17 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI'))
18 WHERE synthetic IN
19 ('00POOLE010MK3STSP00',
20 '00POOLE0100MK3MCI00')
21 ORDER BY test_case, rn, lct
22 /
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 70 00POOLE010MK3STSP00 2 1 2
0 1 71 00POOLE010MK3STSP00 2 2 2
0 1 72 00POOLE010MK3STSP00 2 3 2
0 1 73 00POOLE010MK3STSP00 2 4 2
0 1 74 00POOLE010MK3STSP00 2 5 2
0 1 75 00POOLE010MK3STSP00 2 6 2
0 1 76 00POOLE010MK3STSP00 2 7 2
0 1 77 00POOLE010MK3STSP00 2 8 2
0 1 78 00POOLE010MK3STSP00 2 9 2
0 1 79 00POOLE010MK3STSP00 2 10 2
0 1 80 00POOLE0100MK3MCI00 2 11 2
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 10 00POOLE010MK3STSP00 2 12 1
0 1 11 00POOLE010MK3STSP00 2 13 1
0 1 12 00POOLE010MK3STSP00 2 15 1
0 1 13 00POOLE010MK3STSP00 2 17 1
0 1 14 00POOLE010MK3STSP00 2 19 1
0 1 15 00POOLE010MK3STSP00 2 22 1
0 1 16 00POOLE0100MK3MCI00 2 23 1
0 1 17 00POOLE010MK3STSP00 2 25 1
0 1 18 00POOLE010MK3STSP00 2 27 1
0 1 19 00POOLE010MK3STSP00 2 30 1
0 1 20 00POOLE010MK3STSP00 2 32 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 0 00POOLE0100MK3MCI00 3 1 1
0 2 1 00POOLE010MK3STSP00 3 2 1
0 2 2 00POOLE010MK3STSP00 3 3 1
0 2 3 00POOLE010MK3STSP00 3 4 1
0 2 4 00POOLE010MK3STSP00 3 5 1
0 2 5 00POOLE010MK3STSP00 3 6 1
0 2 6 00POOLE010MK3STSP00 3 7 1
0 2 7 00POOLE010MK3STSP00 3 8 1
0 2 8 00POOLE010MK3STSP00 3 9 1
0 2 9 00POOLE010MK3STSP00 3 10 1
0 2 10 00POOLE010MK3STSP00 3 11 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 11 00POOLE010MK3STSP00 3 14 1
0 2 12 00POOLE010MK3STSP00 3 16 1
0 2 13 00POOLE010MK3STSP00 3 18 1
0 2 14 00POOLE010MK3STSP00 3 20 1
0 2 15 00POOLE010MK3STSP00 3 21 1
0 2 16 00POOLE0100MK3MCI00 3 24 1
0 2 17 00POOLE010MK3STSP00 3 26 1
0 2 18 00POOLE010MK3STSP00 3 28 1
0 2 19 00POOLE010MK3STSP00 3 29 1
0 2 20 00POOLE010MK3STSP00 3 31 1
0 2 21 00POOLE010MK3STSP00 3 33 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 22 00POOLE010MK3STSP00 3 34 1
0 2 23 00POOLE010MK3STSP00 3 35 1
0 2 24 00POOLE010MK3STSP00 3 36 1
0 2 25 00POOLE010MK3STSP00 3 37 1
0 2 26 00POOLE010MK3STSP00 3 38 1
0 2 27 00POOLE010MK3STSP00 3 39 1
0 2 28 00POOLE010MK3STSP00 3 40 1
0 2 29 00POOLE010MK3STSP00 3 41 1
0 2 30 00POOLE010MK3STSP00 3 42 1
0 2 31 00POOLE010MK3STSP00 3 43 1
0 2 32 00POOLE0100MK3MCI00 3 44 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 33 00POOLE010MK3STSP00 3 45 1
0 2 34 00POOLE010MK3STSP00 3 46 1
0 2 35 00POOLE010MK3STSP00 3 47 1
0 2 36 00POOLE010MK3STSP00 3 48 1
0 2 37 00POOLE010MK3STSP00 3 49 1
0 2 38 00POOLE010MK3STSP00 3 50 1
0 2 39 00POOLE010MK3STSP00 3 51 1
0 2 40 00POOLE010MK3STSP00 3 52 1
0 2 41 00POOLE010MK3STSP00 3 53 1
0 2 42 00POOLE010MK3STSP00 3 54 1
0 2 43 00POOLE010MK3STSP00 3 55 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 44 00POOLE010MK3STSP00 3 56 1
0 2 45 00POOLE010MK3STSP00 3 57 1
0 2 46 00POOLE010MK3STSP00 3 58 1
0 2 47 00POOLE010MK3STSP00 3 59 1
0 2 48 00POOLE0100MK3MCI00 3 60 1
0 2 49 00POOLE010MK3STSP00 3 61 1
0 2 50 00POOLE010MK3STSP00 3 62 1
0 2 51 00POOLE010MK3STSP00 3 63 1
0 2 52 00POOLE010MK3STSP00 3 64 1
0 2 53 00POOLE010MK3STSP00 3 65 1
0 2 54 00POOLE010MK3STSP00 3 66 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 55 00POOLE010MK3STSP00 3 67 1
0 2 56 00POOLE010MK3STSP00 3 68 1
0 2 57 00POOLE010MK3STSP00 3 69 1
0 2 58 00POOLE010MK3STSP00 3 70 1
0 2 59 00POOLE010MK3STSP00 3 71 1
0 2 60 00POOLE010MK3STSP00 3 72 1
0 2 61 00POOLE010MK3STSP00 3 73 1
0 2 62 00POOLE010MK3STSP00 3 74 1
0 2 63 00POOLE010MK3STSP00 3 75 1
86 rows selected.
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 10 00POOLE010MK3STSP00 2 1 1
0 1 70 00POOLE010MK3STSP00 2 1 2
0 1 11 00POOLE010MK3STSP00 2 2 1
0 1 71 00POOLE010MK3STSP00 2 2 2
0 1 12 00POOLE010MK3STSP00 2 3 1
0 1 72 00POOLE010MK3STSP00 2 3 2
0 1 13 00POOLE010MK3STSP00 2 4 1
0 1 73 00POOLE010MK3STSP00 2 4 2
0 1 14 00POOLE010MK3STSP00 2 5 1
0 1 74 00POOLE010MK3STSP00 2 5 2
0 1 15 00POOLE010MK3STSP00 2 6 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 75 00POOLE010MK3STSP00 2 6 2
0 1 16 00POOLE0100MK3MCI00 2 7 1
0 1 76 00POOLE010MK3STSP00 2 7 2
0 1 17 00POOLE010MK3STSP00 2 8 1
0 1 77 00POOLE010MK3STSP00 2 8 2
0 1 18 00POOLE010MK3STSP00 2 9 1
0 1 78 00POOLE010MK3STSP00 2 9 2
0 1 19 00POOLE010MK3STSP00 2 10 1
0 1 79 00POOLE010MK3STSP00 2 10 2
0 1 20 00POOLE010MK3STSP00 2 11 1
0 1 80 00POOLE0100MK3MCI00 2 11 2
0 2 0 00POOLE0100MK3MCI00 3 1 1
0 2 1 00POOLE010MK3STSP00 3 2 1
0 2 2 00POOLE010MK3STSP00 3 3 1
0 2 3 00POOLE010MK3STSP00 3 4 1
0 2 4 00POOLE010MK3STSP00 3 5 1
0 2 5 00POOLE010MK3STSP00 3 6 1
0 2 6 00POOLE010MK3STSP00 3 7 1
0 2 7 00POOLE010MK3STSP00 3 8 1
0 2 8 00POOLE010MK3STSP00 3 9 1
0 2 9 00POOLE010MK3STSP00 3 10 1
0 2 10 00POOLE010MK3STSP00 3 11 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 11 00POOLE010MK3STSP00 3 14 1
0 2 12 00POOLE010MK3STSP00 3 16 1
0 2 13 00POOLE010MK3STSP00 3 18 1
0 2 14 00POOLE010MK3STSP00 3 20 1
0 2 15 00POOLE010MK3STSP00 3 21 1
0 2 16 00POOLE0100MK3MCI00 3 24 1
0 2 17 00POOLE010MK3STSP00 3 26 1
0 2 18 00POOLE010MK3STSP00 3 28 1
0 2 19 00POOLE010MK3STSP00 3 29 1
0 2 20 00POOLE010MK3STSP00 3 31 1
0 2 21 00POOLE010MK3STSP00 3 33 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 22 00POOLE010MK3STSP00 3 34 1
0 2 23 00POOLE010MK3STSP00 3 35 1
0 2 24 00POOLE010MK3STSP00 3 36 1
0 2 25 00POOLE010MK3STSP00 3 37 1
0 2 26 00POOLE010MK3STSP00 3 38 1
0 2 27 00POOLE010MK3STSP00 3 39 1
0 2 28 00POOLE010MK3STSP00 3 40 1
0 2 29 00POOLE010MK3STSP00 3 41 1
0 2 30 00POOLE010MK3STSP00 3 42 1
0 2 31 00POOLE010MK3STSP00 3 43 1
0 2 32 00POOLE0100MK3MCI00 3 44 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 33 00POOLE010MK3STSP00 3 45 1
0 2 34 00POOLE010MK3STSP00 3 46 1
0 2 35 00POOLE010MK3STSP00 3 47 1
0 2 36 00POOLE010MK3STSP00 3 48 1
0 2 37 00POOLE010MK3STSP00 3 49 1
0 2 38 00POOLE010MK3STSP00 3 50 1
0 2 39 00POOLE010MK3STSP00 3 51 1
0 2 40 00POOLE010MK3STSP00 3 52 1
0 2 41 00POOLE010MK3STSP00 3 53 1
0 2 42 00POOLE010MK3STSP00 3 54 1
0 2 43 00POOLE010MK3STSP00 3 55 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 44 00POOLE010MK3STSP00 3 56 1
0 2 45 00POOLE010MK3STSP00 3 57 1
0 2 46 00POOLE010MK3STSP00 3 58 1
0 2 47 00POOLE010MK3STSP00 3 59 1
0 2 48 00POOLE0100MK3MCI00 3 60 1
0 2 49 00POOLE010MK3STSP00 3 61 1
0 2 50 00POOLE010MK3STSP00 3 62 1
0 2 51 00POOLE010MK3STSP00 3 63 1
0 2 52 00POOLE010MK3STSP00 3 64 1
0 2 53 00POOLE010MK3STSP00 3 65 1
0 2 54 00POOLE010MK3STSP00 3 66 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 55 00POOLE010MK3STSP00 3 67 1
0 2 56 00POOLE010MK3STSP00 3 68 1
0 2 57 00POOLE010MK3STSP00 3 69 1
0 2 58 00POOLE010MK3STSP00 3 70 1
0 2 59 00POOLE010MK3STSP00 3 71 1
0 2 60 00POOLE010MK3STSP00 3 72 1
0 2 61 00POOLE010MK3STSP00 3 73 1
0 2 62 00POOLE010MK3STSP00 3 74 1
0 2 63 00POOLE010MK3STSP00 3 75 1
Could you please let me know if am doing something wrong?
|
|
|
|
| Re: Need help with Queries [message #554986 is a reply to message #554984] |
Sun, 20 May 2012 13:20 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In the following, I changed "PARTITION BY lct" to "PARTITION BY lct, test_case".
SCOTT@orcl_11gR2> SELECT cpstpn, concno, enum, synthetic, test_case,
2 ROW_NUMBER () OVER (PARTITION BY lct, test_case ORDER BY enum) rn, lct
3 FROM (SELECT cpstpn, concno, enum, synthetic,
4 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
5 2 AS test_case
6 FROM en1
7 WHERE cpstpn = 0
8 AND concno = 1
9 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
10 UNION ALL
11 SELECT cpstpn, concno, enum, synthetic,
12 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
13 3 AS test_case
14 FROM en1
15 WHERE cpstpn = 0
16 AND concno = 2
17 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI'))
18 WHERE synthetic IN
19 ('00POOLE010MK3STSP00',
20 '00POOLE0100MK3MCI00')
21 ORDER BY test_case, rn, lct
22 /
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 10 00POOLE010MK3STSP00 2 1 1
0 1 70 00POOLE010MK3STSP00 2 1 2
0 1 11 00POOLE010MK3STSP00 2 2 1
0 1 71 00POOLE010MK3STSP00 2 2 2
0 1 12 00POOLE010MK3STSP00 2 3 1
0 1 72 00POOLE010MK3STSP00 2 3 2
0 1 13 00POOLE010MK3STSP00 2 4 1
0 1 73 00POOLE010MK3STSP00 2 4 2
0 1 14 00POOLE010MK3STSP00 2 5 1
0 1 74 00POOLE010MK3STSP00 2 5 2
0 1 15 00POOLE010MK3STSP00 2 6 1
0 1 75 00POOLE010MK3STSP00 2 6 2
0 1 16 00POOLE0100MK3MCI00 2 7 1
0 1 76 00POOLE010MK3STSP00 2 7 2
0 1 17 00POOLE010MK3STSP00 2 8 1
0 1 77 00POOLE010MK3STSP00 2 8 2
0 1 18 00POOLE010MK3STSP00 2 9 1
0 1 78 00POOLE010MK3STSP00 2 9 2
0 1 19 00POOLE010MK3STSP00 2 10 1
0 1 79 00POOLE010MK3STSP00 2 10 2
0 1 20 00POOLE010MK3STSP00 2 11 1
0 1 80 00POOLE0100MK3MCI00 2 11 2
0 2 0 00POOLE0100MK3MCI00 3 1 1
0 2 1 00POOLE010MK3STSP00 3 2 1
0 2 2 00POOLE010MK3STSP00 3 3 1
0 2 3 00POOLE010MK3STSP00 3 4 1
0 2 4 00POOLE010MK3STSP00 3 5 1
0 2 5 00POOLE010MK3STSP00 3 6 1
0 2 6 00POOLE010MK3STSP00 3 7 1
0 2 7 00POOLE010MK3STSP00 3 8 1
0 2 8 00POOLE010MK3STSP00 3 9 1
0 2 9 00POOLE010MK3STSP00 3 10 1
0 2 10 00POOLE010MK3STSP00 3 11 1
0 2 11 00POOLE010MK3STSP00 3 12 1
0 2 12 00POOLE010MK3STSP00 3 13 1
0 2 13 00POOLE010MK3STSP00 3 14 1
0 2 14 00POOLE010MK3STSP00 3 15 1
0 2 15 00POOLE010MK3STSP00 3 16 1
0 2 16 00POOLE0100MK3MCI00 3 17 1
0 2 17 00POOLE010MK3STSP00 3 18 1
0 2 18 00POOLE010MK3STSP00 3 19 1
0 2 19 00POOLE010MK3STSP00 3 20 1
0 2 20 00POOLE010MK3STSP00 3 21 1
0 2 21 00POOLE010MK3STSP00 3 22 1
0 2 22 00POOLE010MK3STSP00 3 23 1
0 2 23 00POOLE010MK3STSP00 3 24 1
0 2 24 00POOLE010MK3STSP00 3 25 1
0 2 25 00POOLE010MK3STSP00 3 26 1
0 2 26 00POOLE010MK3STSP00 3 27 1
0 2 27 00POOLE010MK3STSP00 3 28 1
0 2 28 00POOLE010MK3STSP00 3 29 1
0 2 29 00POOLE010MK3STSP00 3 30 1
0 2 30 00POOLE010MK3STSP00 3 31 1
0 2 31 00POOLE010MK3STSP00 3 32 1
0 2 32 00POOLE0100MK3MCI00 3 33 1
0 2 33 00POOLE010MK3STSP00 3 34 1
0 2 34 00POOLE010MK3STSP00 3 35 1
0 2 35 00POOLE010MK3STSP00 3 36 1
0 2 36 00POOLE010MK3STSP00 3 37 1
0 2 37 00POOLE010MK3STSP00 3 38 1
0 2 38 00POOLE010MK3STSP00 3 39 1
0 2 39 00POOLE010MK3STSP00 3 40 1
0 2 40 00POOLE010MK3STSP00 3 41 1
0 2 41 00POOLE010MK3STSP00 3 42 1
0 2 42 00POOLE010MK3STSP00 3 43 1
0 2 43 00POOLE010MK3STSP00 3 44 1
0 2 44 00POOLE010MK3STSP00 3 45 1
0 2 45 00POOLE010MK3STSP00 3 46 1
0 2 46 00POOLE010MK3STSP00 3 47 1
0 2 47 00POOLE010MK3STSP00 3 48 1
0 2 48 00POOLE0100MK3MCI00 3 49 1
0 2 49 00POOLE010MK3STSP00 3 50 1
0 2 50 00POOLE010MK3STSP00 3 51 1
0 2 51 00POOLE010MK3STSP00 3 52 1
0 2 52 00POOLE010MK3STSP00 3 53 1
0 2 53 00POOLE010MK3STSP00 3 54 1
0 2 54 00POOLE010MK3STSP00 3 55 1
0 2 55 00POOLE010MK3STSP00 3 56 1
0 2 56 00POOLE010MK3STSP00 3 57 1
0 2 57 00POOLE010MK3STSP00 3 58 1
0 2 58 00POOLE010MK3STSP00 3 59 1
0 2 59 00POOLE010MK3STSP00 3 60 1
0 2 60 00POOLE010MK3STSP00 3 61 1
0 2 61 00POOLE010MK3STSP00 3 62 1
0 2 62 00POOLE010MK3STSP00 3 63 1
0 2 63 00POOLE010MK3STSP00 3 64 1
86 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need help with Queries [message #554992 is a reply to message #554991] |
Sun, 20 May 2012 14:29 |
|
supiash
Messages: 70
Registered: May 2012
Location: bangalore
|
Member |
|
|
OK Let me Explain the Two Scenarios
Problem1 1:Allocate 24 ENUM's
Solution:
Step1:
SCOTT@orcl_11gR2> SELECT cpstpn,
2 concno,
3 COUNT (concno)
4 FROM en1
5 WHERE synthetic LIKE '00POOLE010MK3STSP%'
6 OR synthetic LIKE '00POOLE0100MK3MCI%'
7 OR synthetic LIKE '00POOLE0100MK2ORD%'
8 GROUP BY cpstpn, concno
9 HAVING COUNT (concno) >= 24
10 ORDER BY COUNT (concno) ASC, concno ASC
11 /
CPSTPN CONCNO COUNT(CONCNO)
---------- ---------- -------------
0 1 63
1 1 64
0 2 64
0 0 92
4 rows selected.
CHOOSE CPSTPN=0 AND CONCNO=1
STEP2:
SCOTT@orcl_11gR2> SELECT cpstpn, concno, enum, synthetic, test_case,
2 ROW_NUMBER () OVER (PARTITION BY lct ORDER BY enum) rn, lct
3 FROM (SELECT cpstpn, concno, enum, synthetic,
4 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
5 2 AS test_case
6 FROM en1
7 WHERE cpstpn = 0
8 AND concno = 1
9 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
10 UNION ALL
11 SELECT cpstpn, concno, enum, synthetic,
12 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
13 3 AS test_case
14 FROM en1
15 WHERE cpstpn = 0
16 AND concno = 1
17 AND lctype = 'MK2ORD')
18 WHERE synthetic IN
19 ('00POOLE010MK3STSP00',
20 '00POOLE0100MK3MCI00',
21 '00POOLE0100MK2ORD00')
22 ORDER BY test_case, rn, lct
23 /
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 10 00POOLE010MK3STSP00 2 1 1
0 1 70 00POOLE010MK3STSP00 2 1 2
0 1 11 00POOLE010MK3STSP00 2 2 1
0 1 71 00POOLE010MK3STSP00 2 2 2
0 1 12 00POOLE010MK3STSP00 2 3 1
0 1 72 00POOLE010MK3STSP00 2 3 2
0 1 13 00POOLE010MK3STSP00 2 4 1
0 1 73 00POOLE010MK3STSP00 2 4 2
0 1 14 00POOLE010MK3STSP00 2 5 1
0 1 74 00POOLE010MK3STSP00 2 5 2
0 1 15 00POOLE010MK3STSP00 2 6 1
0 1 75 00POOLE010MK3STSP00 2 6 2
0 1 16 00POOLE0100MK3MCI00 2 7 1
0 1 76 00POOLE010MK3STSP00 2 7 2
0 1 17 00POOLE010MK3STSP00 2 8 1
0 1 77 00POOLE010MK3STSP00 2 8 2
0 1 18 00POOLE010MK3STSP00 2 9 1
0 1 78 00POOLE010MK3STSP00 2 9 2
0 1 19 00POOLE010MK3STSP00 2 10 1
0 1 79 00POOLE010MK3STSP00 2 10 2
0 1 20 00POOLE010MK3STSP00 2 11 1
0 1 80 00POOLE0100MK3MCI00 2 11 2
0 1 200 00POOLE0100MK2ORD00 3 12 1
0 1 201 00POOLE0100MK2ORD00 3 13 1
0 1 202 00POOLE0100MK2ORD00 3 14 1
0 1 203 00POOLE0100MK2ORD00 3 15 1
0 1 204 00POOLE0100MK2ORD00 3 16 1
0 1 205 00POOLE0100MK2ORD00 3 17 1
0 1 206 00POOLE0100MK2ORD00 3 18 1
0 1 207 00POOLE0100MK2ORD00 3 19 1
0 1 208 00POOLE0100MK2ORD00 3 20 1
0 1 209 00POOLE0100MK2ORD00 3 21 1
0 1 210 00POOLE0100MK2ORD00 3 22 1
0 1 211 00POOLE0100MK2ORD00 3 23 1
0 1 212 00POOLE0100MK2ORD00 3 24 1
0 1 213 00POOLE0100MK2ORD00 3 25 1
0 1 214 00POOLE0100MK2ORD00 3 26 1
0 1 215 00POOLE0100MK2ORD00 3 27 1
0 1 216 00POOLE0100MK2ORD00 3 28 1
0 1 217 00POOLE0100MK2ORD00 3 29 1
0 1 218 00POOLE0100MK2ORD00 3 30 1
0 1 219 00POOLE0100MK2ORD00 3 31 1
0 1 220 00POOLE0100MK2ORD00 3 32 1
0 1 221 00POOLE0100MK2ORD00 3 33 1
0 1 222 00POOLE0100MK2ORD00 3 34 1
0 1 223 00POOLE0100MK2ORD00 3 35 1
0 1 224 00POOLE0100MK2ORD00 3 36 1
0 1 225 00POOLE0100MK2ORD00 3 37 1
0 1 226 00POOLE0100MK2ORD00 3 38 1
0 1 227 00POOLE0100MK2ORD00 3 39 1
0 1 228 00POOLE0100MK2ORD00 3 40 1
0 1 229 00POOLE0100MK2ORD00 3 41 1
0 1 230 00POOLE0100MK2ORD00 3 42 1
0 1 231 00POOLE0100MK2ORD00 3 43 1
0 1 232 00POOLE0100MK2ORD00 3 44 1
0 1 233 00POOLE0100MK2ORD00 3 45 1
0 1 234 00POOLE0100MK2ORD00 3 46 1
0 1 235 00POOLE0100MK2ORD00 3 47 1
0 1 236 00POOLE0100MK2ORD00 3 48 1
0 1 237 00POOLE0100MK2ORD00 3 49 1
0 1 238 00POOLE0100MK2ORD00 3 50 1
0 1 239 00POOLE0100MK2ORD00 3 51 1
0 1 240 00POOLE0100MK2ORD00 3 52 1
63 rows selected.
|
|
|
|
| Re: Need help with Queries [message #554993 is a reply to message #554992] |
Sun, 20 May 2012 14:35 |
|
supiash
Messages: 70
Registered: May 2012
Location: bangalore
|
Member |
|
|
Problem Statement2 : Allocate 100 ENUM's
Solution:
Step1:
SCOTT@orcl_11gR2> SELECT cpstpn,
2 concno,
3 COUNT (concno)
4 FROM en1
5 WHERE synthetic LIKE '00POOLE010MK3STSP%'
6 OR synthetic LIKE '00POOLE0100MK3MCI%'
7 OR synthetic LIKE '00POOLE0100MK2ORD%'
8 GROUP BY cpstpn, concno
9 HAVING COUNT (concno) >= 100
10 ORDER BY COUNT (concno) ASC, concno ASC
11 /
no rows selected
Sterp 2:
SCOTT@orcl_11gR2> COLUMN concnos FORMAT A10
SCOTT@orcl_11gR2> SELECT cpstpn, concnos, count_concnos
2 FROM (SELECT cpstpn, concnos, count_concnos
3 FROM (SELECT cpstpn,
4 stragg (concno) OVER (ORDER BY count_concno) concnos,
5 SUM (count_concno) OVER (ORDER BY count_concno) count_concnos
6 FROM (SELECT cpstpn, concno,
7 COUNT (concno) count_concno
8 FROM en1
9 WHERE cpstpn = 0
10 AND (synthetic LIKE '00POOLE010MK3STSP%' OR
11 synthetic LIKE '00POOLE0100MK3MCI%' OR
12 synthetic LIKE '00POOLE0100MK2ORD%')
13 GROUP BY cpstpn, concno))
14 WHERE count_concnos >= 100
15 ORDER BY count_concnos)
16 WHERE ROWNUM = 1
17 /
CPSTPN CONCNOS COUNT_CONCNOS
---------- ---------- -------------
0 1,2 127
1 row selected.
choose CPSTPN=0 and CONCNO=1
cpstpn=0 and CONCNO=2
Step3:
SQL> SELECT cpstpn, concno, enum, synthetic, test_case,
2 ROW_NUMBER () OVER (PARTITION BY test_case,lct ORDER BY enum) rn, lct
3 FROM (SELECT cpstpn, concno, enum, synthetic,
4 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
5 2 AS test_case
6 FROM en1
7 WHERE cpstpn = 0
8 AND concno = 1
9 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
10 UNION ALL
11 SELECT cpstpn, concno, enum, synthetic,
12 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
13 3 AS test_case
14 FROM en1
15 WHERE cpstpn = 0
16 AND concno = 2
17 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
18 UNION ALL
19 SELECT cpstpn, concno, enum, synthetic,
20 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
21 4 AS test_case
22 FROM en1
23 WHERE cpstpn = 0
24 AND concno = 1
25 AND lctype = 'MK2ORD'
26 UNION ALL
27 SELECT cpstpn, concno, enum, synthetic,
28 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
29 5 AS test_case
30 FROM en1
31 WHERE cpstpn = 0
32 AND concno = 2
33 AND lctype = 'MK2ORD')
34 WHERE synthetic IN
35 ('00POOLE010MK3STSP00',
36 '00POOLE0100MK3MCI00',
37 '00POOLE0100MK2ORD00')
38 ORDER BY test_case, rn, lct
39 /
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 10 00POOLE010MK3STSP00 2 1 1
0 1 70 00POOLE010MK3STSP00 2 1 2
0 1 11 00POOLE010MK3STSP00 2 2 1
0 1 71 00POOLE010MK3STSP00 2 2 2
0 1 12 00POOLE010MK3STSP00 2 3 1
0 1 72 00POOLE010MK3STSP00 2 3 2
0 1 13 00POOLE010MK3STSP00 2 4 1
0 1 73 00POOLE010MK3STSP00 2 4 2
0 1 14 00POOLE010MK3STSP00 2 5 1
0 1 74 00POOLE010MK3STSP00 2 5 2
0 1 15 00POOLE010MK3STSP00 2 6 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 75 00POOLE010MK3STSP00 2 6 2
0 1 16 00POOLE0100MK3MCI00 2 7 1
0 1 76 00POOLE010MK3STSP00 2 7 2
0 1 17 00POOLE010MK3STSP00 2 8 1
0 1 77 00POOLE010MK3STSP00 2 8 2
0 1 18 00POOLE010MK3STSP00 2 9 1
0 1 78 00POOLE010MK3STSP00 2 9 2
0 1 19 00POOLE010MK3STSP00 2 10 1
0 1 79 00POOLE010MK3STSP00 2 10 2
0 1 20 00POOLE010MK3STSP00 2 11 1
0 1 80 00POOLE0100MK3MCI00 2 11 2
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 0 00POOLE0100MK3MCI00 3 1 1
0 2 1 00POOLE010MK3STSP00 3 2 1
0 2 2 00POOLE010MK3STSP00 3 3 1
0 2 3 00POOLE010MK3STSP00 3 4 1
0 2 4 00POOLE010MK3STSP00 3 5 1
0 2 5 00POOLE010MK3STSP00 3 6 1
0 2 6 00POOLE010MK3STSP00 3 7 1
0 2 7 00POOLE010MK3STSP00 3 8 1
0 2 8 00POOLE010MK3STSP00 3 9 1
0 2 9 00POOLE010MK3STSP00 3 10 1
0 2 10 00POOLE010MK3STSP00 3 11 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 11 00POOLE010MK3STSP00 3 12 1
0 2 12 00POOLE010MK3STSP00 3 13 1
0 2 13 00POOLE010MK3STSP00 3 14 1
0 2 14 00POOLE010MK3STSP00 3 15 1
0 2 15 00POOLE010MK3STSP00 3 16 1
0 2 16 00POOLE0100MK3MCI00 3 17 1
0 2 17 00POOLE010MK3STSP00 3 18 1
0 2 18 00POOLE010MK3STSP00 3 19 1
0 2 19 00POOLE010MK3STSP00 3 20 1
0 2 20 00POOLE010MK3STSP00 3 21 1
0 2 21 00POOLE010MK3STSP00 3 22 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 22 00POOLE010MK3STSP00 3 23 1
0 2 23 00POOLE010MK3STSP00 3 24 1
0 2 24 00POOLE010MK3STSP00 3 25 1
0 2 25 00POOLE010MK3STSP00 3 26 1
0 2 26 00POOLE010MK3STSP00 3 27 1
0 2 27 00POOLE010MK3STSP00 3 28 1
0 2 28 00POOLE010MK3STSP00 3 29 1
0 2 29 00POOLE010MK3STSP00 3 30 1
0 2 30 00POOLE010MK3STSP00 3 31 1
0 2 31 00POOLE010MK3STSP00 3 32 1
0 2 32 00POOLE0100MK3MCI00 3 33 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 33 00POOLE010MK3STSP00 3 34 1
0 2 34 00POOLE010MK3STSP00 3 35 1
0 2 35 00POOLE010MK3STSP00 3 36 1
0 2 36 00POOLE010MK3STSP00 3 37 1
0 2 37 00POOLE010MK3STSP00 3 38 1
0 2 38 00POOLE010MK3STSP00 3 39 1
0 2 39 00POOLE010MK3STSP00 3 40 1
0 2 40 00POOLE010MK3STSP00 3 41 1
0 2 41 00POOLE010MK3STSP00 3 42 1
0 2 42 00POOLE010MK3STSP00 3 43 1
0 2 43 00POOLE010MK3STSP00 3 44 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 44 00POOLE010MK3STSP00 3 45 1
0 2 45 00POOLE010MK3STSP00 3 46 1
0 2 46 00POOLE010MK3STSP00 3 47 1
0 2 47 00POOLE010MK3STSP00 3 48 1
0 2 48 00POOLE0100MK3MCI00 3 49 1
0 2 49 00POOLE010MK3STSP00 3 50 1
0 2 50 00POOLE010MK3STSP00 3 51 1
0 2 51 00POOLE010MK3STSP00 3 52 1
0 2 52 00POOLE010MK3STSP00 3 53 1
0 2 53 00POOLE010MK3STSP00 3 54 1
0 2 54 00POOLE010MK3STSP00 3 55 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 2 55 00POOLE010MK3STSP00 3 56 1
0 2 56 00POOLE010MK3STSP00 3 57 1
0 2 57 00POOLE010MK3STSP00 3 58 1
0 2 58 00POOLE010MK3STSP00 3 59 1
0 2 59 00POOLE010MK3STSP00 3 60 1
0 2 60 00POOLE010MK3STSP00 3 61 1
0 2 61 00POOLE010MK3STSP00 3 62 1
0 2 62 00POOLE010MK3STSP00 3 63 1
0 2 63 00POOLE010MK3STSP00 3 64 1
0 1 200 00POOLE0100MK2ORD00 4 1 1
0 1 201 00POOLE0100MK2ORD00 4 2 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 202 00POOLE0100MK2ORD00 4 3 1
0 1 203 00POOLE0100MK2ORD00 4 4 1
0 1 204 00POOLE0100MK2ORD00 4 5 1
0 1 205 00POOLE0100MK2ORD00 4 6 1
0 1 206 00POOLE0100MK2ORD00 4 7 1
0 1 207 00POOLE0100MK2ORD00 4 8 1
0 1 208 00POOLE0100MK2ORD00 4 9 1
0 1 209 00POOLE0100MK2ORD00 4 10 1
0 1 210 00POOLE0100MK2ORD00 4 11 1
0 1 211 00POOLE0100MK2ORD00 4 12 1
0 1 212 00POOLE0100MK2ORD00 4 13 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 213 00POOLE0100MK2ORD00 4 14 1
0 1 214 00POOLE0100MK2ORD00 4 15 1
0 1 215 00POOLE0100MK2ORD00 4 16 1
0 1 216 00POOLE0100MK2ORD00 4 17 1
0 1 217 00POOLE0100MK2ORD00 4 18 1
0 1 218 00POOLE0100MK2ORD00 4 19 1
0 1 219 00POOLE0100MK2ORD00 4 20 1
0 1 220 00POOLE0100MK2ORD00 4 21 1
0 1 221 00POOLE0100MK2ORD00 4 22 1
0 1 222 00POOLE0100MK2ORD00 4 23 1
0 1 223 00POOLE0100MK2ORD00 4 24 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 224 00POOLE0100MK2ORD00 4 25 1
0 1 225 00POOLE0100MK2ORD00 4 26 1
0 1 226 00POOLE0100MK2ORD00 4 27 1
0 1 227 00POOLE0100MK2ORD00 4 28 1
0 1 228 00POOLE0100MK2ORD00 4 29 1
0 1 229 00POOLE0100MK2ORD00 4 30 1
0 1 230 00POOLE0100MK2ORD00 4 31 1
0 1 231 00POOLE0100MK2ORD00 4 32 1
0 1 232 00POOLE0100MK2ORD00 4 33 1
0 1 233 00POOLE0100MK2ORD00 4 34 1
0 1 234 00POOLE0100MK2ORD00 4 35 1
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 235 00POOLE0100MK2ORD00 4 36 1
0 1 236 00POOLE0100MK2ORD00 4 37 1
0 1 237 00POOLE0100MK2ORD00 4 38 1
0 1 238 00POOLE0100MK2ORD00 4 39 1
0 1 239 00POOLE0100MK2ORD00 4 40 1
0 1 240 00POOLE0100MK2ORD00 4 41 1
127 rows selected.
|
|
|
|
|
|
|
|
| Re: Need help with Queries [message #554996 is a reply to message #554992] |
Sun, 20 May 2012 14:54 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Let me see if I can work through this one post at a time. I gather that you want to use the values of cpstpn and concno selected in step 1 in the query that is step2. You can either use the query from step 1 as an inline view or as a sub-query factoring (with) clause, as I have done below. In general, if you are going to use such a sub-query more than once, then it is better to use the sub-query factoring (with) clause.
SCOTT@orcl_11gR2> WITH
2 step1 AS
3 (SELECT cpstpn, concno
4 FROM (SELECT cpstpn, concno, COUNT (concno) count_concno
5 FROM en1
6 WHERE synthetic LIKE '00POOLE010MK3STSP%'
7 OR synthetic LIKE '00POOLE0100MK3MCI%'
8 OR synthetic LIKE '00POOLE0100MK2ORD%'
9 GROUP BY cpstpn, concno
10 HAVING COUNT (concno) >= 24
11 ORDER BY COUNT (concno) ASC, concno ASC)
12 WHERE ROWNUM = 1)
13 SELECT cpstpn, concno, enum, synthetic, test_case,
14 ROW_NUMBER () OVER (PARTITION BY lct ORDER BY enum) rn, lct
15 FROM (SELECT cpstpn, concno, enum, synthetic,
16 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
17 2 AS test_case
18 FROM en1
19 WHERE (cpstpn, concno) IN (SELECT cpstpn, concno FROM step1)
20 AND (lctype = 'MK3STSP' OR lctype = 'MK3MCI')
21 UNION ALL
22 SELECT cpstpn, concno, enum, synthetic,
23 CEIL (COUNT (*) OVER (ORDER BY enum) / 64) AS lct,
24 3 AS test_case
25 FROM en1
26 WHERE (cpstpn, concno) IN (SELECT cpstpn, concno FROM step1)
27 AND lctype = 'MK2ORD')
28 WHERE synthetic IN
29 ('00POOLE010MK3STSP00',
30 '00POOLE0100MK3MCI00',
31 '00POOLE0100MK2ORD00')
32 ORDER BY test_case, rn, lct
33 /
CPSTPN CONCNO ENUM SYNTHETIC TEST_CASE RN LCT
---------- ---------- ---------- -------------------------- ---------- ---------- ----------
0 1 10 00POOLE010MK3STSP00 2 1 1
0 1 70 00POOLE010MK3STSP00 2 1 2
0 1 11 00POOLE010MK3STSP00 2 2 1
0 1 71 00POOLE010MK3STSP00 2 2 2
0 1 12 00POOLE010MK3STSP00 2 3 1
0 1 72 00POOLE010MK3STSP00 2 3 2
0 1 13 00POOLE010MK3STSP00 2 4 1
0 1 73 00POOLE010MK3STSP00 2 4 2
0 1 14 00POOLE010MK3STSP00 2 5 1
0 1 74 00POOLE010MK3STSP00 2 5 2
0 1 15 00POOLE010MK3STSP00 2 6 1
0 1 75 00POOLE010MK3STSP00 2 6 2
0 1 16 00POOLE0100MK3MCI00 2 7 1
0 1 76 00POOLE010MK3STSP00 2 7 2
0 1 17 00POOLE010MK3STSP00 2 8 1
0 1 77 00POOLE010MK3STSP00 2 8 2
0 1 18 00POOLE010MK3STSP00 2 9 1
0 1 78 00POOLE010MK3STSP00 2 9 2
0 1 19 00POOLE010MK3STSP00 2 10 1
0 1 79 00POOLE010MK3STSP00 2 10 2
0 1 20 00POOLE010MK3STSP00 2 11 1
0 1 80 00POOLE0100MK3MCI00 2 11 2
0 1 200 00POOLE0100MK2ORD00 3 12 1
0 1 201 00POOLE0100MK2ORD00 3 13 1
0 1 202 00POOLE0100MK2ORD00 3 14 1
0 1 203 00POOLE0100MK2ORD00 3 15 1
0 1 204 00POOLE0100MK2ORD00 3 16 1
0 1 205 00POOLE0100MK2ORD00 3 17 1
0 1 206 00POOLE0100MK2ORD00 3 18 1
0 1 207 00POOLE0100MK2ORD00 3 19 1
0 1 208 00POOLE0100MK2ORD00 3 20 1
0 1 209 00POOLE0100MK2ORD00 3 21 1
0 1 210 00POOLE0100MK2ORD00 3 22 1
0 1 211 00POOLE0100MK2ORD00 3 23 1
0 1 212 00POOLE0100MK2ORD00 3 24 1
0 1 213 00POOLE0100MK2ORD00 3 25 1
0 1 214 00POOLE0100MK2ORD00 3 26 1
0 1 215 00POOLE0100MK2ORD00 3 27 1
0 1 216 00POOLE0100MK2ORD00 3 28 1
0 1 217 00POOLE0100MK2ORD00 3 29 1
0 1 218 00POOLE0100MK2ORD00 3 30 1
0 1 219 00POOLE0100MK2ORD00 3 31 1
0 1 220 00POOLE0100MK2ORD00 3 32 1
0 1 221 00POOLE0100MK2ORD00 3 33 1
0 1 222 00POOLE0100MK2ORD00 3 34 1
0 1 223 00POOLE0100MK2ORD00 3 35 1
0 1 224 00POOLE0100MK2ORD00 3 36 1
0 1 225 00POOLE0100MK2ORD00 3 37 1
0 1 226 00POOLE0100MK2ORD00 3 38 1
0 1 227 00POOLE0100MK2ORD00 3 39 1
0 1 228 00POOLE0100MK2ORD00 3 40 1
0 1 229 00POOLE0100MK2ORD00 3 41 1
0 1 230 00POOLE0100MK2ORD00 3 42 1
0 1 231 00POOLE0100MK2ORD00 3 43 1
0 1 232 00POOLE0100MK2ORD00 3 44 1
0 1 233 00POOLE0100MK2ORD00 3 45 1
0 1 234 00POOLE0100MK2ORD00 3 46 1
0 1 235 00POOLE0100MK2ORD00 3 47 1
0 1 236 00POOLE0100MK2ORD00 3 48 1
0 1 237 00POOLE0100MK2ORD00 3 49 1
0 1 238 00POOLE0100MK2ORD00 3 50 1
0 1 239 00POOLE0100MK2ORD00 3 51 1
0 1 240 00POOLE0100MK2ORD00 3 52 1
63 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 07:22:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
