SQL & PL/SQL :: Join In A Query Which Has (+) Symbol At End
Oct 26, 2011I have come across a join in a query which has the (+) symbol at the end of it.
View 3 Replies
ADVERTISEMENT
How To Change Decimal Symbol And Digit Grouping Symbol
Jun 19, 2011Currently , my system using , as digit grouping symbol and . as decimal symbol in Oracle forms and report. Is there any way to reverse that to the whole system?
View 3 Replies View RelatedSQL & PL/SQL :: Query On Self Join
Nov 13, 2011The existing format of the records are
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
This should be modified as below.
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
[Code]..
Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.
SQL & PL/SQL :: Query Without Outer Join
Nov 2, 2012I have an attendance table in which we have empno, time_in, time_out..etc.
I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.
I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?
SQL & PL/SQL :: Table Join Query
Nov 10, 2010I have the following 2 tables.
A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);
[Code]..
Join column : object_id and parent_id.
The object id in docversion will have multiple values for element value in listofstring table.
ordinal value represents teh sequence of element value
Eg:
data for docversion:
1 23
2 34
data for LISTOFSTRING:
1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B
ouptut:
I want the output as follows
docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B
SQL & PL/SQL :: Update Query Using Join
Jun 26, 2012How to update single table column using join query
Example:
Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1
PL/SQL :: One To Many Relationship In A Query Via Join
Oct 11, 2013I have been told to create a query which should give out the same output as the UI of an exception management application . However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .
View 4 Replies View RelatedAlias In Inner Join Query
Feb 27, 2008In my Oracle 9i Schema, I have two tables:
Table One
pocOne pocTwo
2 3
2 4
1 2
Table Two
TableTwoId Name
1 Jones
2 Smith
3 Edwards
4 Camden
My SQL to fetch all records with Smith works great:
select Name from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo)
where Name = 'Smith'
Now I need to create an alias for the Name field. Here is my attempt:
select myAliasName from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo),
(select Name as myAliasName from TableTwo)
where myAliasName = 'Smith'
This attempt pulls up all the records instead of just Smith records. How I can create an alias for the Name field in my above query?
SQL & PL/SQL :: Convert Join Sub Query
Sep 10, 2012I want to convert join to subquery. how cna i do this.
FROM
TABLE_1 A
INNER JOIN TABLE_2 B
ON ( A.COL_1 = B.COL_1)
[Code]....
SQL & PL/SQL :: Query To Join Two Date Fields?
May 19, 2011TABLE - 1
CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80
TABLE - 2
DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
SQL & PL/SQL :: Join Query - Retrieve Last Or First Rank Row?
Dec 2, 2010the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
Performance Tuning :: When To Use Sub-query And When To Use Join
Dec 14, 2010In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.
So when should I use sub-query and when should I go for join?
SQL & PL/SQL :: How To Use Outer Join Condition In A Query
Jun 27, 2013How 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].......
SQL & PL/SQL :: Joining A Query Instead Of Using Left Join?
Jan 20, 2011joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
select * from
(
select i.ips,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
[code]....
No Join In Query Involving 2 Tables
May 13, 2013I saw a strange plan for one query in TESTING DB today. Although 2 tables are involved i dont see any join , NL/HJ/SMJ !! i am not facing any performance issue but curious to know what type of optimization oracle is doing here.
Query text and plan :
SELECT FIRST_NAME
FROM CAMPA.TABLE_A
WHERE NAME_ID =
(SELECT NAME_ID
FROM CAMPA.TABLE_B
WHERE ban=:b1);
[code]....
PL/SQL :: Avoid Cartesian Join In Query?
Oct 24, 2012 must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.
select dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey, dt.DebtorNo, dt.Name as "debtor Name", dt.State, cl.ClientNo, cl.Name as "Client Name", inv.InvNo, inv.PurchOrd, inv.Amt,
to_char(inv.InvDate, 'MM-DD-YY') invoice_date, to_char(ag.DateLastBuy, 'MM-DD-YY') aging_lastbuy, to_char(ag.DateLastPmt, 'MM-DD-YY') aging_lastpmt
[code]...
PL/SQL :: Update Statement In Join Query
Nov 16, 2012I'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.
SQL & PL/SQL :: Update Query Based On Join Condition
Jun 16, 2011I 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.
Optimizer Behavior With Hierarchical Query And A Join?
Jul 23, 2013The full statement is:
SELECT v.key$ FROM VERSION_TABLE v, DOCUMENT_TABLE d, CLASS_TABLE z WHERE
v.documentKey = d.key$ AND
d.classKey = z.key$ AND
z.key$ IN (SELECT zz.key$ FROM CLASS_TABLE zz
START WITH zz.name = 'esDTTemplate'
CONNECT BY PRIOR zz.key$ = zz.parentKey) AND
v.ESGROUP = 'SearchOperatorsMapping' ORDER BY d.name
Now I noticed that the subquery is never used to seed the join: indexes - if any - are used. Otherwise a full table scan is performed.In the example - if ESGROUP is indexed, then it's chosen to start the join evaluation. If not, a full table scan is performed.Is there any way to suggest to the optimizer to use the subquery in case there are no indexes - as a fallback ?
In the above example where VERSION_TABLE contains nearly two million records, the no index solution takes 60 secs. vs. less than 1 sec. in the index case.Wrapping the hierarchical query in a inline view leads to same result.
PS: the execution plan (without index) is:
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT ORDER BY | | 1 | 171 | 9 (23)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 171 | 8 (13)| 00:00:01 |
[code]...
Query Optimization On Join With A View On Huge Table?
Jun 22, 2011I have this table
create table ACTIONARI_ARH
(
actionar_id NUMBER(10) not null,
id VARCHAR2(20) not null,
id_2 VARCHAR2(20),
tip VARCHAR2(1),
nume VARCHAR2(100),
prenume VARCHAR2(100),
adresa VARCHAR2(200),
[code]....
and this view
CREATE OR REPLACE VIEW ACTIONARI AS
SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES","DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
FROM (
select
[code]....
The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.
The view is about getting only the most recent info for one person (actionar_id).
if i run a
a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.
b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.
my problem when i use this view in a join.let's assume i have another table with no more than 500 records, something like
create table SMALL_TABLE
(
actionar_id NUMBER(10) not null,
......
);
and if i run
select *
from SMALL_TABLE s
join actionari a
on a.actionar_id = s.actionar_id;
it takes like forever to process, forever means 1~3 minutes.by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.i am using oracle 10g.
SQL & PL/SQL :: Cross Join Query To Remove Repeating Combination
Nov 15, 2010I have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
SQL & PL/SQL :: Delete Rows Returned By Complex Join Query
Apr 5, 2013How Can I delete the returned two rows?
1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....
SQL & PL/SQL :: Escape Operator Symbol?
Feb 28, 20111) Can we set a different symbol other than '' for escape operator.
2) If yes, how to see the current escape operator symbol.
3) How to find out the below name with escape operator?
Employee name ----> rama_krishna_raj
SQL & PL/SQL :: Encountered The Symbol End-of-file?
May 27, 2011i am getting error for this code
CREATE OR REPLACE FUNCTION "ABS"."PROCESS_YEAR"(processYear DATE) RETURN NUMBER DETERMINISTIC IS
year NUMBER:=NULL
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
PL/SQL :: To_char Not Working / (||) Is Working With Join Query
Mar 22, 2013I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
SQL & PL/SQL :: Normal Join And Outer Join
Oct 19, 2013Lets say I have three tables t1 and t2 and t3.
SELECT * FROM T1;
Id
____
1
2
3
4
SELECT * FROM T2;
Id
____
1
SELECT * FROM T3;
Id
____
1
Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join
select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id
However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4
One way of doing that is using the not exists clause
select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)
Is there a better way of doing this in sql ?
How To Get The Column Data Consist Of % Symbol
Mar 15, 2009I want a query to get the column data , which consists of % symbol ,using like .
Sample :
The varchar data type column consit of columns 100%,100 ,90% ,77% with the Query i have to get the rows as 100%,90% ,77%. (i can get the data using in operator but i want to know how to get that using like).
SQL & PL/SQL :: Trimming Euro Symbol In Given String
Nov 23, 2012I am using the following query to trim the white space and enter character in the given string. BUt it is trimming the euro symbol also.
SQL> select regexp_replace(i_ruleexpression, '([^[:graph:]|^[:blank:]])','') into i_ruleexpression from dual ;
i_ruleexpression: Item.addChildByName("Advantage Smart 75 (-15€)")
SQL & PL/SQL :: Insert Rupee (currency) Symbol
Apr 8, 2013I want to display currency Rupee(Indian Currency) symbol in salary.
I am getting only $ symbol.
how to do it.
tell the process.
SQL & PL/SQL :: PLS-00103 - Encountered Symbol NUMBER
Jun 7, 2011I am trying to schedule a DBMA_JOB and getting the below mentioned error.
JOB:
====
declare
VARIABLE v_jobnum NUMBER;
begin
DBMS_JOB.SUBMIT(job => v_jobnum,
what => 'dbms_refresh.refresh(""CLARITY"."M_MV_INBASKET_ANALYSIS"");',
next_date => to_date("05-07-2011 13:45","DD-MM-YYYY HH24:MI"),
interval => '(ADD_MONTHS(TRUNC(SYSDATE,"MM")+4,1)+13.75/24)');
end;
[code]....