Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle queries --Challenging Question

Posted on 2016-09-19
12
Medium Priority
?
95 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 25

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 74

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 49

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 53

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 38

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
 

Author Comment

by:sam_2012
ID: 41807078
Hi Sdstuber,

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

Accepted Solution

by:
PortletPaul earned 2000 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 49

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 49

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

719 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