Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

Query - used in Oracle APEX Chart displaying out of order

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?
  • 5
1 Solution
try to change last line to:

order by year

Hope it helps. Regards.
bcarlisAuthor 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

bcarlisAuthor Commented:
I added this in the select and it worked. The need is to have a row from each year being returned.

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

Open in new window

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

bcarlisAuthor Commented:
Now I need to make it dynamic with the years being returned in the main query
bcarlisAuthor Commented:
Got it..  Any better way?

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

bcarlisAuthor Commented:
Great job Bill!

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now