I need to modify my query so that it can give me a total(duration) and total(stlmntcharge) per day in april 2013 starting from the 1st till the 30th. At the moment my query looks like below:
SELECT sum(duration),sum(stlmntcharge) FROM voipcdr WHERE (calldate >= TO_DATE('20130401','YYYYMMDD') AND calldate <= TO_DATE('20130430','YYMMDD')) AND (remtrunkid IN (SELECT UNIQUE trunkid FROM trunks WHERE description LIKE '%Telkom%' AND gw_range_id = '61' AND trunkid like '9%'))
or remip in(SELECT UNIQUE startip FROM gateways WHERE rangename LIKE 'vo-za%' OR rangename LIKE 'PC-IS-VOIS%')
I want to get any employee name of deptno 10 but total count of number of employees under dept 10.
DECLARE l_deptno NUMBER:=10; l_count NUMBER; l_ename varchar2(20); BEGIN SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno; dbms_output.put_line(l_count||' '||l_ename); end;
I need the following totals for each student based on the schools that they are enrolled for each month July through June.
Total Days Suspended from each school
Attendance Table Columns - Absent Date, Absent Code, Pupil_Number, School (Absent Codes for suspended = 3)
Total Days Present at each school - Days Enrolled at each school minus Days Suspended minus Absences
Totals Takes the first day that a student is enrolled (counts the days that the school is in session and the student is not marked absent) Student must be enrolled during the time frame for that month.
Total Days Absent - Total days absent minus suspensions
Examples Student enrolls in school 11111 on 7/15/2011 Student withdraws from School 11111 on 11/15/2011 Student enrolls in school 22222 on 11/20/2011
Total Days Present should be totaled for each student for each month that they are in a school.
I need 1 row of data for the above student for each month that contains total days suspended, total days present and total days absent. Pupil Number School Month Totals Days Suspended Total Days Absent Total Days Present
1234 11111 07 0 0 0 1234 11111 08 0 0 4 (school days 4) 1234 11111 09 3 1 16 (schools days 20 - 3-1) 1234 11111 10 0 1 18 (school days 19 - 1) 1234 11111 11 1 1 7 (student enrolled at school A 9 days -1-1) 1234 22222 11 0 1 10 (student enrolled at school B 11 days -1) 1234 22222 12 1 2 17 (school B 20-1-2) 1234 22222 01 0 0 20
Is there a way I can generate a list of date using a simple SELECT statement to generate all dates between to date points? For example between SYSDATE AND SYSDATE-7?
One way I know is have a table and run PL/SQL script to put all dates in it and query that but was wondering if it can be achieve via SQL SELECT query at all without creating a table (querying DUAL).
I see that I can use SDO_LRS.SPLIT_GEOM_SEGMENT to split a line at a single point (and get 2 resulting lines).
However, how I could split a line, at multiple points, into multiple segments? I need to do this for many rows, therefore a function or procedure would be good if any exists.
Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....
why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?
I have loaded shape files with SQL developer Georaper and limited number of decimals to 8. There is option to limit number of decimals in georapter.But there are some X, Y with many decimals. This may create problem in spatial functions in which geometries are compared and filtered
how to control the decimal points of X,Y to be loaded in geometry column. -0.4695623500000000172249770002963487058878 51.47160360000000167701728059910237789154-0.4702653600000000211345252409955719485879 51.47174944999999723904693382792174816132-0.4713475400000000092859409051015973091125 51.47330664999999783049133839085698127747-0.4717886499999999760923685698799090459943
I have a requirement to fetch the nearest neighbor from a given point coordinates inside a point cloud/georaster data. how can i retrieve a collection of all neighboring points from a given point.
I guess i will have to use sdo_nn operator. how to use the operator to achieve the result.
I'm developing, or trying, on Apex 4.2...The issue is about themes and how to manage the positions of diferents Display Points of a theme.
Everybody know that any theme have a map where its position all differents parts of this map (Body1, Body2, Body3, position01, position02 and so). So when you choice any theme, it come with all this Display points defined. You can see this map rendering any region page and in the item Display Point you can see this map clicking on the light.
There is anyway to can manage this map and change position or behavior of those points?. For example , I'm interested that under the tabs, to have at the same height, 3 diferentes position, one at left, one at center and other at right.
My example: I'm given an Allowance throughout the week. It happens to be 10 dollars but it can vary from day to day.I can create a running total with SUM(Amt) Over etc...This is the CUMUL column in the example below.
On certain days I've spent different percentages of the allowance. (The SPENT Column which is a field in the database)I can't manage to create the AMTLEFT column in the example below.The AmtLeft column seems to be a kind of running total that 'refers to itself' so this is where I'm stumped.
I'm trying to Rank Username based on the Total Sum of amount waived but I want to avoid Ranking the Overall Total at the bottom, plus I dont want them in Ranking order, I want the order to stay the same as it currently is.
SELECT DECODE(GROUPING(USERNAME),1,'TOTAL',0,UPPER(USERNAME)) as "USERNAME", SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'JAN' THEN AMOUNT_WAIVED ELSE 0 END) AS JAN, SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'FEB' THEN AMOUNT_WAIVED ELSE 0 END) AS FEB,
I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".
Currently I am doing this by submitting a second query:
select count(*) from (<previous query criteria>)
Is there a better performing approach I could be using?
I am writing a report that breaks on the first 4 fields. That part is working fine. I also want a count for each Group (the 4 fields), and a grand total. Since I want to break on all 4 fields as if they are one combined field, I made a concatenated column (called Break_key) and had the report total on that.
I was surprised when the count appeared at the top of each group, rather than at the bottom. The grand total is at the very bottom of the report, as I would have thought. How can I get the sub-totals at the bottom, rather than the top?
Is there any data dictionary table to get the object grants for total objects in the database?
Using DBA_TAB_PRIVS, i get object grants for tables only. But i'm looking for remaining objects also. The list of remaining objects is below.
view, or materialized view Sequence Procedure, function, or package User-defined type Synonym for any of the preceding items Directory, library, operator, or indextype Java source, class, or resource
provide me the other data dictionary tables for querying.
I am displaying customers names and there inventory prices(inv_price * ol_quantity) however I need a total sum of all customer prices at the bottom
ex name1 | 50 name2 | 25 total amount | $75
DECLARE CURSOR abc IS SELECT customer.c_last, SUM(inventory.inv_price * order_line.ol_quantity) AS Total, inventory.inv_price, order_line.ol_quantity FROM customer, orders, order_line, inventory WHERE customer.c_id = orders.c_id AND
I'm on 11.2.0.3. I want to write a query to get calculate a running total of incidents per day - this query will be used for an APEX line chart.Sample table and data:
create table sales ( id number primary key, time_of_sale date, item varchar2(20)); insert into sales values (1, to_date('02-JAN-2013','DD-MON-YYYY'), 'book'); ....
I am trying to use rollup for a total, but it doesn't work because what I'm totaling is too conditional. I even tried adding on the totals to the end using a union and a specialized function, but for some reason, the order by stops working when I do the union (all field aliases and types are the same).I know the structure of the query itself probably isn't the best, how I can get the grand total, The subtotals from the rollup work fine, though.
The order by query runs fine against both parts of the union when they are run individually, but once I make it part of the union - the first part of the order by is an unknown field and the second is not a SELECTed list expression. I've commented out the union here. The statement works fine without it except for the grand total being all 0s. I kind of understand why it's all 0s, but not how to get around that.This is running in Oracle 10g2. I am trying to get totals for decisions by each of the four component values: ex_land, ex_imp,tx_land,tx_imp and then subtotals for each component values.
That works for the following query, but I cannot get a grand total through the rollup.
Now, i want to count quantity follow slip_no and merchandise.
Example: Merchandise :2501B002CA have 8 slip with quantity is 12 and 5 slip with quantity is 16. Merchandise: 2501B001CA have 6 slip with quantity is 12 and 7 slip with quantity is 16.
I have 2 blocks named emp,emp1 those are DB's Blocks and empno, ename, sal in EMP block and sal, sum(sal) are in EMP1 block, i set the property for sum is summarized block is: EMP1 and item is SAL, i want display sum(sal), but not display when i click execute query.