Solved

Query - used in Oracle APEX Chart displaying out of order

Posted on 2014-01-29
6
558 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
[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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

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.  …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

707 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