Solved

Query - used in Oracle APEX Chart displaying out of order

Posted on 2014-01-29
6
543 Views
Last Modified: 2014-02-26
Hi,
I have chart that uses a query grouped to sum the revenue for each year and market segment.
The chart works great but if the first and second segment doesn't exist for 2010 but it does in 2011, and 2012 then the graph shows 2011, 2012, 2010 order

select 'f?p=&APP_ID.:3:'||:app_session||'::::P3_YEAR,P3_SEGMENT:'||to_char(to_date(year,'YY'),'YYYY')||','||market_segment||':'  LINK,
       to_char(to_date(year,'YY'),'YYYY') year,
  case when market_segment = 'Technology'     then sum(revenue) end as  "Technology",
  case when market_segment = 'Energy'         then sum(revenue) end as  "Energy",
  case when market_segment = 'Infrastructure' then sum(revenue) end as  "Infrastructure",
  case when market_segment = 'Chemicals'      then sum(revenue) end as  "Chemicals"
    from apps.xxapex_incoming_ord_5yrs 
where org_id = :G_EMP_OID
group by market_segment, to_char(to_date(year,'YY'),'YYYY')
order by to_char(to_date(year,'YY'),'YYYY')

Open in new window


how would I write the query to maybe have a row for each segment, each year though may be zero?
0
Comment
Question by:bcarlis
  • 5
6 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39818378
try to change last line to:

order by year

Hope it helps. Regards.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39820891
same results... as long as it returns only one row for 2010, the first value null, it doesn't show it in the graph until it gets to the value that is not null.

YEAR 	Advanced Technology	Energy and Environmental	High Tech Infrastructure	Life Science and Chemicals
2010	 -	 -	 -	1356.1
2011	340	 -	 -	 -
2011	 -	 -	 -	0795.69
2012	8473.86	 -	 -	 -
2012	 -	230	 -	 -
2012	 -	 -	8802.6	 -
2012	 -	 -	 -	67031.66
2013	45915.3	 -	 -	 -
2013	 -	400	 -	 -
2013	 -	 -	938.56

Open in new window


out-of-order.pdf
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39820935
I added this in the select and it worked. The need is to have a row from each year being returned.

union
select '10' year, 'Technology ' market_segment, 0 revenue
from dual

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 2

Author Comment

by:bcarlis
ID: 39820950
Now I need to make it dynamic with the years being returned in the main query
0
 
LVL 2

Accepted Solution

by:
bcarlis earned 0 total points
ID: 39821176
Got it..  Any better way?

union
select distinct xyears.year, xmrkts.market_segment, 0 revenue
  from apps.xxapex_incoming_ord_5yrs xyears, apps.xxapex_incoming_ord_5yrs xmrkts
	where xyears.org_id = xmrkts.org_id 
	and xmrkts.org_id = :G_EMP_OID
	order by 1

Open in new window

0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 39888272
Great job Bill!
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

Suggested Solutions

Title # Comments Views Activity
Oracle and DateTime math 6 25
Problems using Provider=OraOLEDB.Oracle via VBScript/Classic ASP 5 45
Substring() and LEFT() syntax 4 21
selective queries 7 21
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

912 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

16 Experts available now in Live!

Get 1:1 Help Now