Please respond to this query

Conditions:
map table1.column1 if not null  / spaces else table2.column2 if not null/spaces
else spaces

column 1 datatype is in char ----it should be in varchar2
column 2 datatypes is in numeric value ------it should be in varchar2

Query:


Select(case when table1.column1 is not null and table1.column1 = ''
then cast( table1.column1 as varchar2(25)) when table2.column2 is not null and
table2.column2 ='' then cast(table2.column2 as varchar2(25)) else '' end) from table1,table2

The error is as

table 1 has invalid identifier



arukuAsked:
Who is Participating?
 
jaanthCommented:
Aruku,

Your query runs against sample tables just fine,

drop table table1 ;
create table table1 ( column1 char ) ;
drop table table2 ;
create table table2 ( column2 numeric ) ;

insert into table1 (column1) values ('X') ;
insert into table1 (column1) values ('Y') ;
insert into table1 (column1) values ('Z') ;
insert into table1 (column1) values ('') ;
insert into table1 (column1) values (null) ;

insert into table2 (column2) values ('') ;
insert into table2 (column2) values (0) ;
insert into table2 (column2) values (1) ;
insert into table2 (column2) values (2) ;
insert into table2 (column2) values (null) ;

select * from table1, table2 ;

/* Your Query, pasted from above */
Select(case when table1.column1 is not null and table1.column1 = ''
then cast( table1.column1 as varchar2(25)) when table2.column2 is not null and
table2.column2 ='' then cast(table2.column2 as varchar2(25)) else '' end) from table1,table2 ;

Are your column names correct for your actual tables?
The result of your query is blank rows, one output row for each of cross join row. Is this really what you want?

jaanth
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>from table1,table2

is there no link/join between the 2 tables?
apart from that, I see no code that would raise that error.
can you clarify how exactly you run it, please?
0
 
tigin44Commented:
your code is seems clear... expect the relation between the tables... in your case there is a cross join... you should define the relation between the tables...
Select case when table1.column1 is not null and table1.column1 = '' then cast( table1.column1 as varchar2(25)) 
			when table2.column2 is not null and table2.column2 ='' then cast(table2.column2 as varchar2(25)) 
		else '' end
from table1,table2

Open in new window

0
 
arukuAuthor Commented:
As there is no condition in common between the tables Do I need to join them?
as when the first condition is not satisfied it will go to the next one and the first one get
overlapped. I am running the same error in Oracle but i am getting the error as table 1 is indefinate. Do i need to join the table
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please show some sample data from the 2 tables, as well as the resulting data requested...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.