creating view with union inside 1

[olivek]

When I do the union like this, it works,
select a, b, c
from A
union
select d, e, to_number(NULL)
from B;

But when I put it inside a view:
create or replace view XXX AS
(select a, b, c
from A
union
select d, e, to_number(NULL)
from B; )
The view can be created, but when I did any select query, I got error message ORA-01790: expression must have same datatype as corresponding expression. I am sure the real data inside the view definitions works by itself, I have worked on it for over 10 hours, still didn't figure it out.

What's wrong with view?

thank you a million!

 

[Turkbear]

HI, it's the field names:

Use aliases like,

(select a, b, c
 from A
 union 
 select d a, e b, to_number(NULL) c
 from B; )

Views need to have names for the fields, and, for a union to work, they must be the same in both select statements.
hth,

 

[olivek]

thank you, turkbear,
in fact, my code is as below, it doesn't work when I try to select any thing from it. I always got the error ORA-01790: expression must have same datatype as corresponding expression. It seems to_number() doesn't work in my case. ???
+++++++++++++++++++++
create or replace view cba_view
(cba_shipper_number,
cba_national_account_nbr,
cba_cust_short_name,
cba_customer_name,
cba_drop_shipper_ind,
cba_email_address,
cba_weight_measurement,
cba_fedex_acct_nbr,
nat_national_account_nbr,
nat_customer_name,
nat_super_national_account_nbr
)
as
(select cba.cba_shipper_number,
cba.national_account_nbr,
cba.cust_short_name,
cba.customer_name,
cba.drop_shipper_ind,
cba.email_address,
cba.weight_measurement,
cba.fedex_acct_nbr,
nat.national_account_nbr,
nat.customer_name,
nat.super_national_account_nbr
from customer_barcode_account cba, national_account nat
where cba.national_account_nbr(+) = nat.national_account_nbr
)
UNION
(select cba.cba_shipper_number,
cba.national_account_nbr,
cba.cust_short_name,
cba.customer_name,
cba.drop_shipper_ind,
cba.email_address,
cba.weight_measurement,
cba.fedex_acct_nbr,
--0,
to_number(NULL),
NULL,
--0,
to_number(NULL)
from customer_barcode_account cba
where cba.national_account_nbr is null
)
;
++++++++++++++++++++++++++++++++++++++++++

 

[Turkbear]

I'm confused by your second query..It appears to have 13 fields while your view and the first query only have 11..

What's with the --0 s and the to_number(NULL) ?

 

[olivek]

In order to make data type consistent, we need to convert null to number or char depending on our specific circumstances.

When I used "to_number(null), null, to_number(null)", I got that error message, so I tried inserting 0 instead "0, null, 0", that works, so I guess the problem is because to_number. But I have to use null, not zero for this case.

--0 means I commented out 0

 

[sem]

Check whether nat.super_national_account_nbr and nat.national_account_nbr are also numbers.

Regards, Dima

 

[dpuneet]

For a union to work you should have the same count and type of columns in the select statement.
As far as I can see there are a couple of things you can set right.
First. You cannot convert NULL to number. So remove the To_number conversion. NULL is acceptable datatype which can be mixed with any and all datatypes.

Second remove the parenthesis around your select statements. The parenthesis should start after
the "AS" and end after the second SQL Statement.
also try and remove the "--0".

See if this works.

 

[sem]

Dpuneet: you can convert NULL to number as well as to char and to date. When you have a UNION, each field in subsequent subqueries is casted to the type of corresponding field of the first one. In this situation NULL in the first (main) subquery is treated as varchar2. If you need to join it with date field and get date datatype in the result set, you should either change the order of subqueries or explicitly cast NULL to date.

Regards, Dima