Sort SQL query problem

Sort SQL query problem

Postby kit-xxi » Mon 11 Apr 2011 09:39

Hi!
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;


Then run our test program TestSQLQuery.exe that is in Sort Test Example.rar.
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_age

You may see that sort by field "week_age" is wrong.
It'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


Thanks!
kit-xxi
 
Posts: 2
Joined: Mon 11 Apr 2011 08:39

Postby AlexP » Thu 14 Apr 2011 06:24

Hello,

This problem is connected with the fact that SimpleDataSet sorts the received DataSet inside, without considering users' Order By.
You sould use the SQLQuery, ClientDataSet, and DataSetProvider components with the poRetainServerOrder option set to True to solve the problem.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby kit-xxi » Wed 11 May 2011 10:28

AlexP wrote:Hello,

This problem is connected with the fact that SimpleDataSet sorts the received DataSet inside, without considering users' Order By.
You sould use the SQLQuery, ClientDataSet, and DataSetProvider components with the poRetainServerOrder option set to True to solve the problem.


Thank you very much!
Problem solved
)))
kit-xxi
 
Posts: 2
Joined: Mon 11 Apr 2011 08:39

Postby AlexP » Wed 11 May 2011 10:53

Hello,

It is good to see that this problem was solved. If any other questions come up, please contact us.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for Oracle