Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » generation of charts in apex
generation of charts in apex [message #462857] Mon, 28 June 2010 04:30 Go to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

CREATE TABLE "NEW"
( "COL1" VARCHAR2(4000),
"COL2" VARCHAR2(4000),
"COL3" VARCHAR2(4000),
);

insert into NEW values('a','1','met');
insert into NEW values('x','2','not met');
insert into NEW values('z','3','not met');
insert into NEW values('e','4','met');
insert into NEW values('r','5','not met');
insert into NEW values('t',6','met');
insert into NEW values('u',7','not met');
insert into NEW values('i','8','met');


hi

for the above table data i need to populate the graph by finding the percentage of the met and not met conditions.
please any body help me out how to write the query and where i have to implement it.
i need cluster bar verticle graph for mension met condition and not met saperatly in two bars..

thanks in advance
Re: generation of charts in apex [message #462871 is a reply to message #462857] Mon, 28 June 2010 05:57 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

please give your response
Re: generation of charts in apex [message #462872 is a reply to message #462857] Mon, 28 June 2010 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that the crucial step here is to create a query that will return a result you need. If I got it right, it doesn't seem to be that difficult. Sample data you provided suggests that both "met" and "not met" make 50% each (so I guess that it isn't a very good sample). Anyway: how would you calculate those percents? Did you already try to do that? What did you do? Post it here, please.
Re: generation of charts in apex [message #462958 is a reply to message #462872] Mon, 28 June 2010 21:59 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

thanks for your response.

that is the just a sample data which i have posted i have a set of more than 1000 rows of data where i need to calculate the percentage og met and not met conditions saperatly.

i tried with below code which gives me result normally,but it does't satisfy the chart syntax.

i dont know how to implement it in the graph.

select round(percent * 100/(select count(a) from NEW),2)from (select null link,COL2 lable,count(a) percent from NEW where COL3='met' group by COL2)

but the syntax of chart is
SELECT link, label, value FROM...

please help me

thanks in advance
sk
Re: generation of charts in apex [message #462959 is a reply to message #462958] Mon, 28 June 2010 22:00 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

please anybody help me soon..
waiting for your valuable responses.
Re: generation of charts in apex [message #462979 is a reply to message #462959] Tue, 29 June 2010 01:36 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
May be this can help you out.

regards,
Delna
Re: generation of charts in apex [message #463141 is a reply to message #462979] Tue, 29 June 2010 15:45 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As your sample data is both invalid (please, next time provide something that executes successfully, not rubbish we have to fix) and not descriptive, I modified them; in my example, there are 5 "met" and 3 "not met" records. It means that percentage is as follows:
met     : 5 / 8 * 100 = 62.5%
not met : 3 / 8 * 100 = 37.5%

A query that calculates those values is:
SQL> select met / cnt * 100 pct_met,
  2         not_met / cnt * 100 pct_not_met
  3  from (select sum(decode(col_met, 'met', 1, 0)) met,
  4               sum(decode(col_met, 'not met', 1, 0)) not_met,
  5               count(*) cnt
  6        from new
  7       );

   PCT_MET PCT_NOT_MET
---------- -----------
      62.5        37.5

SQL>

Now, Apex: create a "Chart" page. As every graph contains three (and only three) columns (link, label, value), we need to create two series - one for "met" and one for "not met" percentage. In order to do that, we only have to slightly modify the query presented above.

A query for "met" values is:
select null link,
       'met' label,
       met / cnt * 100 value
from (select sum(decode(col_met, 'met', 1, 0)) met,
             sum(decode(col_met, 'not met', 1, 0)) not_met,
             count(*) cnt
      from new
     )

Add another series, this time for "not met" percentage. I hope you'll manage to do that by yourself.

Finally, the result is

/forum/fa/7943/0/

I guess that's all.

  • Attachment: apex_met.PNG
    (Size: 9.69KB, Downloaded 2700 times)
Re: generation of charts in apex [message #463177 is a reply to message #463141] Wed, 30 June 2010 00:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi Littlefoot,
As shown in the graph, I think there are four series with values 62.5, 0, 0, 37.5.
You have added only two series, MET and NOT MET, then why the graph is showing 4?

regards,
Delna
Re: generation of charts in apex [message #463310 is a reply to message #463177] Wed, 30 June 2010 11:43 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that Chart works that way; it found two series, and - although the "other" value (when displaying "met", its "other" value is "not met" and vice versa) is zero, it is displayed anyway.

To test it, I created yet another series - a dummy one, simply selecting hardcoded value of 50. Changed chart type to 3D column and here's how it looks like:

/forum/fa/7953/0/

See? As many 0 (zeros) as there are series that, actually, don't exist.

OK, next step. Obviously, my query was poorly written. It *did* the job, but not the way one would want it to. Therefore, I have modified it so that it returns all we need in only one series. This is the query:
SQL> select label,
  2         pct
  3  from (select 'met' label,
  4               met / cnt * 100 pct
  5        from (select sum(decode(col_met, 'met', 1, 0)) met,
  6                     count(*) cnt
  7              from new
  8             )
  9        union all
 10        select 'not met' label,
 11               not_met / cnt * 100 pct
 12        from (select sum(decode(col_met, 'not met', 1, 0)) not_met,
 13                     count(*) cnt
 14              from new
 15             )
 16       );

LABEL          PCT
------- ----------
met           62.5
not met       37.5

SQL>
I fetched percentages from the UNION, and also created labels (which we will need in Apex). The same query (as above) is used in Apex, with a minor difference - selecting a "link" value:
select null link,
       label,
       pct
from (select 'met' label,
...

When the chart is done, the result is as follows:

/forum/fa/7954/0/

Much better than previously, wouldn't you say?


difference of time in minutes [message #463369 is a reply to message #462857] Thu, 01 July 2010 00:49 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

can we take the difference if two times which is stored in two saperate column like starting time and ending time.

please give me the sample example

i have declared the column as varchar2

[Updated on: Thu, 01 July 2010 00:57]

Report message to a moderator

Re: difference of time in minutes [message #463380 is a reply to message #463369] Thu, 01 July 2010 01:47 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Yes its possible.

Have a look at the date function


Examples

Regards
Ved

[Updated on: Thu, 01 July 2010 01:48]

Report message to a moderator

Re: difference of time in minutes [message #463386 is a reply to message #463369] Thu, 01 July 2010 02:13 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You can subtract one date from another which contains time as its part. This will return number of days between those two.

If you are storing those time values in VARCHAR2, then you have to convert then into date/timestamp first, before subtraction. After that you can convert that number of days into required time units like hour/min/sec.

regards,
Delna
Re: difference of time in minutes [message #463393 is a reply to message #463369] Thu, 01 July 2010 02:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
shobhakashyapgmailcom wrote on Thu, 01 July 2010 07:49
i have declared the column as varchar2

That is stupid. Why do you people do such things? Why would anyone want to store a date values into a character column? Now you can expect unexpected behaviour such as invalid months [dd.mm.yyyy] (25.14.2010) or letters in "dates" (2A.07.2010) or mixed formats (24.03.2010 vs. 03.24.2010) or awkward values (what does 01.02.03 mean? Is it 01 Feb 2003, is it 02 Jan 2003, is it ...).


By the way: what does this question have to do with the "chart" problem you initially had? Are they related? If not, please, open a new topic for every new problem - do not put all questions you have into a single topic.
Re: difference of time in minutes [message #463396 is a reply to message #463386] Thu, 01 July 2010 02:51 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member


col1 col2
5/31/2010 20:43 5/31/2010 20:59


if you dont maind can you tell me how can i achieve to get the difference og time,which is of varchar2 type..

am not able to make out
please
Re: difference of time in minutes [message #463400 is a reply to message #463396] Thu, 01 July 2010 03:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Delna explained what to do in a message #463393. Follow her advice.
Re: difference of time in minutes [message #463417 is a reply to message #463393] Thu, 01 July 2010 04:36 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

it is chart related with the chart issue.
my data have start date and end date with time.
i have a constant value that each process shuld end with particular time.if the codition satisfies then it is met else not met.

for that i need to take the difference of time and compare.

and my data is stored in the table by uploading csv file.while uploading i get error if i use non-varchar data type..for avoid the error i do use varchar2..
Re: difference of time in minutes [message #463418 is a reply to message #463417] Thu, 01 July 2010 04:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's wrong. You should have fixed the error cause, not produce a new problem instead. As you are still in the development process, I suggest you to redefine column datatype and store dates into a DATE column.
Re: difference of time in minutes [message #463564 is a reply to message #463418] Thu, 01 July 2010 21:56 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

am trying to rsolve my issue from long time,
if possible can you give me the code to upload the csv to DB table in apex..
i tried at max level.its of no use..so i used varchar2

[Updated on: Thu, 01 July 2010 21:56]

Report message to a moderator

Re: difference of time in minutes [message #463570 is a reply to message #463564] Fri, 02 July 2010 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When I have to load data into an Oracle database, I use SQL*Loader. I don't know what you did and how you tried to solve problems you met, but I'm sure that dates should be stored into a DATE datatype columns.
Re: difference of time in minutes [message #463571 is a reply to message #463570] Fri, 02 July 2010 00:27 Go to previous message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

i refered the below url steps and did my uploading part.am not aware of the sql loader.and i dont know my apex 2.2 version will support or not.and its implementation also..

http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/
Previous Topic: Setting multiple items based on a text field value without submitting
Next Topic: Apex 4.0 Installation On Oracle 11g Enterprise Edition Rel 2
Goto Forum:
  


Current Time: Thu Mar 28 07:09:51 CDT 2024