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
ADVERTISEMENT
Nov 3, 2013
I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?
View 6 Replies
View Related
Nov 2, 2012
I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.
The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
View 2 Replies
View Related
Jul 4, 2012
I want to configure row device to usable for creating new asm diskgroup.we are using ASM. Steps to configure that row device to available for creating new diskgroup.
View 2 Replies
View Related
Jun 19, 2013
why can not we resize online redolog files instead of re-creating the same with large size and remove the older onces.2. How can we make RMAN obsolute backups as usable ?
View 8 Replies
View Related
Jun 13, 2012
I have a requirement to put together a Oracle SQL template to create re-usable DDL/DML Scripts for Oracle databases.Only the Oracle DBA will be running the scripts so permissions is not an issue.
The workflow for any DDL is as follows:-
1) New Table
a. Check if the table exists from the system/admin views.
b. If table exists then give message "Table Exists"
c. If table does not exist then execute DDL code
2) Add Column
a. Check if Column exists for a given table from system/admin views
b. If column exists in the specified table,
b1. backup table.
b2. alter table to make changes to the column
b3. verify data or execute dml script convert from backup to the new change.
c. If Column does not exist
c1. backup table
c2. alter table to add column
c3. execute dml to populate column with default value.
The DML scripts are for populating base tables with data required for business operations.
3) Add new row
a. check if row exists by comparing old values of each column with new values to be added for the new record.
b. If exists, give message row exists
c. If not exists, add new record.
4) Update existing record (We have createtime columns in these tables so changes can be tracked)
a. check if row exists using primary key.
b. If exists,
b1. deactivate the record using the "active" column of the table
b2. Add new record with the changes required.
c. If does not exist, add new record with the changes required.
View 17 Replies
View Related
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
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
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 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
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
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
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
May 10, 2013
How can i convert the time part into second. for example
in mu table i have below rows
1753-01-01 09:18:00.000
1753-01-01 09:52:00.000
1753-01-01 09:36:00.000
I want to convert the time part of each rows to number of second in different column. so for the 1st rows it is 09:18:00.000 that means 9hour and 18 min = 9*60*60+18*60.
But that is complex column.
View 14 Replies
View Related
Feb 25, 2010
I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:
SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID
However, I only need the DATABASE piece selected in my FOR loop below.
CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;
[Code]...
View 2 Replies
View Related
Jan 9, 2013
To make SQL query to before and after specific character.
Create table test(flist not null VARCHAR2(200));
First field content with below record:
FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP
to filter the above record from FLIST column thorugh sql script as below:
FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP
means remove first 5 charator and after APXXXXXXXXX.
Output of SQL query should come like below:
CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION
View 24 Replies
View Related
Apr 14, 2010
I started one packaged procedure in plsql developer yesterday..thats being run from yesterday...I wanted to know what part of the query that is being run now from the procedure.
View 23 Replies
View Related
Oct 30, 2010
I need to insert Data into a table based on particular condition, So I'm using an Insert select as follows:
INSERT INTO error_table
(error_code,cust_id,customer_name,error_message )
SELECT '34', cust_id,customer_name, 'Missing xxx'
FROM customers
WHERE cust_id = cur_cust.cust.id
AND ( NOT EXISTS ( Select .....) -- control 1
OR (EXISTS ( Select .....) --control 2
OR (NOT EXISTS ( Select .....) -- control 3
I have around 10 controls. I want these controls to run conditionally..I was wondering if this can be done using a decode based on some variable outside the query.
I want the query to run like this:
if A then execute control 1: AND ( NOT EXISTS ( Select .....)
If B then execute control 2: AND ( OR EXISTS ( Select .....)
View 2 Replies
View Related
Mar 25, 2010
In a table, I have a column in a table with the following values:
targetcol
30 WD
180 WD
70%
0
80%
180 CD
70%
0
7 WD
2 WD
0
-
-
-
-
-
-
-
-
-
where WD is working days and CD Calendar days.
I just need to extract number from above.
View 11 Replies
View Related
Feb 13, 2012
I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.
View 3 Replies
View Related
Oct 16, 2012
For an entered date, I want to hardcode the time part as 18:00:00, and rest of the date should be whatever is passed as input, how can i do this?
View 4 Replies
View Related
Jun 14, 2012
i am working on sql server developer in on of the organization. i got requirement from client that migration part from oracle 6i forms to oracle 10g and i got existing forms backup from client that is have .fmb, .mmb, .rdf and some of libraries having .pll files, but am new to oracle which form need to open from form builder first?
i tried to open .fmb file given database connection also but it is showing some libraries are missing. what should be the solution for my problem.
View 1 Replies
View Related
Mar 11, 2010
Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.
Here xxx.a will contain values like 123 or 456 like that .
View 6 Replies
View Related
Apr 30, 2013
I work with Oracle Forms and Report Builder since several weeks, but now i have something that i want to do with Report Builder.
I get string from DB (It's a paragraph) But i would like to set bold weight on a part of the string
for now, i set bold text i want to and i have created a fonction that make text bold like this
l_chaine := (SUBSTR(MyStrFromDB, InSTR(UPPER(MyStrFromDB), '[B]'),InSTR(UPPER(MyStrFromDB), '[/B]') ));
if(length(l_chaine) > 0) then
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
end if;
but it doesnt look to works..
how can i do that? And where i can do that?
View 2 Replies
View Related
Aug 31, 2010
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;
[Code]....
View 2 Replies
View Related