Solved

Oracle queries --Challenging Question

Posted on 2016-09-19
12
62 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Such a result is produced by an option in the group by clause:

GROUP BY ROLLUP
0
 

Author Comment

by:sam_2012
Comment Utility
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
Comment Utility
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
Comment Utility
awesome
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.

Join & Write a Comment

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 …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

744 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

8 Experts available now in Live!

Get 1:1 Help Now