Forum Stats

  • 3,851,566 Users
  • 2,263,999 Discussions
  • 7,904,781 Comments

Discussions

Categories

Export query result to Excel file

lemalhu
lemalhu Member Posts: 14
edited Apr 14, 2020 11:38AM in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Apr 14, 2020 9:00AM 
    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.

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,747 Blue Diamond
    edited Apr 14, 2020 9:18AM

    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')

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Apr 14, 2020 9:13AM

    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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Apr 14, 2020 9:14AM

    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'.

    · Share on TwitterShare on Facebook
  • lemalhu
    lemalhu Member Posts: 14
    edited Apr 14, 2020 10:44AM

    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

    · Share on TwitterShare on Facebook
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Apr 14, 2020 11:38AM

    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.

    · Share on TwitterShare on Facebook