SQL & PL/SQL :: GROUP BY When Running Query

Jul 29, 2010

i am facing group by issue when running the query.the error code is ORA-00979: not a GROUP BY expression.i suspect is due to the subquery in the SELECT clause.

SELECT
WHINR100.COMPANY,
WHINH210.SEQN,
FXINH039.OTBP,
WHINR100.BPID,
TCCOM100.NAMA,
WHINR100.ITEM,
WHINH210.RCNO,
FXINH051.btch,
case when (whinh210.ORNO in (select fxinh033.pdno from fxinh033))
[code]...

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Group Results By Date With Running Totals?

Apr 30, 2012

My version of the Database:

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

I have a staff table with the columns staff_id and completion_date. The completion_date shows the date a staff member completed questionnaire. If the staff member did not complete the questionaire, then the completion_date column will be NULL.

Table Definition:

-- Create table
create table staff
(
staff_id number not null,
completion_date date
);

See attached text file (staff.sql) for Insert Statements.

The result set needs to have the following columns: ReportDate: the Sunday of each week. Completed: The number of staff who have completed the questionnaire by the ReportDate. NotCompleted: The number of staff who did not complete the questionnaire by the ReportDate. Total: The SUM of Completed and NotCompleted columns.

As the number of Completed goes up, the number of NotCompleted goes down. Eventually Completed will equal Total and NotCompleted goes to zero.

The result set would look similar as follows and used to generate a bar graph chart:

ReportDateCompletedNotCompletedTotal
2012-Apr-01814651473
2012-Apr-082214511473
2012-Apr-1514413291473
2012-Apr-2242010531473
2012-Apr-295788951473

View 8 Replies View Related

SQL & PL/SQL :: Group By Query

Nov 5, 2011

I am new to PL/SQL, could you pls let me know how to solve this requirement using PL SQL

C-A | C-B
A123 | -1
B334 | 4
B567 | 2
B333 | -1
T777 | 2
Y774 | 3
T879 | 4
T654 | 3
T474 | 0
Y432 | -1

I need the output like this

C-1|C-2|C-3|C-4
3 |7 |3/10|7/10

