SQL & PL/SQL :: Query For Two Groupings On Same Table
Apr 30, 2010
I've a table, with fields - KEY1, KEY2, EVENT_DATE, AMOUNT. I want to sum up the amounts for a given period in time (eg. sum up amount for last 10 weeks).
select KEY1, KEY2, sum(AMOUNT) K1K2SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1, KEY2;
Now, I want to same sum over KEY1 only.
select KEY1, sum(AMOUNT) K1SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1;
Now, I want to supplement the first table with an extra column having K1SUM.
select O1.KEY1, O2.KEY2, K1K2SUM, K1SUM from
(select KEY1, KEY2, sum(AMOUNT) K1K2SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1, KEY2) O1,
(select KEY1, sum(AMOUNT) K1SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1) O2
where O1.KEY1 = O2.KEY1;
Doing two scans of the table is a little costly for me. Is there a single pass solution? I got a partial answer using,
select KEY1,KEY2,sum(AMOUNT) SUMS from table1
where EVENT_DATE>sysdate-70 group by
grouping sets (KEY1, (KEY1,KEY2));
View 2 Replies
ADVERTISEMENT
Jun 19, 2012
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
View 4 Replies
View Related
Jan 23, 2013
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
View 5 Replies
View Related
Apr 22, 2009
How to use a table type variable with a regular query? For example, in my PL/SQL proc I have a table of employee names.
v_emp_tbl; --contains 'John','Sally','Ted'
Then I want to get names in a table called tbl_employees which are not in the pl/sql table. So if the table contains 'John','Sally','Ted',and 'Don' then I want to see 'Don'.
Not sure how to do this. Essentially I want something like:
Select emp_name into v_single_emp
from tbl_employees where emp_name NOT IN v_emp_tbl;
I know I can loop through the pl/sql table but I wanted to see if there is some other way.
View 2 Replies
View Related
Jul 22, 2010
I have one table which has 90 columns all has varchar2 datatype except one Column[primary key (Number)]. In this Table we have 1000 records, I want to fetch those records from Table which has value in all 90 columns means there is no null value in any column.
I know simple method Like this :-
column_name1 IS NOT NULL AND Column_name2 IS NOT NULL.
Like this we can write IS NOT NULL condition for all column.Is there any other way to write this Query because it makes Query very longer and it is very tedious job to write this Condition for all Columns.
View 3 Replies
View Related
Apr 12, 2012
I do a query like this one:
Select value (tab1).worker
from workdescription_Tab wdt, table(wdt.worksOn) tab1
Result is a column - each line includes a ref table with different number of entries.
If I could select this column directly, i would print out the entries of each ref table like this:
Select j.job, cursor
( select (w).lastname
from table (j.workers) w)
from job_tab j;
or
Select j.job, value(w).lastname
from job_tab j, table(j.workers) w;
But how can i do this with the query result above? I am not able to manage it...
View 2 Replies
View Related
May 5, 2013
I have the following table structure...............
Main_Head table name
main_head_id ,pk
head_desc,
head_id ,
sub_head_id
keys
col table ref col
sub_head_id main_head head_id
the table is recursive table self join
-----------------------------------------
now i want to write the query which return all head_desc which have same head_id
View 12 Replies
View Related
Nov 10, 2010
I have the following 2 tables.
A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);
[Code]..
Join column : object_id and parent_id.
The object id in docversion will have multiple values for element value in listofstring table.
ordinal value represents teh sequence of element value
Eg:
data for docversion:
1 23
2 34
data for LISTOFSTRING:
1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B
ouptut:
I want the output as follows
docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B
View 14 Replies
View Related
May 25, 2010
I have the following issue i have two table
PRODUCT (id, product_name)
1, prod1
1, prod11
2, prod2
3, prod3
OSS(id, oss_name)
1, oss1
2, oss2
what i want to return is the product_name and the oss_name for each id.
1, prod1, oss1
1, prod11,
2, prod2, oss2
3, prod3,
View 13 Replies
View Related
Mar 10, 2011
Is there a way to retrieve information about a table through an sql query? In particular I need to retrieve the size of some varchar columns.
View 3 Replies
View Related
Feb 29, 2012
I am using Oracle SQL developer 3.0.0.4. I have tables starting with @. For eg @45412_USERS
I am getting the below error when i run select * from @45412_USERS;
It says ORA-00903: invalid table name
00903. 00000 - "invalid table name"
*Cause:
*Action:
Error at Line: 7 Column: 14
I also tried with select * from <Owner>.@45412_USERS this also didnt work.
View 4 Replies
View Related
Jun 7, 2012
How to find display the o/p like manager name under dependent employess same like parent child relation ship on noraml emp table:
sample o/p:
name job
xx manger
yy sales
yy1 sales
aa manager
rr marketing
rr1 marketing
View 6 Replies
View Related
Sep 13, 2012
I have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
View 2 Replies
View Related
Jul 22, 2013
I am fetching a value in a variable as:
<select application_short_name into l_appl_nm from fnd_application where application _id=:p_appl_id>
Now I need to use the value fetched in variable "l_appl_nm" as a table partition name in next query.
View 8 Replies
View Related
Sep 13, 2004
Can we create a table from a Select query ?
View 5 Replies
View Related
Apr 4, 2008
query to know number of columns in a table i.e.
if I want to know how many number of colums are present in a specific table then what would be the query.
View 1 Replies
View Related
Aug 18, 2012
I�m Using Oracle 11.I have a table with 16 million rows and an index (let's call it the employee table with an index on department). I need to select all the employees whose departments are located in the uk. I achieve this by selecting all the department numbers from departments where location = 'UK' in a sub select then plug this into the main query as follows:
SELECT *
FROM employees
WHERE department IN (SELECT department from departments where location = 'UK');
It takes ages, 25 seconds or more, the explain plan shows its doing a full table scan on emplyees. I need it to use the index. The sub query is instant and returns only 5 rows. If I explicitly put the 5 numbers in the IN clause the query uses the index and executes in 0.04 seconds. See below:
SELECT *
FROM employees
WHERE department IN (1,2,3,4,5);
I need it to use the subquery once and then use the index on the main table.
View 2 Replies
View Related
Mar 22, 2013
I have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).
my question is Ihave one package in that am trying to insert into this table as follows:
INSERT INTO A
SELECT b.name name,
a.address address,
[Code]....
so the query works on Prod but fails on Dev because column order is different.
I have 2 solutions:
1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.
so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.
View 5 Replies
View Related
Jan 16, 2013
i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:
EMP
EMP_1
EMP_2
EMP_3
EMP_4
i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.
View 10 Replies
View Related
Oct 15, 2012
I have table xx_xml_test m which have row single entry
<logentry
revision="3">
<author>MA111300</author>
<date>2012-10-03T12:42:40</date>
<paths>
<path
[code].......
i want convert the table like below
Revision author date kind action path
3 MA111300 2012-10-03 12:42:40 file A /root.txt
3 MA111300 2012-10-03 12:42:40 file A /sample2/test_2.txt
View 2 Replies
View Related
Jul 24, 2012
CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,
[Code]...
NAME PRICE DATAENTRD ROWRANK
aaa 9999 24.07.2012 05:56:00 1
aaa 10000 24.07.2012 05:55:58 2
I want to insert this result into another table, how can I do it??
Quote:TABLE
CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)
View 8 Replies
View Related
Apr 2, 2012
I am in the need of using a table type object in SQL query.
I have a package which has a spec in which I have declared :
TYPE TESTREC IS RECORD
(
RE_ID NUMBER(9),
RATING_TARIFF_NAME VARCHAR2(40),
);
TYPE TESTTABTYPE IS TABLE OF TESTREC;
TTR TESTTABTYPE;
In one of the package procedures I am collecting data into the above indicated table type object (TTR):
SELECT
RE_ID,
RATING_TARIFF_NAME
BULK COLLECT INTO TTR
FROM TESTPACKTAB;
This works fine. The values get collected into TTR and am able to print them too.
But when I :
SELECT
AA.RATING_TARIFF_NAME
INTO v_name
FROM
TABLE( TTR ) AA ;
in the same procedure immediately after the collection I get the error while running the procedure :
ORA-21700 : Object does not exist or is marked for delete.
View 5 Replies
View Related
Apr 24, 2010
Below query. Below is the DDL , DML and expected output.
create table tab_1 (col1 varchar2(10), col2 varchar2(10));
select * from tab_1
Output of query
col1 col2
12
23
34
[code]...
The above query is not giving below expected output.
Output:
4 4/3/2/1
3 3/2/1
7 7/6/5
8 8/7/6/5
11 11/10/9
View 6 Replies
View Related
Jun 24, 2011
Below query is taking a long time...
select gam.SOL_ID,COUNT(gam.FORACID) from gam,smt where
gam.ACID=smt.ACID and gam.ACID NOT IN(select ACID from imt) and
gam.SCHM_TYPE in('SBA','CCA','CAA','ODA') and GAM.ACCT_CLS_FLG='N' and
gam.SOL_ID IN(select SOL_ID from IMT) group by gam.SOL_ID
/
attached is the explain plan.
in which index on IMT table is not used. And the query is doing a FTS on IMT table. What needs to be done to avoid FTS on IMT table.
View 10 Replies
View Related
Oct 30, 2013
How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select *
from some_table st,
other_table ot
where st.id = ot.id
View 15 Replies
View Related
Sep 3, 2013
I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are
where owner NOT IN ('SYS','SYSTEM') and data_type IN ('CHAR','VARCHAR2','NUMBER')
My query as follows
select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner
View 3 Replies
View Related
Oct 19, 2010
sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
View 2 Replies
View Related
Jan 11, 2007
I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"
How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?
SELECT COUNT(*) FROM my_table_name
WHERE my_date_column LIKE '2006-12-30%'
GO
It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.
View 2 Replies
View Related
Aug 23, 2012
I have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.
View 3 Replies
View Related
May 21, 2013
how can i create this query on a procedure:
Insert into COMPUSOFT.PESAJE@DB_2
(PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA,
PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD,
USR_COD, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO)
select /*+ FULL(Tbl1) */
[code]..
plus i would like to insert also that when it runs the query also change a value in table pesaje column dmn_cod to "yes" default "no" in db_2
View 5 Replies
View Related