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.
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]........
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
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.
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].......
I have two tables as
Table LEAVE
Column Type Null Description
APP_NO Number(6,0) Not Null PK Leave Application Number
ECN Number(6,0) Not Null FK Employee Code Number
APP_Date Date Not Null Date of Application
From_Date Date Not Null Date from which the leave starts
TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
Remark VARCHAR2(100) Nullable Reason to be put if status is rejected
[code]....
What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.
For example if 3 rows are inserted in the LEAVES table as
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');
Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'
I tried with the following trigger, but is compiling with a warning (not showing what the warning is)
CREATE OR REPLACE TRIGGER leaveentitle
AFTER INSERT ON LEAVES
FOR EACH ROW
BEGIN
UPDATE LEAVEENTITLE LVE
SET LVE.SL_USED = SL_USED+(CASE
WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED'
THEN :NEW.NO_OF_DAYS
SL_UNUSED=SL_ENTITLED - SL_USED
ELSE 0
END),
[code]....
find the logic to query data based on a condition like..I am having a table dummy
USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
How to update single table column using join query
Example:
Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1
I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:
UPDATE tbl1
SET tbl1.EMPID =
(SELECT tbl2.EMPIDA FROM tbl2
WHERE LOWER(tbl1.EMAILCOL) = LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE tbl1.EMAILCOL= tbl2.EMAILCOL2 );
But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.
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
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].....
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]...
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?
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
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.
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]....
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.
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 '<'.)
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 RelatedCreate 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.
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
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?
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
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
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
I have a result-set which has 4 columns like (Region, PaymentDate, DebitAmt, CreditAmt). This result-set will always have a maximum of one month's records in it.
Suppose, imagine i have data for a month (ex, Mar 1 to Mar 31)...
Now... to aggregate (sum) the amount columns (DebitAmt, CreditAmt) in my resultset based on different date ranges, i wrote a sql like below...
Quote:
SELECT
REGION,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN DebitAmt ELSE 0 END) AS Debit_H1,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN CreditAmt ELSE 0 END) AS Credit_H1,
[code]...
My doubt is, in the above query, to aggregate two different columns based on same condition, i am checking the same condition twice...
I got a requirement to check if a particular range of values are present , if few values missing then have to add null for the values. This has to be a SQL statement.
table 1 :
fiscal_month_id, value1, value2
2010100028482848
2010110025712709.5
2010120027452721.3
2011010026052692.25
2011020026382681.4
2011030030992751
2011040027662753.14
2011050029732780.625
This is the table. the fiscal month id denotes that the values is from month Oct(20101000) to May (20110500). The fiscal year cycle for me is from OCT to SEP. Hence when we select this table, the select should retreive the below data
fiscal_month_id, value1, value2
2010100028482848
2010110025712709.5
2010120027452721.3
2011010026052692.25
[code].....
How to do this in a select statement ?
This fiscal_month_ID is actual from another table where all time level information are kept.
I have a column transaction Number . It has the transaction number of goods sold.. Below is the sample.
1. PIT0120029015554492215851181828221018554492R06
2. XY1029201195J05
3. YJ1039201176J01
My Query :
I need to substr the transaction number which starts with PIT to susbst (trxno,1,12) .. and anything other than PIT i need full number without substr . But when i use the above code :" susbstr(trxno,1,12) ..": it will substr entire column,. Is there any way to substr only PIT and leave others,
DB:oracle 10g
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012