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]...
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
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)
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
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]....
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.
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 RelatedI'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.
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
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.
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?
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.
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 RelatedI 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.
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.
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
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
provide me the query to check our database is running using spfile or running using pfile.
View 2 Replies View RelatedHow 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 RelatedI 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.
How to find currently running scheduled jobs using query !?
View 3 Replies View RelatedI 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]...
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.
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..
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
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 /
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.
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.
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 ?
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
