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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Nov 22, 2011
How to update two tables in single set or single query ?
View 8 Replies
View Related
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
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
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
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
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
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
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
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
Feb 3, 2012
I want to list the tables in a schema which contains a particular value or data.
View 2 Replies
View Related
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
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
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
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
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
