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


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

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

SQL & PL/SQL :: Select * From One Table But Not All Tables In A Query

Oct 30, 2013

How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?

select *
from some_table st,
other_table ot
where st.id = ot.id

View 15 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

SQL & PL/SQL :: Alias Error - ORA-00933 SQL Command Not Properly Ended?

Mar 7, 2013

SQL> select lo.location_id,te.employee_id
2 from LOCATIONS AS lo,
3 TEST AS te
4 where te.employee_id = 169;

ERROR at line 2:
ORA-00933: SQL command not properly ended

View 2 Replies View Related

Client Tools :: Checking Users Execute Privilege On ALIAS LIBRARY

Nov 19, 2012

I want to check if a user has execute previlege on ALIAS LIBRARY,is there any table in which i can check this

View 7 Replies View Related

Select Query From Two Tables?

Jun 7, 2011

I have two tables a and b column names are id and date. Data type of date in a and b are different

table (a)

date id
10-DEC-01 2:08:39 PM 1
10-DEC-01 2:08:39 PM 2
10-JAN-02 10:10:22 PM 3
10-JAN-02 10:10:22 PM 4
10-JAN-02 10:10:22 PM 5

table (b)
date id
10-DEC-01 1
10-DEC-01 2

I need table b like this one

table (b)

date id
10-DEC-01 1
10-DEC-01 2
10-JAN-02 3
10-JAN-02 4
10-JAN-02 5

View 1 Replies View Related

SQL & PL/SQL :: PLS-00402 - Alias Required In SELECT List Of Cursor To Avoid Duplicate Column

Oct 25, 2013

I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.

for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,

[Code].....

View 6 Replies View Related

SQL & PL/SQL :: How To Update Two Tables In Single Set Or Query

Nov 22, 2011

How to update two tables in single set or single query ?

View 8 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

SQL & PL/SQL :: Primary Key Values To Query More Than 25 Tables

Oct 6, 2011

I have two primary key values based on that I would need to query at least 25+ tables(each of these tables might have both primary key columns or either one to query) to extract data for my 150 columns and load it onto my target table.

View 2 Replies View Related

PL/SQL :: Update SAME Column Name In Two Tables From ONE Query

Oct 15, 2012

is it possible to update a same column name in two tables.

I have two tables in same schema

(1)table name
pem.igp_parent
column name
igp_no.
igp_type

(2)table name
pem.igp_child
column name
igp_no.
igp_type

i want to update igp_no column in one query, how it would be possible.

View 2 Replies View Related

Use Of Query Clause For Nested Tables During Export

Aug 30, 2010

I have a query regarding nested tables while exporting. Are we not allowed to use Query clause during the export of nested table?

I am getting the error:
"EXP-00053: unable to execute QUERY on table NT_LP_COREBAL_MAINT because the table has inner nested tables
Export terminated successfully with warnings."

The exp command we are using is:
exp XXXX/XXXX@XXXX file=test1.dmp log=test1.log tables=nt_lp_corebal_maint query='where domain_id=10110307'.

View 1 Replies View Related

SQL & PL/SQL :: Function That Returns Source Tables From A Query

Oct 16, 2013

I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.

View 14 Replies View Related

SQL & PL/SQL :: MS Access Query With Linked Tables To Oracle DB

Mar 2, 2011

The code was originally an MS Access Query with linked tables to the Oracle DB however trying to remove the need for access and have it drop directly into Excel using Recordset.

SELECT
SWPRO.CASE_INFORMATION.CASEDESC,
SWPRO.OUTSTANDING_MAIL.CASENUM,
CASE_DATA_1.FIELD_VALUE,
SWPRO.OUTSTANDING_MAIL.SENTDATE,
SWPRO.OUTSTANDING_MAIL.DEADDATE
FROM

[code]....

View 20 Replies View Related

SQL & PL/SQL :: Query That Will Give List Of Views Using Three Tables?

Aug 16, 2011

I wanted a query that will give me the list of views using three tables

when i fire

select * from dba_views where text like '%SELECT%';

it is giving me error as expected number got long.

View 2 Replies View Related

Getting Query Rewrite Using MV's Built From Remote Tables?

Nov 19, 2012

Is it possible to have query rewrite using MV's built from remote tables?

11.20.2

View 1 Replies View Related

SQL & PL/SQL :: Query To Find List Of Tables Contains Particular Data In Schema?

Feb 3, 2012

I want to list the tables in a schema which contains a particular value or data.

View 2 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

create a procedure so that I could get minus query of 2 table as a result.

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

View 11 Replies View Related

Quest Toad Software - Does It Preload Tables When Running Same Query Again

Feb 19, 2009

I have a large, relatively complex query which I am in the midst of trying to optimize. I am using Toad as my SQL editor and executor. I am trying to test the execution time, but the first time the query executes is a good 5mins slower than future executions. I am wondering; does Toad "pre-load" the tables from previous querys when re-running a query?

I want to be able to run a query as if its the first time it is being run in order to truly gauge whether I am really optimizing the code or not.

View 1 Replies View Related

SQL & PL/SQL :: Insert Multiple Records In Different Tables At A Time (Using Single Query)

Jun 8, 2010

I want to insert multiple record in diff. table by using single query...

how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..

But i want to do this task by using single query....how can i do it?

View 4 Replies View Related

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

write a query to delete similar records in particular fields(columns) in different tables.

View 2 Replies View Related

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related







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