No data when query run using Excel from Oracle

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:

Code:

---

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?

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

Code:

---

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?

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.

Quote:

---

Originally Posted by 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.

---

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.

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.