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

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

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

SQL & PL/SQL :: External Table Query (compare Number Records In File With External Table)

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

Using PL/SQL Table With Regular Query

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

SQL & PL/SQL :: Query For Table Has 90 Column?

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

SQL & PL/SQL :: Query For Unnest A Table

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

SQL & PL/SQL :: Recursive Table Query?

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

SQL & PL/SQL :: Table Join Query

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

SQL & PL/SQL :: Query Two Table After One Column ID?

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

SQL & PL/SQL :: Getting Info About A Table Through Query?

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

SQL & PL/SQL :: Query A Table Beginning With @

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

PL/SQL :: Select Query On Emp Table

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

PL/SQL :: Query To Get Reference Of Table?

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

PL/SQL :: How To Use Value Taken In Variable As Table Name In Query

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

Create Table From Select Query?

Sep 13, 2004

Can we create a table from a Select query ?

View 5 Replies View Related

Query To Know Number Of Columns In A Table?

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

Getting Query To Do Full Table Scan?

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

SQL & PL/SQL :: Insert Into Table A With Select Query On B And C?

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

SQL & PL/SQL :: Query To Find Relevant Table Name

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

SQL & PL/SQL :: How To Write Query To Convert XML To Table

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

SQL & PL/SQL :: Inserting Result Set Of Query Into Another Table

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

SQL & PL/SQL :: Table Type Of RECORD In A Query?

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

SQL & PL/SQL :: Create Table Hierarchical Query?

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

Performance Tuning :: Query Doing FTS On A Big Table

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

SQL & PL/SQL :: Select * From One Table But Not All Tables In A Query

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

PL/SQL :: Query To Find Table And Column Name By Using Value

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

Group By Month - Query A Transaction Table?

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

Query Table Entries With A (timestamps) Column?

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

How To Give Table Name Dynamically In A Select Query

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

Create Query On Procedure - Change Value In Table?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved