No data when query run using Excel from Oracle

[aseem12321]

Hi,

I would like to extract data from Oracle to Excel. I have created and tested the connection using ADO. When I run the query in Oracle SQL Developer client, it runs fine. However the same query run in Excel, does not provide any data. Here is the query:

select decode(b.location,'Bangkok','Thailand','Beijing','China','China WSBB','China','Colombo','Sri Lanka','Colombo','Sri Lanka', 'DBIA', 'Singapore','DSI Tokyo','Japan','Guangzhou','China','Ho Chi Minh','Vietnam','Hong Kong','HONGKONG', 'Hong Kong LON','HONGKONG','Jakarta','Indonesia','Karachi','Pakistan','Kuala 
Lumpur','Malaysia','London','Malaysia', 'Manila','Philippines','Mumbai','India','Seoul','South Korea','Shanghai','China','Singapore','Singapore', 'Singapore local','Singapore','Sydney','ANZ','Sydney GMF','ANZ','Taipei','Taiwan','Tokyo 
Consolidated','Japan', 'Tokyo Sales','Japan',b.location), count(t.systemid) 
from rms_owner.trade t, book b
where t.systemid > 209240000
and t.systemid < 209250000
and t.book in (
select name from book where location in ('Bangkok','Beijing','China 
WSBB','Colombo','Colombo','DBIA','DSI Tokyo','Guangzhou',
'Ho Chi Minh','Hong Kong','Hong Kong LON','Jakarta','Karachi','Kuala 
Lumpur','Manila','Mumbai','Seoul','Shanghai','Singapore',
'Singapore local','Sydney','Sydney GMF','Taipei','Tokyo Consolidated','Tokyo Sales')
AND status = 'Open' AND bookgroupYN = 'N'   
)
and t.lastupdated between '01-JUN-2009' and '30-JUN-2009'
and t.datecreated between '01-JUN-2009' and '30-JUN-2009'
and t.tradetable  not in ( 'FXCASHBALANCE','CASHBALANCE')
and t.action !='Roll'
and b.name = t.book
group by b.location

Why is there a problem?

[RhinoBull]

Try giving your decoded field allias (perhaps location?):

SELECT
	decode(b.location,
		  'Bangkok','Thailand',
		  'Beijing','China',
		  'China WSBB','China',
		  'Colombo','Sri Lanka',
		  'Colombo','Sri Lanka', 
		  'DBIA', 'Singapore',
		  'DSI Tokyo','Japan',
		  'Guangzhou','China',
		  'Ho Chi Minh','Vietnam',
		  'Hong Kong','HONGKONG', 
		  'Hong Kong LON','HONGKONG',
		  'Jakarta','Indonesia',
		  'Karachi','Pakistan',
		  'Kuala Lumpur','Malaysia',
		  'London','Malaysia', 
		  'Manila','Philippines',
		  'Mumbai','India',
		  'Seoul','South Korea',
		  'Shanghai','China',
		  'Singapore','Singapore', 
		  'Singapore local','Singapore',
		  'Sydney','ANZ',
		  'Sydney GMF','ANZ',
		  'Taipei','Taiwan',
		  'Tokyo Consolidated','Japan', 
		  'Tokyo Sales','Japan',
		  b.location)    location, 
	count(t.systemid) 
FROM
	rms_owner.trade t, book b
where 
	t.systemid > 209240000
and t.systemid < 209250000
and t.book in (select name 
			   from book 
			   where location in ('Bangkok','Beijing','China WSBB','Colombo','Colombo','DBIA','DSI Tokyo','Guangzhou',
								  'Ho Chi Minh','Hong Kong','Hong Kong LON','Jakarta','Karachi','Kuala Lumpur',
								  'Manila','Mumbai','Seoul','Shanghai','Singapore',
								  'Singapore local','Sydney','Sydney GMF','Taipei','Tokyo Consolidated','Tokyo Sales')
				AND status = 'Open' 
				AND bookgroupYN = 'N'   
				)
and t.lastupdated between '01-JUN-2009' and '30-JUN-2009'
and t.datecreated between '01-JUN-2009' and '30-JUN-2009'
and t.tradetable  not in ( 'FXCASHBALANCE','CASHBALANCE')
and t.action !='Roll'
and b.name = t.book
group by b.location

Also, why not defining a Country directly in the database so you wouldn't have to decode that many values?

[aseem12321]

Hi,

I tried using alias. It did not work. I dont think the problem is in decode. When I tried running the query using * instead of decode, it still did not work in excel.

Regards.

[abhijit]

Hi,

I tried using alias. It did not work. I dont think the problem is in decode. When I tried running the query using * instead of decode, it still did not work in excel.

Regards.

Are you able to fetch data from Oracle into your Excel spreadsheet?
Did you try with any other table?

Sometimes the issue lies elsewhere, like user permissions on the object, etc.

[aseem12321]

Hi, I found the error. I was just using column name to fetch data where as when I tried using table.column, it worked. Thanks guys.

Cheers.