SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query
Sep 6, 2010
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
Query for anyone interested:
Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.
View 18 Replies
ADVERTISEMENT
Feb 27, 2012
Im running the following query in DB - Prod2
select *
from t_act_rwrd_reimb_stay a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
The above table t_act_rwrd_reimb_stay --> is from a view on the database Prod2 and this view is pulling the data from the database prod1 via dblink
When I run the same query on Prod1 it returns 3 rows. Is there any reason this is not fetching data on prod2 view? or whatz wrong with the above query
or for example if I run the above query as follows with the dblink "@prod1" it returns the data properly
select *
from t_act_rwrd_reimb_stay@Prod1 a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay@prod1 b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
View 10 Replies
View Related
Jul 20, 2010
Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.
View 12 Replies
View Related
Apr 1, 2011
i am trying this query
SELECT
CASE
WHEN TYPE = 'COUNTRY' THEN (SELECT CODE FROM LIST_T)
END mi
FROM LIST_T
and in that subquery i am returning two or more than records so it is giving me the error like ORA-01427: single-row subquery returns more than one row
how do i solve this error and i must to fetch two or more records from the subquery if it possible in any other
View 11 Replies
View Related
Jul 23, 2009
I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.
This is the pre-existing query:
SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.
View 14 Replies
View Related
Nov 20, 2009
want to update 230 records in ins_spr table but its returning error..
Sql statement in blue color returning 230 records.
------------------------------------------------
update ins_spr set
SPR_EXCC = 'NORSk'
where spr_code = (select distinct spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS') and spr_stuc in (select distinct a.sqe_stuc from srs_sqe a where a.SQE_EQEC = 'NP3M' and a.SQE_SQSC = 'BE' and a.sqe_stuc in ( select distinct b.sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))
OR
update ins_spr set
SPR_EXCC = 'NORSK'
where spr_code = (select spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS')
and exists (select sqe_stuc from srs_sqe a where sqe_stuc = substr(spr_code,1,8) and SQE_EQEC = 'NP3M' and SQE_SQSC = 'BE'
and exists ( select sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))
View 1 Replies
View Related
Apr 17, 2012
One of the tables is having
SQL> select count(1) from item_list;
COUNT(1)
----------
14356529
To fetch the count using the above query , it takes 6 mins. There are no other processes running in the DB. The fragmentation % on this table is showing 16.3%
I tried finding the fragmentation using the below query.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
((round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"
from dba_tables
where table_name like ITEM_LIST%'
and owner = 'TEST_USR'
and blocks != 0
order by 4 desc
TABLE_NAME table size kb actual data in table kb
------------------------------ ------------- -----------------------
wasted space kb percent fragmented
--------------- ------------------
ITEM_LIST 5163360 4318302.27
845057.73 16.3664306
View 15 Replies
View Related
May 2, 2012
I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?
Let's consider an example with 5 records:
Table data:
CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/
[Code]...
So when we check the table, we get:
SELECT * FROM enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
What I would like to have when I run the select query:
SELECT * from enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.
View 6 Replies
View Related
Jan 10, 2011
I am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
View 12 Replies
View Related
Mar 15, 2012
I am using PL/SQL Developer.I have two tables: A and BTable A contains serial_from and serial_to values.This is used to define the serial numbers issued to customers (i.e. the start and end range of serial numbers issued).Shown here for a client:
Table B contains the individual numbers, B.serial (within the serial_from and serial_to range) along with other data, and is only created when the serial is used by the client.
I would like to create a list of records for individual clients containing serial numbers issued but not used. i.e. they are in between the serial_from and serial_to values in Table A, but not in Table B.
How can I create a list of numbers issued, but not yet used? Because Table A contains only two values (to and from) no record exists for them, so how do I generate a list and check against it?
View 3 Replies
View Related
Dec 28, 2010
Actually m working on oracle 10g forms. I have a list item in which no. of records displayed is 10. I am populating this list item through a record group. I want when user select a value from first record then he should not be able to select the same value in further record of that list item.
View 12 Replies
View Related
Jun 1, 2010
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
View 9 Replies
View Related
Feb 21, 2011
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....
View 6 Replies
View Related
Apr 22, 2011
I need to find the most recent rows (top 10) in a table... I have about 300 tables so the fields are different, is there a way to generically find it?
View 17 Replies
View Related
Feb 8, 2007
I need to write a query to sort the records in a particular order,
Say if I group the records by Dept number
Dept no Name
10A
10G
10f
20B
20K
30I
30M
30R
30Y
I need to write a query that will make this records listed like
Dept No Name
10A
20B
30I
10G
20K
30M
10F
20null
30R
10null
20null
30Y
View 1 Replies
View Related
May 10, 2013
I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.
--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))
--insert into t1(col1, col2, flag) values(1, 'a', 'Y');
--insert into t1(col1, col2, flag) values(2, 'b', 'N');
SELECT *
FROM t1 x
WHERE col1 = 1 AND col2 = 'a' -------------- condition1
AND 0 = -------------- condition2
NVL (
(SELECT COUNT (1)
FROM t1 y
WHERE y.flag = 'N'
AND x.col1 = y.col1
AND x.col2 = y.col2),0)--=0
When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.
View 9 Replies
View Related
Sep 20, 2011
The requirement is I have a table (TAB1), wherein I have 3 columns, ID, LID and STATUS.
The value in ID column = ID_SEQ.NEXTVAL,and LID will be either 0 or 1 and the possible values for STATUS are 'ED','CP', NULL. The ID column is not suppose to have duplicate values, but there is no check on the table for the same.
Someone has updated the existing data and ID column is containing duplicate values. Wherever LID = 0 and STATUS = NULL and if only if ID is duplicated then the ID_SEQ.NEXTVAL has to be assigned to ID field, so that there are no more duplicate values.
CREATE TABLE tab1 (id NUMBER , lid NUMBER, status VARCHAR2(10));
Existing Data
------------------
INSERT INTO tab1 VALUES (1,0, 'ED');
INSERT INTO tab1 VALUES (1,0, 'CP');
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
[code]....
get the result using a single update statement.
View 5 Replies
View Related
Aug 23, 2010
SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID
There is no date column in table using sysdate alone need to retrieve last 6 month records
how to use in where condition
View 13 Replies
View Related
Nov 26, 2012
I've stucked with a query. I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
(
(PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID1 <> 13
union
[code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
View 7 Replies
View Related
Mar 5, 2010
I want to fill data from the above records in the same column. How to do that by use SQL command ?
from
Invoice_No.Line_Item
000011
2
3
000021
2
000031
000041
2
000051
to
Invoice_No.Line_Item
000011
000012
000013
000021
000022
000031
000041
000042
000051
View 10 Replies
View Related
Feb 20, 2012
I am trying to query a form having two data blocks. one is a master and the other is a detail block. the problem is when i insert data in the form, save it, and press enter query key, the data in the master block fields are still there. while the data in the detail block field are cleared and i can insert values. how to clear data from master block field so i can query
View 2 Replies
View Related
Jun 17, 2012
Having following table:
UserID REC_TYP REC_CD
12345 'OFFR' 12
23456 'MSG' 13
I'd like to construct the query which in this particular case would return the REC_CD as 'Record_ID' for REC_TYP='OFFR' where USERID=? (always fetched by the application) and if such USER_ID doesn't exists (for the particular REC_TYP of course) to return string or any other value. e.g. The result for this query in case of user_id 23456 = would be "doesn't exist" or sth for instance 'FALSE' and for 123456 it would be '12'
View 2 Replies
View Related
Dec 18, 2012
I have a following requirement in SQL -
Requirement -
I need to list the purchase Ordrers which are not closed.
my sql query should pull if the PO is not closed in 30,90 and 150 days.
It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.
View 1 Replies
View Related
Jun 25, 2013
I have a select query select userid from user_tbl where name='hhh';
I want to return 0 when no records are fetched and I want to handle both teh cases in a single query other than proc or function.
View 12 Replies
View Related
Mar 15, 2011
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
View 1 Replies
View Related
Feb 20, 2012
I have a table with following values in a column
Table A
col1
10
35
20
25
I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.
Table B- 90 records (10 + 35 + 20 + 25)
Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,
with first group having start value -1 and end value -10
second group - start value -11 and end value-45
third group - start value -46 and end value -65
fourth group - start value - 66 and end value-90
one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.
note that the values in Table A is dynamically changing, so not able to hard code values.
View 2 Replies
View Related
Jun 30, 2010
For one example table t1 as below, I want to find the maximum 5 biggest size, but there should not be more than 2 name from the same position.
Name Position Size
N1 P1
N2 P2 50
N3 P3 500
N4 P1 6
N5 P2 60
N6 P3 600
N7 P1 7
N8 P2 70
N9 P3 700
View 1 Replies
View Related
Jun 26, 2013
I want to raise a alert after the records retrieved from a query. i.e, if the query fetches 0 records then the alert show be displayed. Is is possible? If yes tell me the trigger to be used to raise such kind of alert.
View 4 Replies
View Related
Dec 8, 2010
There is a table in Database with millions of records and a query --- Select rowid, ANI, DNIS, message from tbl_sms_talkies where rownum<=:"SYS_B_0" ---- using the high CPU and also this query having high number of executions.
View 10 Replies
View Related
Mar 17, 2010
i have a doubt in building a query.
I have a table with fields
job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
View 12 Replies
View Related
