Board index » delphi » strange error with order by clause and left join views

strange error with order by clause and left join views

Hello,

Interbase server is reporting strange error or results when I
make a query on a view with a left join and an order by clause.

In a real aplication I get the following error (using jdbc):
  'conversion error from string "0"'
Interbase error code: 335544334

I tried to reproduce the error on other conditions and I cant
get the error mentioned but strange results on queries.

To reproduce the error I used the following sql DDL:

----------------------------------
create table p1 (
  x_p1       numeric(10,0),
  f_entrada  date
);

create view vp1 (
  x_p1,
  f_entrada
) as
select x_p1, f_entrada from p1;

create table p2 (
  x_p2       numeric(10,0),
  p1_x_p1    numeric(10,0),
  n_one      numeric(10,0),
  n_two      numeric(10,0)
);

create view vp2 (
  p1_x_p1,
  n_one,
  n_two
) as
select p1_x_p1, sum(n_one), sum(n_two)
from p2 group by p1_x_p1;

create view vvp1 (
  p1_x_p1,
  f_entrada,
  n_one,
  n_two
) as
select p1.x_p1, p2.n_one, p2.n_two
from vp1 p1 left join vp2 p2 on p1.x_p1=p2.p1_x_p1;

/* INSERT */
insert into p1 values (1,'07/10/2001');
insert into p1 values (2,'07/13/2001');
insert into p1 values (3,'08/12/2001');
insert into p1 values (4,'08/10/2001');

insert into p2 values (1,1,0,1);
insert into p2 values (2,2,1,0);
insert into p2 values (3,1,0,1);
insert into p2 values (4,2,0,1);
insert into p2 values (5,2,1,1);

------------------------------------------

Where I execute the query: "select * from vvp1;"
the result is ok.

1    10/07/01       0       2
2    13/07/01       2       2
3    12/08/01  <null>  <null>
4    10/08/01  <null>  <null>

But if I execute the query:
"select * from vvp1 order by f_entrada"
the result looks wrong

1    10/07/01  <null>  <null>
2    13/07/01  <null>  <null>
3    12/08/01  <null>  <null>
4    10/08/01  <null>  <null>

Where are the results of sum 0,2,2,2

Does anyone know what is wrong? Is this a bug?

Please, reply also to my email: jmpe...@cedt.junta-andalucia.es

Thank you

 

Re:strange error with order by clause and left join views


Quote
"Jose Ma Perez" wrote:

> Hello,

        Multi-posted.  I responded to this post in
borland.public.interbase.bugdiscussion.  Please note that multiposting
violates Borland's rules for the use of their servers, which you can
read here:

http://www.borland.com/newsgroups/guide.html

        In the future, please choose a single newsgroup for your post.

        Thank you for your cooperation,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Other Threads