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


SQL & PL/SQL :: How To Extract Substring From A Set Of Strings

Jan 25, 2012

Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .

In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.

View 3 Replies View Related

SQL & PL/SQL :: Substring And Reference Table?

Mar 18, 2013

I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.

Table B is a reference table. It holds the 4 character code and the description.

I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.

SUBSTR(TableA.Code,1,4) Primary_Code,


View 6 Replies View Related

Reports & Discoverer :: Substring Of Values

Jan 2, 2012

I have a reference number of application number which is sent to 3 different address as outward number . for example application number is intimated to the office , the employee and the treasury. this number is generated as follows


in my outward report when i run it all three numbers are getting listed.

i want only P/11/10/133 to be listed and ignore the rest of the two. i tried to use SUBSTR(INOUT_NO,9), but it reads only from the left gives me only the number ie 133, 133A and 133B. i tried rtrim and ltrim but it only clears the extra space. how do i ignore the other two dispatch numbers.

View 2 Replies View Related

SQL & PL/SQL :: Substring Column Based On Condition

Oct 20, 2010

I have a column transaction Number . It has the transaction number of goods sold.. Below is the sample.

1. PIT0120029015554492215851181828221018554492R06
2. XY1029201195J05
3. YJ1039201176J01

My Query :

I need to substr the transaction number which starts with PIT to susbst (trxno,1,12) .. and anything other than PIT i need full number without substr . But when i use the above code :" susbstr(trxno,1,12) ..": it will substr entire column,. Is there any way to substr only PIT and leave others,

DB:oracle 10g

View 7 Replies View Related

PL/SQL :: Store Substring Output To A Table

Oct 23, 2012

declare Input VARCHAR(10) :='011000141'
declare rt StringOutput%rowtype;
SELECT SUBSTR(Input , SumOfLength - ColLength + 1, ColLength) FinalOutput INTO rt FROM mytable ;

I need to store the result to a temp table rt.

View 10 Replies View Related

PL/SQL :: How To Get Substring From A Column With Many Carriage Returns

Sep 21, 2012

I've got this situation.

In a not normalized table loaded with data from an excel file, I have a column containing a long string with many carriage returns so that after each of them it begins a new string I have to manipulate. I mean, in the column PERSON I've got something like this:

Software developer

Of course I have to write a Pl/Sql procedure extracting each string and I know that each string ends with a carriage return chr(13)||chr(10)

I think I should use the INSTR built-in function but I it'd be useful whether some of you had something ready.

View 15 Replies View Related

PL/SQL :: How To Select Substring In Oracle Up To More Than One Specific Character

Nov 12, 2013

How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE  ( trim the values before ^ )            121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.   

SUBSTR(p.phys_sub_grp_2_desc,INSTR(p.phys_sub_grp_2_desc, '^') +1)

View 7 Replies View Related

SQL & PL/SQL :: Syntax To Substring Only English Character And Exclude Sanskrit Characters

Nov 6, 2010

i have a column full name which has the name of customer in both English and Sanskrit alphabets. I need to susbtring only the English name and exclude the Sanskrit name .

View 2 Replies View Related

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 Not Usable In WHERE Part?

Apr 30, 2007

why this query works fine:

FROM trasa T
FROM zajezd Z

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

FROM trasa T


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:


The results are:

-------- -----------
DOD1 1
DOD2 1

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

--------- -----------
DOD1 1
DOD2 1


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


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

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

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
Status Level:      Newbie (20)
Registered:      Feb 6, 2012


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

View 3 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 :: 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

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


View 6 Replies View Related

Server Utilities :: Field In Datafile Exceeds Maximum Length For Number Field?

Apr 23, 2010

Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.

View 13 Replies View Related

Forms :: Update Field Based On Another Field Checkbox

May 3, 2013

I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.

I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.

--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)

insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);

--i want to set the value of trans_Act_qty as trans_qty

