SQL & PL/SQL :: Null Value On Field Use In Join Condition?
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
ADVERTISEMENT
Mar 16, 2011
I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
View 9 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
Aug 23, 2013
The below query is not performing good.
SELECT * FROM wedb_master
WHERE c_group IS NOT NULL;
The table is having 200000 records.
I have index on c_group but not used. FTS is happening.
how improve the performance of IS NOT NULL predicate.
View 2 Replies
View Related
Oct 20, 2011
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.
View 4 Replies
View Related
Aug 23, 2010
How can I use OR condition in sql to check whether if the 6 col values in a row is NULL, I need something like below, but idk the syntax of checking if either of the mentioned cols is null using an or condition.
select a.mid
FROM table_a a
JOIN table_b b
ON a.dept = b.dept
JOIN table_c c
ON b.eid = c.eid
WHERE a.tid = :tid
AND (a.i1 or a.i2 or a.i3 or a.n1 or a.n2 or a.n3 or a.t1 or a.t2 or a.t3 ) IS NULL
View 1 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
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
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
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
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
View Related
Oct 16, 2012
I am using left outer join to fetch PRSN_KEY .I need to find null values in B.PRSN_KEY. I am using below query but its giving me 0 count.
select count(*) from (
Select A.PRSN_KEY AS AKEY,B.PRSN_KEY AS BKEY from CD03955P.H_CM_EEST_EEOR A LEFT JOIN CD03955P.H_CM_EEST_EEOR B
ON
A.PRSN_KEY =B.PRSN_KEY
where
A.CAT_ID=111
AND
A.DATA_SOURCE='PEN_CO'
AND
B.CAT_ID = 1 and B.DATA_SOURCE ='PEN_EEST'
AND B.CAT_CD IN ('ACTIVE','LOA','LOAWP','LOAMLP','LOAMLN')
AND B.EFBEGDT < A.EFBEGDT
)
where BKEY IS NULL
View 8 Replies
View Related
Aug 5, 2011
I want to replace data in a field under a certain condition. I want this in the Select.
EG.
Data:
ID - Street
1 - Klapstraat
2 - Groenstraat
3 -
The result I want is an x if street is not null and empty if the street is null
View 4 Replies
View Related
Apr 1, 2010
I need to list a count of rows where a DATE field is not null and group those counts by day.
Here's my sql so far...
SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;
Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?
View 2 Replies
View Related
May 27, 2013
I have already done auto increment by making sequence and trigger. but now the problem is when i am trying to retrieve data from that table it returns all data perfectly but the primary key that is my auto increment value shows blank.I am facing this problem with join query, there 4 table left joined in my query. But when I remove join from my query then it shows that value.
But i need that value in my join query.So, what is the problem and what can I do?And other thing is when I apply this query in Oracle SQL Developer, it works perfect.
My Query:
return $this->db->query("select * from TBL_EMPLOYEE_BASIC left join TBL_EMPLOYEE_DETAILS on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_DETAILS.EMPL_ID left join TBL_EMPLOYEE_EDUCATION on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EDUCATION.EMPL_ID left join TBL_EMPLOYEE_EXPERIENCE on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EXPERIENCE.EMPL_ID where
[code]...
View 2 Replies
View Related
Oct 7, 2010
I need to load a file with fields separated by '|^|' and at end of each record has '||*||'.
So in my ctl file what do i mention ? fields terminated by '|^|' ? for the record termination wat should I say?
Should I still mention 'trailing null col' in my ctl file...?
Sample data file:
Name|^|Age|^|city||*||
john|^|33|^|||*||
james|^||^|nyc||*||
ken|^|44|^|
washington||*||
the fields are properly terminated with |^| and the records are terminated with ||*||. Is it true that a file with |^| as field terminator cannot be loaded with sqlldr?
View 3 Replies
View Related
Oct 18, 2010
I am posting the below query:
SELECT PEA.INCURRED_BY_PERSON_ID AS PERSON_ID,
PEA.EXPENDITURE_ENDING_DATE AS WEEK_END_DATE,
CASE
[Code].....
The explain is below:
SELECT STATEMENT ALL_ROWSCost: 48,287 Bytes: 18,428,818 Cardinality: 297,239
3 HASH JOIN Cost: 48,287 Bytes: 18,428,818 Cardinality: 297,239
1 TABLE ACCESS FULL TABLE PA.PA_EXPENDITURES_ALL Cost: 2,964 Bytes: 3,506,094 Cardinality: 194,783
2 TABLE ACCESS FULL TABLE PA.PA_EXPENDITURE_ITEMS_ALL Cost: 43,425 Bytes: 26,637,468 Cardinality: 605,397
View 9 Replies
View Related
Sep 13, 2012
I have a BI Suite implementation on one server. And a database with ApEx on another server.
I want to call a BIP report from within my ApEx application using the webservices (runReport) available in BIP 11g.
I used soapUI to test my webservice. Result : OK
When called from within ApEx, ApEx gives me a succes message but the report isn't generated. Instead the console on the BIP server shows the following error:
<Sep 12, 2012 10:28:37 PM BST> <Error> <org.apache.axis.encoding.ser.BeanPropert
yTarget> <BEA-000000> <Could not convert null to bean field 'sizeOfDataChunkDown
load', type int>
'sizeOfDataChunkDownload' is a field of the webservice that is left empty. That indeed is the only difference between my soapUI test and the ApEx situation. In soapUI I removed all empty fields. In ApEx this does not seem possible...
Some extra information:
- the webservice is created on this WSDL : /xmlpserver/services v2/ReportService?wsdl
- it's defined as a SOAP v2
- no basic authentication
- the reports are defined in the BIP environment; not in ApEx
View 1 Replies
View Related
Dec 3, 2010
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
View 3 Replies
View Related
