[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Please respond to this query

Posted on 2009-12-18
5
Medium Priority
?
447 Views
Last Modified: 2013-12-18
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



0
Comment
Question by:aruku
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26084349
>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
 
LVL 26

Expert Comment

by:tigin44
ID: 26084377
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
 

Author Comment

by:aruku
ID: 26084462
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26084473
please show some sample data from the 2 tables, as well as the resulting data requested...
0
 
LVL 7

Accepted Solution

by:
jaanth earned 2000 total points
ID: 26084859
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question