I have problem with sorting SQL query.
Code: Select all
-- Create table
create table TECH_CLASS
(
ID NUMBER(10) not null,
NAME VARCHAR2(254),
DESCRIPTION VARCHAR2(256),
ALIAS VARCHAR2(16)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TECH_CLASS
add constraint PK_TECH_CLASS primary key (ID);
-- Create table
create table TECH_PROCESS
(
PROCESS_TYPE_ID NUMBER(10),
DT_START DATE,
DT_END DATE,
ID NUMBER(10) not null,
ALIAS VARCHAR2(16),
NAME VARCHAR2(254),
B_ID NUMBER(10)
);
alter table TECH_PROCESS
add constraint PK_TECH_PROCESS primary key (ID);
-- Create table
create table PROD_TECHNOLOGY
(
ID NUMBER(10) not null,
TECHNOLOGY_ID NUMBER(10),
PRODUCTION_ID NUMBER(10),
DT_START DATE,
DT_END DATE
);
-- Create/Recreate primary, unique and foreign key constraints
alter table PROD_TECHNOLOGY
add constraint PK_PROD_TECHNOLOGY primary key (ID);
alter table PROD_TECHNOLOGY
add constraint FK_PROD_TECHNOLOGY_TECH_PROC foreign key (TECHNOLOGY_ID)
references TECH_PROCESS (ID)
disable;
-- Create table
create table TECH_TYPE
(
ID NUMBER(10) not null,
NAME VARCHAR2(254) not null,
DESCRIPTION VARCHAR2(256),
TECH_CLASS_ID NUMBER(10)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TECH_TYPE
add constraint PK_TECH_TYPE primary key (ID);
alter table TECH_TYPE
add constraint FK_TYPE_CLASS foreign key (TECH_CLASS_ID)
references TECH_CLASS (ID);
-- Create table
create table TECH
(
ID NUMBER(10) not null,
NAME VARCHAR2(254) not null,
DESCRIPTION VARCHAR2(256),
TYPE_ID NUMBER(10) not null,
SWAP_SEASON NUMBER(1) default 0,
DT_START DATE,
DT_END DATE,
ALIAS VARCHAR2(16),
DT_CONFIRM DATE,
PRODUCT1_ID NUMBER(10),
PRODUCT1_ALIAS VARCHAR2(16)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TECH
add constraint PK_TECH primary key (ID);
alter table TECH
add constraint FK_TECH_TYPE_ID foreign key (TYPE_ID)
references TECH_TYPE (ID);
-- Create table
create table TECH_PROCESS_TECH
(
TECH_PROCESS_ID NUMBER(10) not null,
TECH_ID NUMBER(10) not null
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TECH_PROCESS_TECH
add constraint PK_TECH_PROCESS_TECH primary key (TECH_PROCESS_ID, TECH_ID);
alter table TECH_PROCESS_TECH
add constraint FK_TPT_TECH_ID foreign key (TECH_ID)
references TECH (ID);
alter table TECH_PROCESS_TECH
add constraint FK_TPT_TECH_PROCESS_ID foreign key (TECH_PROCESS_ID)
references TECH_PROCESS (ID);
-- Create table
create table TECH_SEASON
(
ID NUMBER(10) not null,
TECH_ID NUMBER(10) not null,
DESCRIPTION VARCHAR2(256),
DT_START NUMBER(10) not null,
DT_END NUMBER(10) not null
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TECH_SEASON
add constraint PK_TECH_SEASON primary key (ID);
alter table TECH_SEASON
add constraint FK_TECH_SEASON_TECH_ID foreign key (TECH_ID)
references TECH (ID);
-- Create table
create table TECH_STAGE
(
ID NUMBER(10) not null,
TECH_SEASON_ID NUMBER(10) not null,
AGE_START NUMBER(10) not null,
AGE_END NUMBER(10) not null,
DESCRIPTION VARCHAR2(256),
PRODUCT1_ALIAS VARCHAR2(16),
PRODUCT1_ID NUMBER(10),
PRODUCT1_QUANTITY1 NUMBER(20,10),
PRODUCT1_QUANTITY2 NUMBER(20,10),
PRODUCT2_ALIAS VARCHAR2(16),
PRODUCT2_ID NUMBER(10),
PRODUCT2_QUANTITY1 NUMBER(20,10),
PRODUCT2_QUANTITY2 NUMBER(20,10),
EXPENCE_CHARGE1 NUMBER(10),
EXPENCE_CHARGE2 NUMBER(10)
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TECH_STAGE
add constraint PK_TECH_STAGE primary key (ID);
alter table TECH_STAGE
add constraint FK_TECH_STAGE_TECH_SEASON_ID foreign key (TECH_SEASON_ID)
references TECH_SEASON (ID);
-- Create/Recreate indexes
create unique index UK_TECH_STAGE on TECH_STAGE (TECH_SEASON_ID, AGE_START, AGE_END, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT2_ALIAS, PRODUCT2_ID);
--insert data into tables
insert into TECH_CLASS (ID, NAME, DESCRIPTION, ALIAS)
values (302, 'Приріст ваги', 'Приріст ваги', 'weight_grow');
insert into TECH_PROCESS (PROCESS_TYPE_ID, DT_START, DT_END, ID, ALIAS, NAME, B_ID)
values (6, to_date('01-01-2007', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), 5, null, 'Утримання ВС Ломан Біла', null);
insert into PROD_TECHNOLOGY (ID, TECHNOLOGY_ID, PRODUCTION_ID, DT_START, DT_END)
values (22, 5, 286, to_date('01-01-2007 15:52:48', 'dd-mm-yyyy hh24:mi:ss'), to_date('31-12-9999', 'dd-mm-yyyy'));
insert into tech_type (id, name, tech_class_id)
values (32, 'ТН Приріст ваги Ломан ВС Біла', 302);
insert into TECH (ID, NAME, DESCRIPTION, TYPE_ID, SWAP_SEASON, DT_START, DT_END, ALIAS, DT_CONFIRM, PRODUCT1_ID, PRODUCT1_ALIAS)
values (109, 'ТН Приріст ваги Ломан ВС Біла', null, 32, null, to_date('01-01-2007', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), null, null, 1097, 'bird_layer_white');
insert into tech_process_tech (TECH_PROCESS_ID, TECH_ID)
values(5, 109);
insert into tech_season(id, tech_id, dt_start, dt_end)
values(109, 109, 1, 366);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3737, 109, 561, 561, null, 'bird_layer_white', 1097, null, 0, 'bird_layer_white', 1097, null, 0, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3744, 109, 568, 568, null, 'bird_layer_white', 1097, null, 0, 'bird_layer_white', 1097, null, 0, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3751, 109, 575, 575, null, 'bird_layer_white', 1097, null, 0, 'bird_layer_white', 1097, null, 0, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3758, 109, 582, 582, null, 'bird_layer_white', 1097, null, 0, 'bird_layer_white', 1097, null, 0, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3765, 109, 589, 589, null, 'bird_layer_white', 1097, null, 0, 'bird_layer_white', 1097, null, 0, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3275, 109, 99, 99, null, 'bird_layer_white', 1097, null, 1.08, 'bird_layer_white', 1097, null, 1.088, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3282, 109, 106, 106, null, 'bird_layer_white', 1097, null, 1.129, 'bird_layer_white', 1097, null, 1.136, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3296, 109, 120, 120, null, 'bird_layer_white', 1097, null, 1.222, 'bird_layer_white', 1097, null, 1.23, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3317, 109, 141, 141, null, 'bird_layer_white', 1097, null, 1.396, 'bird_layer_white', 1097, null, 1.406, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3324, 109, 148, 148, null, 'bird_layer_white', 1097, null, 1.458, 'bird_layer_white', 1097, null, 1.466, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3338, 109, 162, 162, null, 'bird_layer_white', 1097, null, 1.546, 'bird_layer_white', 1097, null, 1.552, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3380, 109, 204, 204, null, 'bird_layer_white', 1097, null, 1.692, 'bird_layer_white', 1097, null, 1.694, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3660, 109, 484, 484, null, 'bird_layer_white', 1097, null, 1.776, 'bird_layer_white', 1097, null, 1.776, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3716, 109, 540, 540, null, 'bird_layer_white', 1097, null, 1.786, 'bird_layer_white', 1097, null, 1.786, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3723, 109, 547, 547, null, 'bird_layer_white', 1097, null, 1.787, 'bird_layer_white', 1097, null, 1.787, 2, 2);
insert into TECH_STAGE (ID, TECH_SEASON_ID, AGE_START, AGE_END, DESCRIPTION, PRODUCT1_ALIAS, PRODUCT1_ID, PRODUCT1_QUANTITY1, PRODUCT1_QUANTITY2, PRODUCT2_ALIAS, PRODUCT2_ID, PRODUCT2_QUANTITY1, PRODUCT2_QUANTITY2, EXPENCE_CHARGE1, EXPENCE_CHARGE2)
values (3730, 109, 554, 554, null, 'bird_layer_white', 1097, null, 1.789, 'bird_layer_white', 1097, null, 1.79, 2, 2);
commit;http://depositfiles.com/files/ikbwp6shz
Password: dboratest
Current version of dbExpress driver and full source are in the same rar- file.
Version of Oracle is 10g (10.2.0).
Fill all fields ("Data Base", "User" and "Password"), press "Connect" button.
If your connect is succssesful, copy SQL query into the text field "Query" and press "Execute" button.
Code: Select all
select t.week_age,
t.vaga_norm1
from (
select to_number(ceil(tsg.age_start/7)) as week_age,
tsg.product1_quantity2*1000 as vaga_norm1,
tsg.age_start
--max(tsg.age_start) keep (dense_rank last order by tsg.product1_quantity2) as age_start,
--(max(tsg.product1_quantity2)*1000) as vaga_norm1
from prod_technology pt,
tech_process tpr,
tech_process_tech tprt,
tech t,
tech_class tc,
tech_type tt,
tech_season ts,
tech_stage tsg
where pt.production_id = 286
and pt.technology_id=tpr.id
and tprt.tech_process_id=tpr.id
and tprt.tech_id=t.id
and t.type_id = tt.id
and tt.tech_class_id = tc.id
and tc.alias = 'weight_grow'
and t.product1_id = tsg.product1_id
and t.id = ts.tech_id
and tsg.tech_season_id = ts.id
and tsg.product1_id = 1097
and tsg.age_start >= 99
and sysdate between t.dt_start and t.dt_end
and rownum <= 500
) t
order by t.week_ageIt's very strange, but if you delete comments from the text field with SQL query, sorting will be correct.
Code: Select all
--max(tsg.age_start) keep (dense_rank last order by tsg.product1_quantity2) as age_start,
--(max(tsg.product1_quantity2)*1000) as vaga_norm1