--i create one dummy or test block to keep the select all check box. for that table test script is


insert into test (C,B,A) values ('A',1,1);

--code written in select all check box which is created on test.block.


--code written in M_END_YN ( actual check boxes where i will uncheck).

:M_END_ALL := 'N';

--code written on button to update those values which are checked.


View 5 Replies View Related

How To Enable And Disable A Field Dependent On Another Field In Apex

May 9, 2013

I am New to Apex. Im using Apex Version 4.2.


The Scenario is depending upon PROJECT NUMBER the Fields are Populated including FUNDS CHECK FLAG .

But the AUTHORIZAION DATE is a MANDATORY field has to be filled by User

The Requirement is to make AUTHORIZAION DATE as MANDATORY only when the FUNDS CHECK FLAG is *"BLANK "* or *“Y”*

If the FUNDS CHECK FLAG is *“N”* then the AUTHORIZAION DATE to be made OPTIONAL.

View 2 Replies View Related

Backup & Recovery :: How To Link ASM File Alias And Database File Names

Nov 12, 2012

During a duplicate process to a new database name, rman crashed after the restore but before the switch datafile all.So now, we have under ASM the data files under the correct (new) diskgroup but v$datafile contains the previous names (and so diskgroup) and v$datafile_header is empty. RMAN is completly lost, our solution is to manually rename each file under SQL*Plus using ALTER DATABASE RENAME FILE. Unfortunately, we are using or migrating to OMF, so file names are meaningless and we are unable to associate ASM files with database files.

Any way (query or anything else) to associate the ASM files to the database files. Here's an abstract of what we have for one (small) tablespace:

Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.276.799152039
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.318.799151641
SQL> select file#, name from v$datafile where ts#=17
2 /

View 11 Replies View Related

Update Field With 1st 9 Characters Of Another Field In Same Record?

Apr 23, 2009

I need to update a field with the 1st 9 characters of another field in the same record.

View 1 Replies View Related

SQL & PL/SQL :: Updating A Date Field With Field From Another Table?

Nov 14, 2011

I have a table called Customer_Type with following fields

Customer_type ,Active_date, Inactive_date

Tbale 2:Customer


What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql

I have teh code using merge function..I want something in traditional old fashion..

The sql statements are below

type_code VARCHAR2(10),


View 5 Replies View Related

Application Express :: How To Convert Application ID To Alias

Jun 4, 2013

The first problem I want to solve is to convert the application ID in an url to an Alias name eg: helpdesk for an help desk application. Instead of referencing the apex application by id, I want to type the alias name on the url. example url: [URL]: I want this to be [URL]

View 6 Replies View Related

PL/SQL :: Same Value Until Field Changes

Jan 24, 2013

I have bills and payments for a customer, a customer may make many payments against 1 bill. I want to figure out the time taken from bill issue date to the time the balance of the customer falls to less then 10% of the most recent bill.I have managed to compute the running balance for the customer

So something like:
6-Oct-11     38533 38533     6-Oct-11 38533
20-Sep-12     -6000           0
6-Dec-11     78552 78552     6-Dec-11 78552

But I need to know when the running balance falls beneath 10% of the previous bill.

I thought a way to do this would be to keep the same bill amount in the BILL_AMT column until the BILL_AMT is not null which means that a new bill has been issued.

I can't use LAG because the number of records I am going back by increases by 1 for each row where the BILL_AMT is null.I also can't use a running total because I don't want the total for all of the records for the customer, just the total (i.e. the bill amount and all intervening nulls (which would be the bill amount)) since the last bill?

i.e. how can I keep the same value in a field as long as the values in that field are null and then adopt a new value when there is a non-null value?

View 2 Replies View Related

SQL & PL/SQL :: Delete One Particular Field Value In A Row?

Feb 22, 2010

How to delete one particular field value in a row?

Constraint: There should be no null value or zero instead of deleted value. Other values remain unchanged.

View 14 Replies View Related

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