Query - used in Oracle APEX Chart displaying out of order

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?
LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
 
BILL CarlisleAPEX DeveloperAuthor Commented:
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
 
gplanaCommented:
try to change last line to:

order by year

Hope it helps. Regards.
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BILL CarlisleAPEX DeveloperAuthor Commented:
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
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Now I need to make it dynamic with the years being returned in the main query
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Great job Bill!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.