problem with a query

[Pathwary]

We are using Adaptive Server Anywhere Version 9.0 and running into a problem
which only happens in this version.

We have a query with union each block of the query works fine with out the
union until we join them using the union. The value on entered_value column
is set to zero with union. Without union the column value is non zero. This
same query works fine in v7 database. Any help you could provide is
appreciated.
Thanks

select

entered_value from

v_test where

parent_id is not null

union

select

entered_value from

v_test where

parent_id is null

[Reg Domaratzki]

We'll need some more details, including the exact version and build number
of ASA, the definition of v_test (which I'll assume is a view), the data
values in the database, and what the problem actually is. Does the server
GPF, does the query run slow, does it return incorrect results, does it give
a syntax error?

The following script worked great for me in v9.0.1 build 1751 (three rows
returned with values 1,NULL and 4) :

if exists ( select 1 from systable where table_name = 'v_test' ) then
drop table v_test
end if;

create table v_test (
pk integer default autoincrement primary key,
entered_value integer null,
parent_id integer null
);

insert into v_test values (DEFAULT, 1, 2);
insert into v_test values (DEFAULT, NULL, 3);
insert into v_test values (DEFAULT, 4, NULL);
insert into v_test values (DEFAULT, NULL, NULL);

select entered_value from v_test where parent_id is not null
union

select entered_value from v_test where parent_id is null;

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Pathwary" <path...@hotmail.com> wrote in message
news:402bb2ba$1@forums-1-dub...

[Breck Carter [TeamSybase]]

Please show us what v_test looks like, and what the rows look like.

Breck

On 12 Feb 2004 09:07:06 -0800, "Pathwary" <path...@hotmail.com>
wrote:

--
bca...@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

[Pathwary]

The version I have is 9.0.1.1751.

The query runs very fast and gives no syntax error. It's
just that when both queries are combined and run together
the value is set to 0 in entered_value column.

[Reg Domaratzki]

You still haven't shown us the strcuture of the table(s) in question, or the
data that exists in the table(s).

--
Reg

<Pathwary> wrote in message news:402bd3e6.5d...@sybase.com...