Forum Stats

  • 3,836,764 Users
  • 2,262,185 Discussions
  • 7,900,102 Comments

Discussions

Generate the query result in excel

user12251389
user12251389 Member Posts: 334 Blue Ribbon
edited Jul 31, 2017 9:17AM in SQL & PL/SQL

Currently i am using oracle report to generate the weekly reports in excel. There is sql queries behind this reports. The oracle report server is most of the time down and which i dont find reliable to generate weekly report.

So i want to automate my sql queries and generate report on excel instead of copying the query result and paste into excel.  I can use Procedure or PL/SQL block to do this. But i am not sure whether its possible to create excel file and generate the report using PL/SQL.The PL/SQL block or procedure should be parameterized based on RESOURCE_ID coulmn as i can use the same procedure for another resources also. I am using oracle sql developer tool for writing the oracle queries.

Below are the 2 queries:

--Query 1select  db,db_date,count(distinct sales_id)from Sales_Orderwheredb='Test'and resource_id=2 anddb_date between 20170710 and 20170716group by db,db_date--Query 2select  db,db_date,count(distinct it_id)from IT_INFOwheredb='Test'and resource_id=2 anddb_date between 20170710 and 20170716group by db,db_date

And i want to generate the report on excel file like below:

pastedImage_2.png

Tagged:
EdStevens
«134

Answers

  • Kalpataru
    Kalpataru Member Posts: 4,619 Bronze Crown
    edited Jul 27, 2017 6:12AM

    What is your Oracle Database Version ?

    Mention 4 digit database version number.

    Post CREATE TABLE and INSERT STATEMENT with sample data for testing.

  • Saubhik
    Saubhik Member Posts: 5,805 Gold Crown
    edited Jul 27, 2017 6:38AM
  • Stew Ashton
    Stew Ashton Member Posts: 2,907 Bronze Crown
    edited Jul 27, 2017 6:48AM

    In this particular situation, I would do as much as possible in SQL, then put the result in excel.

    Here is what I would do for the SQL part. Since I don't have your data, I'm going to use the EMP table twice.

    drop table t purge;create table t as select deptno, job, sal from emp;drop table u purge;create table u as select deptno, job, comm from emp;select * from (    select 'SAL' col, deptno, job, sal val    from t    where deptno = 30    union all    select 'COMM', deptno, job, comm    from u    where deptno = 30)pivot(sum(val) for col in('SAL' as SAL, 'COMM' as COMM));    DEPTNO JOB              SAL       COMM---------- --------- ---------- ----------        30 SALESMAN        5600       2200        30 CLERK            950                   30 MANAGER         2850 

    Warning, you need Oracle version 11 or later to use PIVOT.

    Best regards, Stew Ashton

  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited Jul 27, 2017 7:07AM

    If you reports server is down so often that it affects your ability to generate a weekly report then it sound like you need to fix you reports server!?

    EdStevens
  • user12251389
    user12251389 Member Posts: 334 Blue Ribbon
    edited Jul 27, 2017 7:17AM

    My oracle version is 11.2.0.4. And i already mentioned sample data in question.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jul 27, 2017 7:43AM
    user12251389 wrote:My oracle version is 11.2.0.4. And i already mentioned sample data in question.

    You "mention the data", but you did not provide scripts that would allow other to reproduce your test case. 

    How to ask questions in OTN Spaces

  • user12251389
    user12251389 Member Posts: 334 Blue Ribbon
    edited Jul 27, 2017 7:58AM

    I just need to run the Select queries to get the weekly report. I cannot or dont want to drop or create table. So why i need to reproduce in this case ?

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jul 27, 2017 8:26AM
    user12251389 wrote:I just need to run the Select queries to get the weekly report. I cannot or dont want to drop or create table. So why i need to reproduce in this case ? 

    You may not need to reproduce the tables, but WE need to produce tables like yours so we can test new SQL against them.

    We also need same data to populate the tables so we have data to test with.

  • user12251389
    user12251389 Member Posts: 334 Blue Ribbon
    edited Jul 27, 2017 9:50AM

    I have send the actual data also with create statement.

    Create table sales_order(sys_db_name varchar(20),entity_id number,shipping_dayid varchar(30),ordmast_srcid number);

    Create table tax_order(sys_db_name varchar(20),entity_id number,shipping_dayid varchar(30),ordmast_srcid number);

    --data for sales_order table

    Insert into sales_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170713,32796);

    Insert into sales_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170716,1447);

    Insert into sales_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170714,9515);

    Insert into sales_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170715,33806);

    Insert into sales_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170710,47831);

    Insert into sales_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170711,40520);

    Insert into sales_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170712,36217);

    --data for tax_order table

    Insert into tax_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170713,22566);

    Insert into tax_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170716,51447);

    Insert into tax_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170714,49515);

    Insert into tax_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170715,63806);

    Insert into tax_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170710,77831);

    Insert into tax_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170711,80520);

    Insert into tax_order (SYS_DB_NAME,ENTITY_ID,SHIPPING_DAYID,"COUNT(DISTINCTORDMAST_SRCID)") values ('FR',2,20170712,96217);

    --First query

    select  sys_db_name,entity_id,shipping_dayid, count(distinct ordmast_srcid)

    from sales_order

    where shipping_dayid between 20170710 and 20170716

    and entity_id = 2

    group by sys_db_name,entity_id,shipping_dayid;

    --Second query

    select  sys_db_name,entity_id,shipping_dayid, count(distinct ordmast_srcid)

    from tax_order

    where shipping_dayid between 20170710 and 20170716

    and entity_id = 2

    group by sys_db_name,entity_id,shipping_dayid

    Below is the excel which is generating fo reports:

    pastedImage_4.png

  • Stew Ashton
    Stew Ashton Member Posts: 2,907 Bronze Crown
    edited Jul 27, 2017 9:59AM

    OK, I'll redo my solution with your data now:

    select * from (    select  'SALES' tbl, sys_db_name,entity_id, shipping_dayid, ordmast_srcid    from sales_order    union all    select  'TAX' tbl, sys_db_name,entity_id, shipping_dayid, ordmast_srcid    from tax_order)pivot(count(distinct ordmast_srcid) for tbl in('SALES' sales, 'TAX' tax))where shipping_dayid between 20170710 and 20170716and entity_id = 2;

    SYS_DB_NAMEENTITY_IDSHIPPING_DAYIDSALESTAX
    FR22017071011
    FR22017071111
    FR22017071211
    FR22017071311
    FR22017071411
    FR22017071511
    FR22017071611

    You can adjust the column headings and remove ENTITY_ID if you want. That will give you something that is as close to the excel result as possible.

    From there, you still want to put that data into an excel file. I'll let someone who knows better answer that part.

    Best, Stew

This discussion has been closed.