Export query result to Excel file

lemalhu

Hello together,

i have to work with a database now i got the problem that i have only readersonly function so i got no possibility to add an additionaly column. Now i got an idea where i want to export my query result to an excel file or into a R-file. So if there a way to export this would be great.

This is my little Code:

select reporting_dt, contract_id, sum(RATING_CONTRACT_CLASS_CD_1) - lag(sum(RAting_contract_class_cd_1), 1, 0) over (partition by contract_id order by reporting_dt ) as delta

from database

where reporting_dt>='31.12.2019' and RATING_CONTRACT_CLASS_CD_1!='U'

group by reporting_dt, contract_id

order by delta asc

);

Would be great if you could help me.

Thank you

EdStevens

lemalhu wrote:Hello together, i have to work with a database now i got the problem that i have only readersonly function so i got no possibility to add an additionaly column. Now i got an idea where i want to export my query result to an excel file or into a R-file. So if there a way to export this would be great. This is my little Code:select reporting_dt, contract_id, sum(RATING_CONTRACT_CLASS_CD_1) - lag(sum(RAting_contract_class_cd_1), 1, 0) over (partition by contract_id order by reporting_dt ) as deltafrom databasewhere reporting_dt>='31.12.2019' and RATING_CONTRACT_CLASS_CD_1!='U'group by reporting_dt, contract_idorder by delta asc);Would be great if you could help me. Thank you 

It is certainly possible to have Excel pull data from an external data source like Oracle database.  Several of the business analysts at my org do so.

As for needing to add another column, why don't you discuss this with your DBA or app support team?  It sounds like you are trying to solve your issue all by yourself instead of working with others.

Paulzip

Why export to an Excel file?  What is that going to achieve?

When you say read only, you mean you can't add columns to tables?  Can you create views?

By the way, if reporting_dt is a date, then you shouldn't be comparing to a string '31.12.2019' - that is NOT a date, you are relying on implicit conversion to a date. You should do

reporting_dt >= to_date('31.12.2019', 'dd.mm.yyyy')

Mike Kutz

SQL*Developer can export to Excel.

Also, Oracle supplies an Excel plugin to fetch/update REST enabled tables (via ORDS).

If "REPORTING_DT" is a DATE data type column, that is not how you compare DATEs.  Do not rely on implicit conversions. Use the TO_DATE() (with format) or the ANSI SQL keyword DATE (and correct date format).

MK

Frank Kulash

Hi,

Whenever you have a problem, start by explaining exactly what the problem is.

Don't mistake a solution for a problem.  Adding an additional column to a table, exporting data to a spreadsheet, and the query you posted all look like possible solutions to some problem.  The first thing you have to explain is what the problem is.

Post CREATE TABLE and INSERT statements for a little sample data in any existing tables.

lemalhu wrote:...where reporting_dt>='31.12.2019' and  ...

Use DATE columns for dates, and don't try to compare DATEs to strings, such as '31.12.2019'.

lemalhu

So sorry for my unclear formulation.

I spoke with some guys in our company and they told me for me its not possible to join anything in this database because im only there for an internship.

So they said i have to try it in R. So my idea was to export my results in SQL to R and join the necessary colmn there.

Thanks for your help im new in this business and at the moment its quite hard to learn everything, so sorry for some miscomunication at the beginning

chris227

With sqlplus you can do

spool yourtextfile.txt

your statement goes here

spool off

The output is written into the file as it woulb be display on the screen.

There are several options to customize this output like set feedback off etc.

For those you might take a closer look into the docs on sqlplus.

Furthermore there is this newe version of sqlplus called sqlcl with seems to be qit more handy for this kind of task, e. g. it offers an export function for csv inherently.