Alias In Inner Join Query

Feb 27, 2008

In 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?

View 3 Replies


ADVERTISEMENT

How To Add Alias In Select * Query

Feb 23, 2009

I want to select all the field from the table and want to put alias for only field i m just wondering is there any way to write a query something like

select *, emp as employee
from empMaster

here i want to display all the column but only with emp column i want to put alias.

View 1 Replies View Related

PL/SQL :: Same Alias For 2 Tables / A Table Without Join in A Query?

Jan 24, 2013

In a query can we have the same alias for more than 1 table as in the following example

Select C.ContractNum, B.Billnum, B.Billamt,A.
From Contractmaster C,
Billdetails B,
Address A,
Currencymaster c,
Where B.billtype = 1
and C.Contractnum = B.Contractnum
and C.customerref = A.Customerref
and c.currencycode ='EUR'

In the above query, Table Contractmaster has an alias C --in capitals  while table Currencymaster has an alias c - in small caps ...

Is this possible in Oracle 11g ? Also i found that the table Currencymaster has no Join conditions I executed the query, without any errors!

View 4 Replies View Related

SQL & PL/SQL :: Query On Self Join

Nov 13, 2011

The 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.

View 5 Replies View Related

SQL & PL/SQL :: Query Without Outer Join

Nov 2, 2012

I 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?

View 7 Replies View Related

SQL & PL/SQL :: Table Join Query

Nov 10, 2010

I 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

View 14 Replies View Related

SQL & PL/SQL :: Update Query Using Join

Jun 26, 2012

How to update single table column using join query

Example:

Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1

View 8 Replies View Related

SQL & PL/SQL :: Join In A Query Which Has (+) Symbol At End

Oct 26, 2011

I have come across a join in a query which has the (+) symbol at the end of it.

View 3 Replies View Related

PL/SQL :: One To Many Relationship In A Query Via Join

Oct 11, 2013

I 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 Related

SQL & PL/SQL :: Convert Join Sub Query

Sep 10, 2012

I 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]....

View 6 Replies View Related

SQL & PL/SQL :: Query To Join Two Date Fields?

May 19, 2011

TABLE - 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.

View 13 Replies View Related

SQL & PL/SQL :: Join Query - Retrieve Last Or First Rank Row?

Dec 2, 2010

the 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

View 7 Replies View Related

Performance Tuning :: When To Use Sub-query And When To Use Join

Dec 14, 2010

In 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?

View 9 Replies View Related

SQL & PL/SQL :: How To Use Outer Join Condition In A Query

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

SQL & PL/SQL :: Joining A Query Instead Of Using Left Join?

Jan 20, 2011

joining 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]....

View 6 Replies View Related

No Join In Query Involving 2 Tables

May 13, 2013

I 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]....

View 7 Replies View Related

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]...

View 14 Replies View Related

PL/SQL :: Update Statement In Join Query

Nov 16, 2012

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.

View 12 Replies View Related

SQL & PL/SQL :: Update Query Based On Join Condition

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

Optimizer Behavior With Hierarchical Query And A Join?

Jul 23, 2013

The 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]...

View 3 Replies View Related

Query Optimization On Join With A View On Huge Table?

Jun 22, 2011

I 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.

View 2 Replies View Related

SQL & PL/SQL :: Cross Join Query To Remove Repeating Combination

Nov 15, 2010

I 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.

View 3 Replies View Related

SQL & PL/SQL :: Delete Rows Returned By Complex Join Query

Apr 5, 2013

How 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]....

View 6 Replies View Related

Alias Not Usable In WHERE Part?

Apr 30, 2007

why this query works fine:

SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK
FROM zajezd Z
ORDER BY Z.JMENO

But when I try to use the alias to filter the results like this (note the WHERE part):

SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK

[code]...

Then it throws an error message ORA-00904: Unknown Identifier for the column/alias POCET_ZASTAVEK?

View 2 Replies View Related

SQL & PL/SQL :: How To Add Alias Column Name To Group By

Aug 10, 2011

I have the following query:

SELECT AGENCY, COUNT(*)
FROM (SELECT A.AGENCY,SUM(A.NUM_LOGIN)
FROM GOVAGENCY A
WHERE AGENCY = 'DOD1'
GROUP BY A.AGENCY
[code].......

The results are:

AGECNY COUNT(*)
-------- -----------
DOD1 1
DOD2 1
2

The rollup value has a blank label instead I want to have a label to the rollup result also ex:

AGENCY COUNT(*)
--------- -----------
DOD1 1
DOD2 1

DEPT. OF DEFENSE 2

View 5 Replies View Related

SQL & PL/SQL :: Using Alias In Same SELECT Statement

Sep 1, 2011

I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).

Ex:

Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;

Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?

View 12 Replies View Related

SQL & PL/SQL :: Putting A Condition On Alias Name

Jun 6, 2012

Is there any way through which we can put forth a condition on Alias name instead of column name in a table.

For example: Select emp_name "Employee Name" fom dual where emp_id is not null;

Instead can there be a workaround so I can put "Employee Name" in where clause?

View 7 Replies View Related

SQL & PL/SQL :: How To Substring Alias Field

Mar 7, 2011

Is it possible to substring an alias field?

I am looking to substring the following so that the age comes back as 2 digits rather than an indefinite number

months_between (c.fatt,b.birth_dt)/12 age

View 2 Replies View Related

Reg - Handle Not Showing Alias Name?

Oct 1, 2012

My OTN account handle shows the number but till yesterday it was showing the Alias Name "ranit B".

Handle:      912545
Email:      
Status Level:      Newbie (20)
Registered:      Feb 6, 2012

[code]...

View 13 Replies View Related

SQL & PL/SQL :: Alias Used In Start By Connect With Program Gives Error

May 3, 2011

We have a query which uses start by and connect with method. this query works fine in our earlier version 10. But when we migrated to 11g, we are facing problem with the query.

Scenario 1:
------------
There is a table in Schema 1 and created a view for the same.

Table : create table alias_test1 ( A varchar2(10),B varchar(10),C varchar2(20),D varchar2(40), E varchar2(10),F number(1));
View: create or replace view alias_vw1
as select A, B,C,D, E,F from alias_test1

Values :
Insert into A.ALIAS_TEST1 (A, B, C, D, E, F)
Values ('Block1', '136', 'TOTBANK', 'Total Bank', ' ', 1);
Insert into A.ALIAS_TEST1 (A, B, C, D, E, F)
Values ('Block2', '136', 'PPRSEGHKB', 'HKB', 'TOTBANK', 2);

Now in the schema B, created a synonym for the view to access. create synonym alias_vw1 for alias_vw1@link.world

Query :
select srl_no,cde,pdesc,lvl,is_leaf,hcy
from( select srl_no,
cde, pdesc, level lvl,
connect_by_isleaf is_leaf,
Ltrim(replace(sys_connect_by_path (rpad(Trim(cde),19,' '),'~'),'~',' ')) hcy
[code]...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved