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
ADVERTISEMENT
Jun 19, 2012
I have 11.1.0.7 instance running and got more 300G of storage available for the database. I would like to know what are the steps to add these disks/storage to the existing DATA asm disk group?
View 5 Replies
View Related
Aug 26, 2010
We have a Production Oracle 10g R2 RAC on HP-UX v2 IA64 servers.We have Two Disk Groups one for Archive (ARC_DISK - 100 GB) and other for Database(DATA_DISK - 1 TB]. We wanted to add more space to the DATA_DISK disk group.Unix admin configured 200 GB from SAN and changed the ownership of the Disk to oracle and permissions to 775 on 1st Node.I opended DBCA from 1st Node and was able to see the disk in 'Show Candidate'.
I added this disk to the DATA_DISK disk group and clicked OK but got ORA- error with some message like some operations could not be performed. I exited DBCA.We realized that we had forgotten to change the ownerhip and permission from the 2nd Node.Unix admin changed the ownership of the Disk to oracle and permissions to 775 on the 2nd Node.
I opened DBCA again from 1st Node and selected the DATA_DISK disk group but could not find the Disk in 'Show Candiate' open. I clicked on 'Show All' and this disk was shown with Header_Status - MEMBER but not allocated to DATA_DISKGROUP. When I clicked the 'Show Member' option, this disk is not shown for DATA_DISK disk group. I exited DBCA at this point.As this is a critcal Production database I didnt proceed any further and exited DBCA.
Now I need to add this Disk to the DATA_DISK disk group but not sure which option to select. I got one reply from another forum to run DBCA select the DATA_DISK Disk Group and then click 'Show All' and select this Disk (which already has MEMBER as Header Status) and select Force Option and click OK to continue.
View 4 Replies
View Related
May 4, 2010
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
View 8 Replies
View Related
Nov 17, 2011
I was given this query: SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
My coworker also said this:
Using this statement as a starting point:
SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
but I need
for
select -5 from dual;
to return
America/NewYork or EST
View 6 Replies
View Related
Nov 21, 2011
I am asking about the best way in SQL for the following case:
CREATE TABLE TRY_A (A NUMBER, B NUMBER);
INSERT ALL
INTO TRY_A VALUES (1,0)
INTO TRY_A VALUES (1,1)
INTO TRY_A VALUES (1,2)
INTO TRY_A VALUES (2,0)
[code].......
I need to learn the best way to select all A values where B is never 0. So if the needed result for the above example is:
A
--------------
3
5
As column B was never 0 with column A = 3 or 5.
View 11 Replies
View Related
Jun 13, 2011
I am having a following sql query:
Select
Product.code,
Customer.code,
Backlog.Date
SUM(Backlog.Qty)
From
Product,
Customer,
[code]....
Using this query i am getting following output::
Prod Cust Date Qty
A X 17-june 1000
A X 18-june 2000
A X 21-june 4000
B Z 11-May 200
B Z 15-May 500
C W 1- Dec 300
Out of these groups, i want to select qty for each product and customer, where date is maximum,that is following results::
Prod Cust Date Qty
A X 21-june 4000
B Z 15-May 500
C W 1- Dec 300
what condition/clause should i add in my query,tried a lot with having clause but on success.
View 17 Replies
View Related
Apr 14, 2010
I have a query, running the query with order by and without orderby clasue casues big performance difference.
here without order by it takes 5 sec
.
SELECT
audit_number,
formatted_audit_number,
ag.sys_audit_id,
audit_begin_date,
audit_end_date,
[Code]....
here with order by it takes 2 min
SELECT
audit_number,
formatted_audit_number,
ag.sys_audit_id,
audit_begin_date,
audit_end_date,
auditee_name,
ein,
[Code]....
I have like 10,000 records.
View 16 Replies
View Related
Dec 5, 2012
i have a table below; sql> desc css_survey
SURVEY_ID NOT NULL NUMBER(5)
USER_ID NOT NULL VARCHAR2(15)
ACADEMIC_SEMESTER VARCHAR2(25)
Q1 NUMBER(1)
Q2 NUMBER(1)
below are the records
survey_id user_id academic_semester q1 q2
1 1 2012 2 3
2 2 2012 2 3
3 3 2012 3 4
4 4 2012 3 4
5 6 2012 2 4
the Q1 and Q2 could have values 1,2,3,4,5 thats means total are 5 questions. i need to know the total users who select value from q1 group by the values from 1..5 the toal_users who select value from q2 values( a group).i need the following result
total_users question_Q1_select question_Q2_select
3 2
2 3
3 4
2 3
View 2 Replies
View Related
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
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
Jun 15, 2012
current environment
Oracle VM 2 Node RAC cluster
Oracle 11G 11.2.0.3.0
Oracle 11g rel 2 GI
on current 2 node cluster we have GI and RAC db configured Nodes: vmorarac1,vmorarac2
we shutdown vmorarac1 to clone it to vmorarac5
on new node I have changed the hostname to vmorarac5 In /etc/sysconfig/network-scripts/ifcfg-eth0 change ip to new ip and same for /etc/sysconfig/network-scripts/ifcfg-eth1 152.144.199.210,152.144.199.211
mad echanges to /etc/hosts on vmorarac1/2 for 2 new IP address assigned to vmorarac5
152.144.199.171 vmorarac1.pbi.global.pvt vmorarac1
192.168.2.30 vmorarac1-priv.pbi.global.pvt vmorarac1-priv
152.144.199.184 vmorarac1-vip.pbi.global.pvt vmorarac1-vip
[Code]....
Query is is that all ok to reboot the new node to reflect changes as below and change the hostname to vmorarac2
152.144.199.210 vmorarac2.pbi.global.pvt vmorarac2
192.168.2.32 vmorarac2-priv.pbi.global.pvt vmorarac2-priv
152.144.199.211 vmorarac2-vip.pbi.global.pvt vmorarac2-vip
hostname change in /etc/sysconfig/network
On new node vmorarac5, there is alreay software for RAC DB and GUI cloned from vmorarac1
addnode.sh is used to add existing node, but as part of pre-requisite configuratoin is there any config step missing
View 3 Replies
View Related
Mar 10, 2012
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number
FROM employee_phones
emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333
Or similarly with Top N concept:
SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
Or really what I want in my export:
SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
phone_number
-------------------
1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
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
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
Dec 6, 2012
We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
what should I do to fix the problem with the NAFBCA schema.
Attached File(s)
NAFBCA.JPG ( 57.49K )
Number of downloads: 6
NAFBCA2.JPG ( 60.36K )
Number of downloads: 4
View 1 Replies
View Related
Feb 23, 2009
I want to select all the field from the table and want to put alias for only field i m just wondering is there any way to write a query something like
select *, emp as employee
from empMaster
here i want to display all the column but only with emp column i want to put alias.
View 1 Replies
View Related