Solved

Oracle queries --Challenging Question

Posted on 2016-09-19
12
74 Views
Last Modified: 2016-09-21
Hi team,

I need to write a query using Oracle SQL , where in I need to generate an DRILL DOWN report . I need to generate this using queries no reporting tools.  
Any sample example to do so will be really helpful.
0
Comment
Question by:sam_2012
12 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 41805776
It is possible to generate a complex drill down or hierarchical query in Oracle. I would suggest you provide a sample data  and a desired result
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41805791
when you provide your sample data and expected results, please also specify your database version (all 4 numbers, for example 11.2.0.4  or 12.1.0.2)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41805814
In information technology to drill down means to move from one place to another, information to detailed data by focusing in on something. In a GUI-environment, "drilling-down" may involve clicking on some representation in order to reveal more detail.[1]
https://en.wikipedia.org/wiki/Drill_down

My interpretation of "drill-down" requires some "click to open the next level" activity which is not possible by SQL alone. So rather than ask us for "any example" I suggest you need to guide us on what it is you want.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41805907
you may try to look for some BI (Business Intelligence) solutions for examples: QlikView, Tableau, etc which read your data as the source and present it in the relevant dashboards, which include the drill down features.

I believe Oracle do have their own BI solutions but I'm not familiar with.

>>I need to generate this using queries no reporting tools.
ok, noted with that but just wondering why no?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41805957
some of the most advanced queries for items like this you'll find in the warehouse guide
http://docs.oracle.com/database/121/DWHSG/aggreg.htm#DWHSG020

to be able to drill down, you usually need to cut it up into details first like facts and time
before being able to roll up or drill down

you could produce a webpage with the static data in a csv and then use d3 to display it graphically
https://d3js.org/

like a grouped bar chart:
http://bl.ocks.org/mbostock/3887051
0
 

Author Comment

by:sam_2012
ID: 41807076
Hi Team,

 I have attached the sample report and data. Kindly help me in this regard.
Data-Sheet.xlsx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sam_2012
ID: 41807078
Hi Sdstuber,

I have attached the sample data and report format for your reference.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41807740
Such a result is produced by an option in the group by clause:

GROUP BY ROLLUP
0
 

Author Comment

by:sam_2012
ID: 41807900
Hi PortletPaul,

I have written the below query for the output , But I need to format the output , I want the grand Total to appear in the last is there a way for it.

Select Zone, District , IndustrialArea , count(inspection) inspection,
count(dataexcel) dataexcel,
count(status) status,
count(EXCELintegration) EXCELintegration,
count(ValidationStatus) ValidationStatus
from Temp_data
group by rollup(Zone, District , IndustrialArea)
order by zone NULLS FIRST, district NULLS FIRST , industrialarea NULLS FIRST
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41808075
NULLS FIRST is a problem because each total/sub total will display nulls

Try this perhaps (no guarantee)
SELECT
        Zone
      , District
      , IndustrialArea
      , COUNT(inspection)       inspection
      , COUNT(dataexcel)        dataexcel
      , COUNT(status)           status
      , COUNT(EXCELintegration) EXCELintegration
      , COUNT(ValidationStatus) ValidationStatus
FROM Temp_data
GROUP BY
        ROLLUP (Zone, District, IndustrialArea)
ORDER BY
      , grouping(Zone)
      , zone NULLS FIRST
      , grouping(District)
      , district NULLS FIRST 
      , grouping(IndustrialArea)
      , industrialarea NULLS FIRST

Open in new window

These may be used as columns in the select clause also so you can see what the do:
      , grouping(Zone)
      , grouping(District)
      , grouping(IndustrialArea)

Open in new window

You may have to "fiddle" to get the best outcome.
0
 

Author Closing Comment

by:sam_2012
ID: 41809785
awesome
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41809820
Yes group by rollup is really handy at times. cheers, Paul
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now