SQL & PL/SQL :: Join For Bringing Master And Child Records Based On Condition?
Jul 30, 2013
The attachment contains the table info. The condition is that when there are childer for the master ,the master record shd be negated . Excuse me if this very easy. Example -DEL HAS children so the record with DEL and DEL SHD not be in the result query. Example -RAG HAS NO children. so the MASTER record should be taken.
Input
COL1COL2COL3COL4
MASTERDELDELP1
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1
My expected output
COL1COL2COL3COL4
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1
View 3 Replies
ADVERTISEMENT
Jun 16, 2011
I have two tables. By joining these two tables, I need to update a field in table1.
UPDATE table1
SET table1.FLAG = 'Fixed'
where table2.lastname = table1.lastname
and table2.status in ('fulltime','parttime')
I keep getting error 'table1.lastname' is invalid identifier.
I can't understand the error message. I made sure that the fields exist.
View 5 Replies
View Related
Oct 10, 2012
CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');
SELECT * FROM test1
STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ
The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM
This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.
Expected Output.
STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10
10 20 AB
21 25 AB
12 22 PC
23 32 PC
33 35 PC
22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM
View 7 Replies
View Related
Jan 27, 2012
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form
2)Reservation Form
3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
View 9 Replies
View Related
Mar 7, 2011
when i run report in PDF, RTF or HTML then correct numbers of records appear but when i run same report in excel then it bring extra records (duplicate).
View 1 Replies
View Related
Nov 10, 2010
I need to work on this requirement.
There are FOUR tables ( T1 , T11 & T2, T22) ALL store order information.
One of four conditions are possible for each Supply Reorder Number:
•Both table queries return no records
oPopulate all the output fields with nulls
•T1 returns a record, but T2 returns no records
oPopulate output fields with values from the join of T1 and T11.
•T1 returns no records, but T2 returns one record
oPopulate output fields with values from the join of T2 and T22.
•T1 returns a record, and T2 returns a record
oIf the latest order is in T1, then populate output fields with values from the join of T1 and T11.
oIf order dates are equal from both join results, then populate output fields with values from the join of T1 and T11 .
oIf the latest order is in T2, then populate output fields with values from the join of T2 and T22.
How do we filter the dataset based on result of table join ?
View 1 Replies
View Related
Jul 8, 2011
I am working on form builder 6i. I have a header block A(Multi record block), child block B. Both are non control blocks. My requirement is, if i put the cursor in any header record, i want to auto populate corresponding child record like in master child relation.
which trigger i need to write logic to auto populate the child record.
View 4 Replies
View Related
Mar 24, 2012
I have 2 tables, ITEM_T is the base table and RELATIVE_T is the relation table which establish the relationship between parent and child.
I want to give ZZZ child as input and I want get output in below format. I tried various method, but I am not able to get the below result.
Result:
ChildParent_1Parent_2
ZZZYYY XXX
ITEM_T
PK_IDITEM_ID
PK_1XXX
[Code]...
Expected Result:
When give input as ZZZ:
ChildParent_1Parent_2
ZZZ YYY XXX
View 7 Replies
View Related
Mar 22, 2011
Test case: drop table test;
create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))
select * from test;
insert into test values (143,'frank','kadel,watson','j2098,k09876');
insert into test values (143,'steve','kadel,watson','j0987i,kuy765');
[code]....
The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.
For example:- Where id = 143, the resultset should be like this.
ID LAST_NAME FIRST_NAME EMPNO
---------- --------------- --------------- ---------------
143 frank kadel j2098
143 frank watson k09876
143 steve kadel j0987i
143 steve watson kuy765
sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.
SQL> select id, last_name, EXTRACTVALUE(x.COLUMN_VALUE, 'e') first_name, empno
2 from test,
3 TABLE(XMLSEQUENCE(XMLTYPE('<e><e>' || REPLACE(first_name, ',', '</e><e>') || '</e></e>')
4 .EXTRACT('e/e'))) x
5 ;
[code]...
View 8 Replies
View Related
Apr 8, 2012
create table ptab(pid number);
create table ctab(aphone varchar2(20));
drop table ctable
create table xtab(pid number,phone varchar2(20), tel1 varchar2(20), tel2 varchar2(20), tel3 varchar2(20))
insert into ptab values(1);
insert into ptab values(2);
insert into ptab values(3);
insert into ptab values(4);
[code]..
i have 3 tables, xtab, ctab and ptab
join condition
ptab.pid = xtab.pid
and
--------------------------------------------------------------------------------
what I want the join between xtab and ctab is, aphone should match with phone, and then tel1, then tel2, and then tel3, (if phone, tel1, tel2, tel3 are not null in that order only), if aphone matches with any of these,then just print that particular pid, important point is, aphone should be checked against phone, tel1, tel2, tel3 that order only
so the results should simply print
pid
1
2
3
4
5 should not be printed here because for pid 5 in xtab, none of the phone numbers match with aphone of ctab
I tried this:
select
DECODE (ctab.aphone,
xtab.phone, 1,
xtab.tel1, 1,
xtab.tel2, 1,
[code]...
but i cannot join ctab and ptab, i dont want to use intersect etc, because we are looking at millions of rows here
View 3 Replies
View Related
Mar 10, 2010
Suppose I have two tables
Transaction
TXN_ID:integer
TXN_DATE:date
Return_Transaction
RET_TXN_ID:integer
TXN_ID:integer
RET_TXN_DATE:date
[code]....
Transaction may have returned transactions. We use outer join to join the tables using TXN_ID. We have a report that shows the following data...The report use the following basic query to check all transactions with returned transactions information...Quote:
SELECT t.txn_id,
t.txn_date,
rt.ret_txn_id
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
Result
TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
102, 2010/03/11, 501
If user want to check all transactions with no returned transactions, a where clause is appended to the query
Quote:
SELECT t.txn_id,
t.txn_date
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
AND Nvl((SELECT 1
FROM return_transaction rt
WHERE t.txn_id = rt.txn_id),0) = 0
[code].....
View 4 Replies
View Related
Feb 16, 2011
I Require to Update the Data in Join Condition. When Run the Query the Error display as ORA-00933: SQL Command Not Properly Ended.
Query:
Update a set a.Dr_Re = Nvl(b.Dr_Amt,0),
a.Cr_Re = Nvl(b.Cr_Amt,0)
FROM xxsc.xxsc_creditors_aging_brnwise a
join (Select Branch,Invoice_id,vendor_site_code,segment1,
case when (sum(nvl(dr_re,0)) - sum(nvl(cr_re,0)) > 0) then sum(nvl(dr_re,0)) - sum(nvl(cr_re,0))
Else 0 End DR_AMT,
[code]........
View 2 Replies
View Related
Jun 27, 2013
How to use outer join condition in my below query.In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more recordsin the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records. I want all the records from
APPS_JP.GEDIS_OFFER_HEADER goh including other conditions.I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result. [code]SELECT GOH.ORIG_SYSTEM, gsp.USER_NAME, goh.ORDER_NO, goh.OMEGA_ORDER_NUMBER, goh.ORDER_TYPE, gc.CUSTOMER_ID, gc.OMEGA_CUSTOMER_NUMBER, CASE WHEN gc.PRIVATE = 'N' THEN gc.CUSTOMER_NAME ELSE ''
[code]...
View 4 Replies
View Related
Jun 14, 2012
i have a query that is not working properly. i am trying to join two tables and i have OR condition and outer join..i am getting error ORA-01719: outer join operator (+) not allowed in operand of OR or IN..my query is as follow
SELECT l.* FROM table1 l, table2 map
WHERE (NVL(l.id, '-99') = NVL(map.id(+), NVL(l.id, '-99'))
AND l.TYPE = 'TKE')
) OR
(NVL(l.id, '-99') = NVL(map.id(+), NVL(l.id, '-99'))
[code]....
)So basically in the query above i am saying that if type ='TKE' dont do the join on all the pos columns. if type is not TKE then do all the joins on all the pos columns etc.how can i rewrite query so that i can use OR and the outer JOIN in same query?
View 1 Replies
View Related
Jun 20, 2012
I have an outer join.
select *
from table a, table b
where
a.id = b.id (+) and
b.status = 'N';
I am not getting any results I want where the table b is null for table a due to the last condition. I would like the result to be like the one below.
Results:
Table a ID Table b ID Table b status
1 1 'N'
2 null null
View 4 Replies
View Related
Jan 2, 2013
I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.
MASS_MASTER --parent table.
MASS_CHILD --child table.
The below query is used to delete records from parent table.
DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);
The child table MASS_CHILD is not having last_date column. provide me the query to delete same records from child table.
View 21 Replies
View Related
May 24, 2011
I am facing below error while doing Schema refresh from production to development. I am facing this error in development database.
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "TASKS" ADD CONSTRAINT "VALUE_CK" CHECK (wrkt_k"
"ow <> 'S') ENABLE NOVALIDATE"
i guess "need to Remove the orphaned child records from the child table (foreign key relationship), and then re-execute the ALTER TABLE ENABLE CONSTRAINT command."
how to find orphaned child records from child table ?
how to resolve this error?
View 26 Replies
View Related
Aug 5, 2011
I have encountered a weird (or maybe not weird at all but unexplainable from my point of view) behavior from Oracle. I have simplified the example as much as possible
This query returns 2 rows as expected:
with edited as
(select F101, e_id from (select 'Test' F101, -1 e_id from dual
union all select 'Test1' F101, -2 e_id from dual) input_clob),
distinct_intermediate_edited as
[code]...
But this one (with only one row in input_clob) returns one row (as expected) but with null on e_id (why?):
with edited as
(select F101, e_id from (select 'Test' F101, -1 e_id from dual) input_clob),
distinct_intermediate_edited as
(select e.f101, e.e_id
from edited e
[code]...
If I change the join condition with and nvl(e.E_id,0) = nvl(e_id,0) both cases work as I expect (e_id = -1 for second query) but I simply want an explication for this behavior.
View 8 Replies
View Related
Jan 15, 2013
I have two permanent tables. I want to add the column DELTA_STROM from the table "Delta" into the table "TEST2". Here, the value of the field "POWER_DELTA" in the table "TEST2" depends on the field "Trade_Date".
If the time of the timestamp in table "Test2" is smaller than 12:40 than the value (DELTA_STROM from DELTA) of the day must be added into the field. If the time is huger than 12:40, the value of the next day must be added into the field "POWER_DELTA". All values for "Power_DELTA" for every day are in table "Delta" in the field "DELTA_STROM".
I just added the right values of "Power_Delta" into the table "TEST2" manually to give an understanding.
DATUM DELTA_STROM
-------- -----------
01.01.12 1.92
02.01.12 5.78
03.01.12 0.73
04.01.12 2.84
05.01.12 11.41
06.01.12 -2.76
07.01.12 0.43
08.01.12 1.25
09.01.12 -0.21
TRADE_DATE POWER_DELTA
---------------------------- -----------
02.01.12 11:21:41,720000000 5.78
02.01.12 12:44:01,236000000 0.73
02.01.12 14:05:01,845000000 0.73
02.01.12 16:21:01,345000000 0.73
02.01.12 17:25:41,470000000 0.73
03.01.12 10:31:01,376000000 0.73
03.01.12 11:55:11,798000000 0.73
03.01.12 16:32:47,612000000 2.84
04.01.12 08:24:01,486000000 2.84
04.01.12 12:24:21,189000000 2.84
04.01.12 17:46:21,123000000 11.41
CREATE TABLE "TEST2"
("TRADE_DATE" TIMESTAMP (6),
"POWER_DELTA" NUMBER
);
[Code]....
View 19 Replies
View Related
Jun 27, 2013
How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.
I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.
SELECT GOH.ORIG_SYSTEM,
gsp.USER_NAME,
goh.ORDER_NO,
goh.OMEGA_ORDER_NUMBER,
goh.ORDER_TYPE,
[code].......
View 10 Replies
View Related
Aug 16, 2010
I have two tables. i need to join the tables. The query is -
select v.c_venditore,v.s_venditore,v.t_diretto_indiretto,v.d_disattivazione,d.s_direzione from VENDITORE v,DIREZIONE d
where v.p_direzione=d.p_direzione order by v.s_venditore
In the table VENDITORE there are 2919 rows. I need to display all the rows. But the joining column p_direzione has some null values.I need to display the null also. But to join the two tables this is the only condition. How can i display all the rows.
View 3 Replies
View Related
Sep 16, 2012
I need to fech parent records only when no child record with status 'N' exists. There are only two possible values for status column of child table 'Y' / 'N'.
Below are table structures and insert statements for data.
CREATE TABLE MASTER
(
COL1 NUMBER,
[Code]....
COMMIT;Query I framed is below
select * from master where exists (select null from child where child.col2 = master.col1
group by child.col2 having count(distinct col3) =1 )
Output in above case would be 3 as for 1 there's one record with status as 'N' and for 2 there's no child record. I am on 10g.
View 2 Replies
View Related
Aug 20, 2010
I've created a query so I can easily compare two sets of data for two different instruments:
select a.CalId, a.AtName, a.NRef, a.VaLoat, a.ValTime, a.ValRing,
cvs.NRef, cvs.CalId, cvs.AtName, cvs.VaLoat, cvs.Valtime, cvs.ValRing
from CalcAttribute a, CalcAttribute cvs
where a.NRef like '438815' and cvs.NRef like '438813'
and a.CalId *= cvs.CalId
and a.AtName *= cvs.AtName
union
[Code]...
This works great - however I want to add an addtional condition, basically so it only returns where the two are not equal.
I thought I should just be able to add an extra:
and a.ValLoat *<> cvs.ValLoat
and a.ValLoat <>* cvs.ValLoat
But it doesnt seem to like this (Incorrect syntax near '<'.)
View 3 Replies
View Related
Mar 14, 2012
For a hash join statement, is it beneficial to have the join condition objects in the index as well as the objects in the where clause?
View 19 Replies
View Related
Mar 2, 2011
Create a Report based on Join Conditions.
Eg.
1st Query : Select * from Emp
2nd Query : Select * from Dept
Join the Relation from 1st Query to Second Query
Apply following conditions:
a) if comm is null then hide the corresponding row
b) If sal is greater than average salary of there dept the display above average else display below average in the place holder column.
View 3 Replies
View Related
Apr 16, 2008
I'm trying to create a relation from child block to the master block that I've created . Foreighn key is there from child to parent table.The error I get is below:
FRM-15004: Error while parsing join condition
The join condition is correct, but I'm still clueless as to what it could be.Is it a form bug?
View 7 Replies
View Related
Jun 13, 2012
i want to update record that is fetched based on join condition on form
1. made a block manually :::: EMPSAL
2. Query DATA SOURCE NAME :::: EMP a, Sal b
3. Where Clause :::: a.empid = b.empid
4. DML DATA Target Type :::: Table
5. DML DATA Target Name :::: EMP a, Sal b
6. All Columns are marked a.empid, a.empname, b.sal, b.date etc
It does not allow me to update record.
View 1 Replies
View Related
Aug 16, 2010
I receive a "FRM-15004 Error while parsing join condition" when attempting to create a relation between block1 (parent table) and block2 (child table). If I do a simple straight join the statement is accepted but if I use a Decode then it results in an error. How to successfully use a decode or a nvl in a join statement of a relation?
Results in Error:
block1.case_id = block2.case_id and
decode(block1.employee_id, null, block1.entity_id, block1.employee_id) =
decode(block2.employee_id, null, block2.entity_id, block2.employee_id)
This join is accepted but I would like to use a decode since a record can have either a employee_id or entity_id, not both
block1.case_id = block2.case_id and
block1.employee_id = block2.employee_id and
block1.entity_id = block2.entity_id
View 1 Replies
View Related
Aug 4, 2013
i have a table where there are codes of length 6 or length 12 some times, i need to add the summary of amount based on two different types of codes, one is adding the distinct codes which are of 6 char long and other sum will be based on from substr(7) till last.
create table strings ( strings_var varchar2(12),strings_amt number);
insert into strings (strings_var,strings_amt) values ('02.01',10 );
insert into strings (strings_var,strings_amt) values ('02.01_A11111',15);
insert into strings (strings_var,strings_amt) values ('02.02_A11111',15);
insert into strings (strings_var,strings_amt) values ('03.01_B11111',15);
insert into strings (strings_var,strings_amt) values ('03.02_B11111',15);
the output which i want is as below.
string value
'02.01' 10
'A11111' 30
'B11111' 30
View 4 Replies
View Related
Dec 13, 2012
I have wells that can have multiple statuses (one record for each status). I need to create a column to display a Y or N based on whether or not a given well has only certain types statuses. If all records for a given well are Susp and/or Abd, then I want to display a Y. If the well records include Susp or Abd, but also have other statuses (or do not even have a status of susp or abd) then I want to display a N.
So:
Well Status ident
12345 SUSP Y
12345 SUSP Y
12345 ABD Y
98765 SUSP N
98765 PROD N
98765 ABD N
45678 SUSP Y
45678 SUSP Y
ASDFG ABD Y
ASDEG ABD Y
TTTTT PROD N
TTTTT TEMP
View 4 Replies
View Related
