Generate the query result in excel

user12251389

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:

Kalpataru

What is your Oracle Database Version ?

Mention 4 digit database version number.

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

Saubhik

Stew Ashton

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

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!?

user12251389

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

EdStevens

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

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

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

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:

Stew Ashton

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_NAME	ENTITY_ID	SHIPPING_DAYID	SALES	TAX
FR	2	20170710	1	1
FR	2	20170711	1	1
FR	2	20170712	1	1
FR	2	20170713	1	1
FR	2	20170714	1	1
FR	2	20170715	1	1
FR	2	20170716	1	1

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