Solved

Query - used in Oracle APEX Chart displaying out of order

Posted on 2014-01-29
6
536 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
Comment Utility
try to change last line to:

order by year

Hope it helps. Regards.
0
 
LVL 2

Author Comment

by:bcarlis
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Author Comment

by:bcarlis
Comment Utility
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
Comment Utility
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
Comment Utility
Great job Bill!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 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

14 Experts available now in Live!

Get 1:1 Help Now