Solved

Oracle queries --Challenging Question

Posted on 2016-09-19
12
86 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 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 52

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
 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

710 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