Column-1 (Count of rows where Column-B = -1)
Column-2 (Count of rows where Column-B in(0,1,2,3,4)
Column-3 (Column-1/Total rows)
Column-4 (Column-2/Total rows)

View 5 Replies View Related

Query For Group By Date?

Apr 30, 2011

We have one oracle table, which has one column "Creation_Date" of the datatype date.

This column has data like
"14-Aug-2010 01:30:30"
"14-Aug-2010 03:30:30"
"15-Aug-2010 01:30:30"
"16-Aug-2010 01:30:30"
"16-Aug-2010 03:30:30"

We want to write SQL query which will group rows by date wise without considering time..I mean for above rows the date wise output should be

Count Date
------- -------
2 14-Aug-2010
1 15-Aug-2010
2 16-Aug-2010

View 2 Replies View Related

Count And Group Query?

Apr 23, 2012

I have a report I created and I need to get a Total Count by Plan Code Description for Each State. I do this and get all my data:

SELECT
mv.R1_State,
mv.subscriber_id,
mv.plan_code,
pm.description,
mv.line_of_business,

[code]...

But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???

Select
Count(pm.description),
mv.R1_State
FROM windsoradm.member_mv mv

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Query With Horizontal Running Totals

Dec 20, 2012

I'm trying to create a report in the following format

Year Name Reg1 Reg2 Reg3
2001 Al 3 4 5
2001 Le 4 1 1
2001 7 5 6
2002 Sue 2 4 1
2002 Al 1 3 6
2002 Jim 6 1 3
2002 16 15 16
2003 Jim 4 -3 2
2003 Le -2 4 5
2003 20 16 23

Note that the totals are accumulating horizontally, broken on year.

View 3 Replies View Related

SQL & PL/SQL :: Know Execution Time Without Running Query?

Jul 13, 2008

Is there a way to know how much time will a query take to execute without running it, just like using the autotrace (traceonly) and explain plan utility.

View 16 Replies View Related

SQL Query Group ID Ranges To Chunks

Sep 23, 2010

I've the following table:

MEASURE_VALUE(ID, VALUE) containing measure values. I would like to calculate the average of a specific id interval. In my case the id is the position where the value was captures.

Example:
id[m] value[mm]
1 1.2
2 1.5
9 2
11 3
18 1
28 1.2

I would like to group ids in a specified range. For n = 3 the result should look like this:
from_id, to_id, avg
1 2 1.35
9 11 1.5
18 18 1
28 28 1.2

I have to find a way to group ids to chunks.

View 9 Replies View Related

SQL & PL/SQL :: Query To Get Sequence Number In Group

Dec 7, 2010

I have Table Data in format :-

--------------------------
ABC
ABC
ABC
XYZ
XYZ

and i have a requirement where I need the output in this format.

-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2

what query to be used for this

View 2 Replies View Related

Forms :: Query In Record Group

Mar 6, 2011

I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.

p.s: That record group is built in run-time.

View 1 Replies View Related

Forms :: Get Record Group Query

Apr 3, 2010

How can I get record group query in code and chang it ?( for example change its where clus). I need to get its Query.

the record group exists and I must get its query not make it.

and I know about Populate_Group_With_Query for changing but cant I have the query to change?

FOR EXAMPLE :
my record group query is :
select a ,b from A where A.c=1;

and I use it in an LOv.

Now i want get this record group in code and change its WHERE cluse after pressing A BUTTON How can I do it?

View 2 Replies View Related

SQL & PL/SQL :: Adding Inner-query Select To Group By?

Aug 30, 2011

I have this query

select EAG.AUDIT_NUMBER Audit_Nbr,
EAG.AUDITEE_NAME Grantee_Name ,
EAG.EIN Grantee_EIN_IRS,
EAG.AUDIT_ISSUE_DATE Audit_Issue_Date,
MAX(AUDT.derive_audit_progress_status(EAG.SYS_AUDIT_ID )) Audit_Clesed_Date,
EAG.OIG_DUE_DATE Six_Month_Due_Date,

[code].....

I want o add additional column to this

I added this sql

SELECT CASE
WHEN currentstep.step_id IN (100)
THEN currentstep.start_date
ELSE (SELECT start_date
FROM audt.os_historystep
WHERE ID =

[code].....

here is the completed query

select EAG.AUDIT_NUMBER Audit_Nbr,
(
SELECT CASE
WHEN currentstep.step_id IN (100)
THEN currentstep.start_date
ELSE (SELECT start_date
FROM audt.os_historystep

[code].....

when I try select from this query I get ORA-00904: "DATE1": invalid identifier.

how to add the new column in group by clause.

View 6 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 To Get Rows Of Latest Date From Each Group

Aug 9, 2012

I want to get rows by latest date for each group pf rows.

say my data is :

TYPE1 TYPE2 quantity DATE-ORDERED
sweets chocolate 10 05-FEB-2012
sweets chocolate 10 04-DEC-2012
sweets chocolate 10 08-FEB-2012
pastries chocolate 20 08-AUG-2012
[Code] ..........

I want to get results by latest date,
sweets chocolate 10 04-DEC-2012
pastries chocolate 20 08-AUG-2012
sweets vanilla 10 05-DEC-2012
pastries vanilla 20 05-NOV-2012

I have tried Queries with Max(DATE-ORDERED) and grouping it..its not showing me results because I don't have indexes in my data.

View 2 Replies View Related

SQL & PL/SQL :: How To Group Query Sequence Number Wise

Oct 3, 2010

I ve a data in table as follows

code status seqnce Length
B1 AVL 1 10
B2 AVL 2 10
B3 ASG 3 10
B4 AVL 4 10
B5 AVL 5 10
B6 AVL 6 10
B4 ASG 7 10
B4 ASG 8 10

I need to group the AVL status codes with the sum of lengths Ex) B1 to B2 - 20 B4 to B6 - 30 like that.

View 5 Replies View Related

PL/SQL :: Query With Group By - Fetch Data From Database

Jul 26, 2013

I am trying to write a proper query to fetch data from database. Scenario:

I need to retrieve employees who are not working in multiple departments. scott@TESTCRM> select * from emp1;     

EMPNO     DEPTNO
---------- ----------     
7654         30      7698         30      7788         20      7788         30      7876         20      7900         10      7900         30    7902     20    7934     10 

scott@TESTCRM>  

Ouput Expected is      

EMPNO     DEPTNO
---------- ---------- 
7654         30      7698         30      7876         20      7902         20      7934         10

View 9 Replies View Related

Client Tools :: Running Query Using CSV File As Reference

Feb 19, 2012

I need to say I am an absolute NOOB when it comes to SQL.I need a script to run in TOAD that will reference a CSV file saved onto my local hard drive. I'll try and describe exactly what I need to do.

The current script which I use via TOAD on our companies READ ONLY database is this:

SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN ('1230001', '1230002', '1230003')

This will return a result for each number that exists within the table.number table along with the status of each number i.e. active or inactive. A very basic query.

What I need to be able to do is run that query but instead of having to copy each number into TOAD manually, I need TOAD to check a .csv file of said numbers and then return the results.So I imagine the query would look something like:

SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN (check

View 13 Replies View Related

SQL & PL/SQL :: Query To Check Database Running Using Spfile Or Pfile

Nov 23, 2011

provide me the query to check our database is running using spfile or running using pfile.

View 2 Replies View Related

Performance Tuning :: How To Find Top Query Running On A Table

Feb 19, 2012

How do i find a particular SQL or a set of SQL's which are excuted against a table (user identified table) that is either a very frequently executed query against that table or high impact SQL against that table? I am currently looking through the AWR reports to go through all the queries but i was wondering if there are any dictionary views where we can find this info from?

View 2 Replies View Related

Precompilers, OCI & OCCI :: Running Query In PRO*C That Works In SQLPLUS

Sep 4, 2008

I have a query similar to the one below which is embedded in a PRO*C program. The query works fine when run in the PRO*C program against a 8i database but fails with an "ORA-02015: cannot select FOR UPDATE from remote table" error when run against a 10g database. The PRO*C program is executing the sql using "EXEC SQL".

QUERY: Select last_name, first_name from Member
where ....
FOR UPDATE OF LAST_NAME;

The other thing to note is this SQL query works fine via sqlplus in a 10g environment.

ADDITIONAL DETAILS: The above query is selecting data from a base table via a user view
VIEW: select * from
otherschema.member@connection_identifier;

This view was created in this manner to allow the user account access to the underlaying table without creating explicit permissions.

View 11 Replies View Related

Scheduler :: Find Currently Running Scheduled Jobs Using Query

Feb 27, 2013

How to find currently running scheduled jobs using query !?

View 3 Replies View Related

SQL & PL/SQL :: Pivot Query - ORA-00937 - Not A Single Group Function?

May 8, 2012

I am having the following pivot query but I am not able to run it is giving error msg

ORA-00937: not a single-group group function

SELECT DISTINCT
C.SHORT_NAME,
MAX (
COUNT (DECODE (TO_CHAR (a.reg_date_cur, 'MON'), 'JAN', A.CAR_NO))

[code]...

View 6 Replies View Related

Quest Toad Software - Does It Preload Tables When Running Same Query Again

Feb 19, 2009

I have a large, relatively complex query which I am in the midst of trying to optimize. I am using Toad as my SQL editor and executor. I am trying to test the execution time, but the first time the query executes is a good 5mins slower than future executions. I am wondering; does Toad "pre-load" the tables from previous querys when re-running a query?

I want to be able to run a query as if its the first time it is being run in order to truly gauge whether I am really optimizing the code or not.

View 1 Replies View Related

Performance Tuning :: Query Running For A Long Time In Second Schema

Apr 27, 2012

I have a Query(report) which is running in <5 mins in one Scheme, where as the same is running for a long time in second schema. I have identified that an Index is scanning for more than 2000 Millions of records in second Schema, but this is scanning only 440 Millions in First Schema and hence it is fast. I am expecting the same to be done in Second schema.

I have verified the following
All records in tables in 2 schemas are same.
All indexes are same
Analyzed the tables
Gathered Histogram on all the columns as per the first schema.

But now i still have the same problem, don't know what could be the problem.

Table_nameNum_RowsBlocks
PRPSL_LST_T5866107159
PRPSL_WKFLW_ACTVTY_T5829904030
ITEM_CHR_VAL_T5134340104049020
ITEM_RGN_ASSN_T8571220137215

Also attached 2 screen shots of OEM Plans..

View 2 Replies View Related

Performance Tuning :: How To Check If Paging Happening While Running The Query

Sep 26, 2013

How can i check if paging happening while running the query. As i have 4gb of PGA target but the query is taking long time in parallel and has hash join.

how to check paging in 11gr2

View 3 Replies View Related

Performance Tuning :: How To Find Percentage Completion Of Query In Running Status

May 2, 2012

How can we check completion status for running sql query. i.e. how much % completed

SQL> begin
2 delete from gsmcrmdw.wc_loy_txn_f_aa
3 where integration_id in
4 ( select integration_id
5 from support_olap.recover_wc_loy_txn_f_953to955
6 );
7 commit;
8 end;
9 /

View 35 Replies View Related

Application Express :: Prevent Interactive Report From Running Query On Initial Page Load?

Oct 1, 2012

I am on APEX 4.1.1, db 11g and I have a report which is quite slow to retrieve results from its query. The page has some filter fields (e.g. store code, dept code) in addition to the generic search field.

When the user lands on this page (from a menu page), the query runs before the page displays and this typically takes 1-2 mins without any filters yet set. The usual browser loading indicators are not obvious to the user and they might not be aware that it is working on the request.

I want it so that it will only run the query AFTER allowing the user to get to the page, let the user fill in some filter criteria and then click on the GO button. Therefore the query will run after the page is submitted. When you hit the GO button on the interactive report, a nice rotating apex_loader.gif appears, making it obvious to the user that it is working on the request.

I would prefer NOT to create 2 pages, one for user filter, and a second for the actual report as I have many reports/inquiries which fit this category and I would prefer NOT to have to create pairs of pages for each.

View 5 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

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

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

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

SQL & PL/SQL :: Group Through Expression Inside Group By?

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







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