SQL & PL/SQL :: Cross Tab Group By Period?
Apr 27, 2011
I'm trying to write a cross tab query but which groups by periods.
Test Case: -
CREATE TABLE COMPANIES
(
"ID" NUMBER(9,0),
"CODE" NUMBER(8,0),
"DESCRIPTION" VARCHAR2(40 CHAR),
CONSTRAINT "PK_COMPANIES" PRIMARY KEY ("ID")
);
[Code]..
The cross tab is to have columns 1 - 31 which sum the quantity of the article sold. Represnting days of the month.
In addition the description of the company.
I would then like to end up with 3 entries in the results for each company, but these are for summing the quantity of articles by the following periods: -
Breakfast: 01:00 - 10:00
Lunch: 11:00 - 14:00
Dinner: 17:00 - 22:30
I hope that makes sense?
View 13 Replies
ADVERTISEMENT
Aug 16, 2011
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
View 13 Replies
View Related
Sep 14, 2011
Following is the scenario:
CREATE TABLE EMP_VACATION(
EMP_NO NUMBER(3),
VAC_TYPE NUMBER(1),
START_DATE DATE,
END_DATE DATE,
START_PERIOD_DATE DATE,
END_PERIOD_DATE DATE,
PRIMARY KEY (EMP_NO,VAC_TYPE,START_DATE))
[code]....
--How to calculate START_PERIOD_DATE and END_PERIOD_DATE for every time insert reocrd, like for 4 cases?
--I need to check start_date with last period date for same employee.
And based upon calculate START_PERIOD_DATE and END_PERIOD_DATE,,,,,
View 7 Replies
View Related
Jun 7, 2012
i want to find the data which has the more then one rates available for the given date. Here below i've posted a test table. how can find the products for which, there multiple dates/overlapping period is available through sql statement.
SQL> CREATE TABLE STO.TEMP
2 (
3 ID NUMBER(8),
4 ITEM_ID NUMBER(8),
5 RATE NUMBER(5,2),
6 FROMDATE DATE,
7 TODATE DATE
8 );
[code]....
View 8 Replies
View Related
Jun 28, 2012
CREATE TABLE CONTRACT
(
EQT VARCHAR2(10 BYTE),
SUBTYP VARCHAR2(3 BYTE),
FROM_DT DATE,
TO_DT DATE
);
[Code]....
The data in contract and lease table show like below.
contract
EQT SUBTYPFROM TO
ABCD100000DRY1/1/20121/7/2012
ABCD100000DCH1/7/20121/21/2012
ABCD100000GAZ1/21/2012
lease
EQT CONTRACTFROM TO
ABCD100000LS11/2/20121/10/2012
ABCD100000LS21/10/20121/17/2012
ABCD100000LS31/17/2012
Now required output should be
EQT SUBTYP CONTRACTFROM TO
ABCD100000DRY 1/1/20121/2/2012
ABCD100000DRYLS11/2/20121/7/2012
ABCD100000DCHLS11/7/20121/10/2012
ABCD100000DCHLS21/10/20121/17/2012
ABCD100000DCHLS31/17/20121/21/2012
ABCD100000GAZLS31/21/2012
The period for contracts should be in sequential order and should not overlap and if contract does not exist for that date period, then it should return NULL.
View 9 Replies
View Related
Dec 14, 2011
I need to divide the given time period into weeks from Monday to Sunday .There should not be overlapping of two months, for a week.Every month should start from First day of that month to next Sunday .Same thing can be done by following PL/SQL block . let me know if there is any simple way by using query instead of block .
declare
pid_from_date date := '01-JAN-11';
pid_to_date date := '31-dec-11';
ln_number number := 0;
ld_from_date date;
ld_to_date date;
begin
[Code]....
View 5 Replies
View Related
Aug 12, 2010
I need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:
Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645
View 12 Replies
View Related
Sep 16, 2013
"ALTER PROFILE profile_name LIMITPASSWORD_LOCK_TIME 1"means the user account will be locked in a period of one day after FAILED_LOGIN_ATTEMPTS has gone over limit.How to set PASSWORD_LOCK_TIME less or more than one day (like not full days)?
View 2 Replies
View Related
Apr 10, 2012
I have TableA which has data like below.
ID NAME CRT_DTE
1 AB 03/05/1992
2 EF 15/04/1995
3 CD 20/08/1995
4 GH 01/01/1999
5 UV 08/07/2001
[code]....
I want a query which splits the total time period (from min crt_dte to max crt_dte) into year ranges.For eg, lets say a range of 5 years then I need to get results like below.
start_dte end_dte
---------- ----------
03/05/1992 03/05/1997
03/05/1997 03/05/2002
03/05/2002 03/05/2007
03/05/2007 09/03/2012
how to write this query.
View 6 Replies
View Related
Aug 28, 2007
I need to create a report that show the overall performance of the system in hourly basic. I need to check all the transaction table in the system. So I try to find Cross Tab query. I need display hourly basic in column. From 00 hour to 23 hour.
In my table i will have a sysdate that track all the record DD/MM/YYYY HH24:Mi, so now i need so split it to a hourly in column. This is how it will look like. Think show in picture is easier.
Each row actually retrieve from a table. So basically i need to count each table, on that day got how many transactions then split all to a hourly basic and display it.
View 5 Replies
View Related
Apr 17, 2013
In my production environment, I have a mostly working Oracle 12c Cloud Control environment, managing several database instances. On all of the databases, I am unable to use the Compare period ADDM feature ( Instance > Performance > AWR > Compare Period ADDM).
When I select that menu option, I see the message "To be able to use this feature some PL/SQL packages need to be loaded into the target database's monitoring schema, DBSNMP." I have been searching for further information in the specific packages that need to be loaded per the message, but neither Orafaq, [URL] nor Google seem to have those details.
View 5 Replies
View Related
Apr 21, 2011
I am trying to break down a row of data that has nine periods that I want to enter into a table one period at a time. I am trying to use "IF EXISTS ( SELECT * FROM TABLENAME WHERE FIELD1 IS NOT NULL) THEN" through all nine fields. It keeps returning lines that have a null column as well as not null columns. if there is a better way of doing this. Here is an example of what I am trying to do:
IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT9 IS NOT NULL) THEN
SELECT NUMBER, 1COL9, 2COL9, 3COL9, FIRST_DT9, LAST_DT9, 4COL9
FROM TABLE_NAME;
ELSE IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT8 IS NOT NULL) THEN
[code]...
View 1 Replies
View Related
Aug 29, 2013
We have a materialized view showing red cross in oralce sql developer. But it functions well, we can select on it, no errors.
I searched on web, and some tells if underlying table changed, it may be broken. and need to refresh.
So I think I need to refresh it, using alter materialized view ... compile, but it told me the table or view doesnot exist, but it exists.
View 21 Replies
View Related
Mar 13, 2013
I am working in all 3 environments: development , Test and Production. How can we access the tables created in one environment in the other environment. Also, request to share the SQL code for the same.
I am using Oracle Sql Developer tool.
View 4 Replies
View Related
Aug 4, 2011
In my present env, Oracle runs in Solaris 10 and I am planning to restore it to Linux5. I have read thro oracle docs, metalink that cross platform restore with different endian can be done by TTS. But it says that tablespace shud be in read-only until we plug in it in the dest server.
Also My db size will be 800-1000GB
View 3 Replies
View Related
Aug 22, 2013
Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
create table try1
(id_number varchar2(10),
item1 varchar2(10),
item2 number(10),
item3 varchar2(10))
[code]....
Table's data:
ID_NUMBERITEM1ITEM2ITEM3ROWID
1asasas12dadasdaAAA9/BAAOAAA0JtAAA
22dadad231fsfsfAAA9/BAAOAAA0JtAAB
View 7 Replies
View Related
Mar 21, 2012
I have a employee table with the below format
EMP
EMPID DEPID
1A
2A
2B
1B
4A
5B
5A
6C
7A
8D
Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.
I need the output as below (many to many I.e. cross referencing)
EMPID DEPIDGroup
1A A1
2A A1
2B A1
1B A1
4A A1
5B A1
5A A1
6C B1 -- new group as the emp and dept are not mapped previously
7A A1
8D C1
View 1 Replies
View Related
Jul 10, 2013
how to display data in tabular format but the printing direction is down/cross? I managed to change set printing direction but the column header is not showing on the cross section.
I expect:
Header1 Header 2 Header1 Header2
John India Max Singapore
But it displays without header name on the next part.
Header1 Header 2
John India Max Singapore
And also is that possible for matrix query to be printed in down/cross direction like above?
View 1 Replies
View Related
Sep 16, 2011
I am essentially trying to reproduce an extremely complex spread-sheet with an engineering mathematical model in it and that has been in use for 5 years now. The Excel spreadsheet was a good environment for tuning the model, but now I have been asked to design an Oracle Form that incorporates their mathematical model and shows their final and intermediate data so they can validate my app against their proven spreadsheet.
To do that I have to create a matrix style table for use as a child data-block in my Oracle Form.
View 4 Replies
View Related
May 7, 2011
We got a request from Customer to migrate a RAC database of size 1.8TB from HP unix raw file system to AIX ASM with a min downtime. I have seen lot of methods of doing cross plat form migration and non-ASM to ASM but not together.
Do we have any proven method for migration such cross platform migration with raw file system to ASM conversion in a single go with min downtime?
View 1 Replies
View Related
Nov 15, 2010
I have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
View 3 Replies
View Related
Nov 1, 2013
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
View 4 Replies
View Related
Nov 18, 2011
Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.
View 4 Replies
View Related
May 31, 2013
I have a editing database with an eversions table:
NAME OWNER
---------------------- --------------------------------
WR5936_DN6676 FRED
WR6739_DN7507 FRED
WR12744_DN13994 FRED
WR6739_DN7511 BARNEY
WR6801_DN7513 BARNEY
I have a process database with a pversions table:
SOID
----------------
5936
6739
12744
6739
I need to select from the editing.eversions table all the records that do not have a matching record in process.pversion. The eversion table is text, and has some additional crap surrounding the characters I want to use for creating a join.
View 3 Replies
View Related
Aug 30, 2012
How to update a form fields which taking a searched value from another table .
I have a form that records student data and which has a drop down to select the 'student registration No' that comes from different table.
When insert or update a student record, first we select the student registration No from drop down to see if he is an existing student.
If he is already registered student then relevant student name and other details should be shown in the form and should be able to enter few other details as well.
View 3 Replies
View Related
May 22, 2013
Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 (x86_64)
I was practicing on Recursive Subquery Factoring based on oracle examples available in the documentation URL....I was working on an example which prints the hierarchy of each manager with his/her related employees. Here is how I proceed.
WITH tmptab(empId, mgrId, lvl) AS
(
SELECT employee_id, manager_id, 0 lvl
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees, tmptab
WHERE (manager_id = empId)
[code]....
107 rows selected.
SQL> However, by accident, I noticed that if instead of putting a comma between the table names I put CROSS JOIN, the very same query behaves differently.That is, if instead of writing
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees, tmptab
WHERE (manager_id = empId)I write
. . .
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees CROSS JOIN tmptab
WHERE (manager_id = empId)I get the following error message
ERROR at line 4: ORA-32044: cycle detected while executing recursive WITH query
I remember, oracle supports both comme notation and CROSS JOIN for Cartesian product (= cross product). For example
SQL> WITH tmptab1 AS
2 (
3 SELECT 'a1' AS colval FROM DUAL UNION ALL
4 SELECT 'a2' AS colval FROM DUAL UNION ALL
5 SELECT 'a3' AS colval FROM DUAL
6 ),
[code]....
SQL> So if both comma notated and CROSS JOIN have the same semantic, why I get a cycle for the above mentioned recursive subquery factoring whereas the very same query works pretty well with comma between the table names instead of CROSS JOIN? Because if a cycle is detected (ancestor = current element) this means that the product with CROSS JOIN notation is generating some duplicates which are absent in the result of the comma notated Cartesian product.
View 9 Replies
View Related
Jun 23, 2011
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
View 16 Replies
View Related
May 17, 2011
Refer to the txt file to create table and insert data.
I executed the following query-
SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail
and got the following result-
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1
Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1
View 3 Replies
View Related
Jun 26, 2013
I'm planning to upgrade a small database (~150GB) from 10.2.0.3 on windows 2003 23bit to 11.2.0.3 RAC on Linux 5.8.The database contains oracle spatial too. A suitable method and link to document to be followed.
View 2 Replies
View Related
May 9, 2011
Is there any way to add one disk group space to another disk group. Because One of my disk group is full i want to add space in to that group.
View 1 Replies
View Related
