SQL & PL/SQL :: Create Result Set Without Date Gaps For Line Chart
Jun 14, 2012
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
[code]....
Requirements for the result set:
1. List the root division as column headers
2. Start date is March 15, 2012
3. End date is June 30, 2012
4. Do not skip or omit dates
5. The values under each column are percentages where completed = 'Y'
5a. Example, D5_Total = 277, D5_Completed(Y) = 254, D5_Percent(Y) = 254/277 = 92%
5b. Rounded to whole percentage like 92% and NOT 91.696%
The result set will be used to create a line graph. The graph will show which root division is closer to reaching 100% before the deadline of June 30, 2012.
I have a date field that should be filled everyday with today's date and I need to get the days that were not entered.
i.e. :
CREATE TABLE TRY_F (DAT DATE);
INSERT ALL INTO TRY_F VALUES (to_date('01/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('02/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('04/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('05/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('06/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('08/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('10/01/2011','DD/MM/YYYY')) INTO TRY_F VALUES (to_date('14/01/2011','DD/MM/YYYY')) SELECT * FROM DUAL;
I need a smart way of getting the dates that were missed in DAT.
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME ------------------------------------------------ STATUS ------------------------------------ BUP OPEN
Another database has following characterset settings:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME STATUS ---------------- ------------------------------------ HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
In my Forms application, I must create an Excel sheet with data displayed by a pie chart, i.e. something like this
[URL]......
I'm able to create the Excel sheet by using OLE2 tool, but I dont know how to create and insert the pie chart. Searching in this forum, I found this post:
[URL].....
it's useful, but somewhat hard to understand. In detail, how can I set the form_charttype and form_chartsource so that it would produce my pie chart?
I am trying to retrieve all rows with the date of May 23rd, 2008. Our data is stored with the date and time.
How do I format the where clause so that the time is taken off? I am still a newbie with oracle and what I have is this:
Select Distinct Table1.*, Table2.* from Table1, Table2 where Table1.file_name = Table2.file_name and Table1.downloaded_date = Table2.date_loaded and downloaded_date = '23/May/2008'
Of course, it's the last line not working because I don't know how to format it and no rows are retrieved when there are definately May 23rd rows stored in there.
The way to create a short cut on desktop to have an sql*plus (command line).
i.e. I want to have an short cut on desktop when I click that short cut that would connect me to a specific database with a username and password (which i supply while creating the shortcut-I would provide the database name, username and password)
I need to load data from a CSV file where one of the CSV values determines how many records should be inserted.
Example of the input data:
KEYWORD;2;REC1_COL1_X,REC1_COL2_X;REC2_COL1_X;REC2_COL2_X KEYWORD;3;REC1_COL1_Y;REC1_COL2_Y,REC2_COL1_Y;REC2_COL2_Y;REC3_COL1_Y;REC3_COL2_Y KEYWORD;4;REC1_COL1_Z;REC1_COL2_Z,REC2_COL1_Z;REC2_COL2_Z;REC3_COL1_Z;REC3_COL2_Z,REC4_COL1_Z;REC4_COL2_Z If the KEYWORD is found, then the next value determines how many value pairs will follow, and therefore how many rows should be created in the affected DB table.
I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.
My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).So I could easily drop old data by dropping old partitions and tablespaces.
Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.The problem of this idea is that i have to update billions of rows.
It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.Is there any way to "link" this information to a column in MESSAGE table?
I mean something like this:
alter table MESSAGE add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;
Is there some possibility like that? Would the partitioning on this column / statement work?Would the value of the keep_message be calculated on runtime?
If so will the performance influence be noticeable?If so will the performance also sink if the application is querying all rows except the keep_message?
We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:
Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).
CREATE TABLE DAN_DATES (ID VARCHAR2(12), YEAR VARCHAR2(, TERM VARCHAR2(, START_DATE VARCHAR2(12))
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD')); INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD')); INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD')); INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD')); INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));
Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.
I want to get IDYEARTERMSTART_DATEMIN_DATE 12012120127-Feb-1227-Feb-12 12012120126-Jun-1227-Feb-12 22011110126-Feb-1126-Feb-11 22011110125-Jul-1126-Feb-11 22011110126-Sep-1126-Feb-11 22012120227-Feb-1227-Feb-12
I have a 2D pie chart. The chart takes the column names as label and shows the results(3) like passed--4, failed--3, on hold ---6. Now I want to have tooltip , if I point on passed--4, it should show "number of students passed last year"similarlyIf I point on failed--3, it should show "number of students failed last year"
Whenever you're trying to create a table instance chart, and if theres 3 primary keys(composite keys). Do u put in the table instance chart as PK1, PK2, PK3 for the 3 primary keys?
And also if theres 2 foreign keys in the table, do u put in as FK1, FK2 on their key types?
I want Hierarical query to display my Chart_Of_Account. I want to make a tree Form in 6i error i am getting is connect by nocycle prior account_code=parent_code * ERROR at line 4: ORA-00920: invalid relational operator
Table
Create Table Chart_Of_Account (Account_Code Char(19), Account_Title varchar2(70), Parent_Code Char(19)) insert into Chart_Of_Account ('DGHOA01010101000001','TEST','DGHOA01010100000000') insert into Chart_Of_Account ('DGHOA01010101000002','TEST1','DGHOA01010100000000') insert into Chart_Of_Account ('DGHOA01010101000003','TEST2','DGHOA01010100000000')
select -1,level,account_code||' - '||ACCOUNT_Title,'NULL',to_char(ACCOUNT_CODE) connect_by_iscycle from chart_of_account start with Substr(account_code,13,7)='0000000' connect by nocycle prior account_code=parent_code
i need a chart with 4 lines and 2 y axis. 2 lines on the first y axis, 2 lines on the other y axis.First i tried to create 4 series and "multiple y axis", then i get the first line at one y axis, and all the other lines at the second y axis.
After that i have seen that it is possible to return more than one value within one series, so i create 2 series with 2 values each - i thought that then i will get the first 2 values at one y axis and the other 2 lines of the second serie at the second y axis... but sadly it is not like i expected - i get the same result as with 4 series.is it really necessary to create a custom xml for that?
here is my example: [URL] as you can see the blue and the red line are on different axis.the query of one serie looks like that (it gives the values of a day and the day before), the queries are ok.
select null link, to_char(a.ts,'DD.MM HH24:MI') label, sum(a.kw*100/12) over ( partition by trunc(a.ts) order by a.ts rows between unbounded preceding and current row ) "KWh", sum(b.kw*100/12) over ( partition by trunc(b.ts) order by b.ts rows between
select null, '0-10' Days , count(*) from tableunionselect null, '11-20' Days, count(*) from tableunionselect null, '21-30' Days, count(*) from tableunionselect null, '>30' Days, count(*) from table
The 3d pie chart was showing the slices is the correct order 0-10, 11-20, 21-30, >30 but now shows it in random order. how to get the order by working. I suspect since the Days column in the select is a Character column this issue is occurring, however, with the restrictions in the number of select items we can have in the Apex charts, was wondering how do you get the order by working?
I have two graph pj item in oracle block one is pie chart and other is vertical bar graph... there is color display in bottom of pie graph which is red=50,yellow-30 and orange=20 ..and in vertical bar red=50,but orange=30 and yellow is 20 ..means orange and yellow is interchange in bar graph..how i can same the both graph value..
the graph is populate through cursor and graph properties is setting through set_custom_propert();
I am using the build-in flash Apex chart which I understand to be a subset of the Anychart.
I have the following problem, I have two series, which I am displaying in a 3D Bar Chart on a horizontal view with two Y-Axis. This works well, the problem is that each Y -Axis has a different scale. I am not setting the min or max value range for either of the y-axis as I do not know what this value range may be at the time of rendering the chart to the application user.
So as it turns out, the left y-axis has a value range from zero to 100 and the right y-axis has a value range from zero to 50 and the application users are finding this difficult and confusing as they are only looking at the bars represented in the chart and assuming that as the two bars, i.e. one form each series look similar in size, they have the same value and this can not be further from the true, as one y-axis is showing a value range twice as big as the other y-axis.
So my question is how do you customise the chart so that the value range on both y-axis is the same. i.e., the largest of the two?
