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


PL/SQL :: How To Enter Value Based On Condition In Column Without Using Trigger

Feb 5, 2013

while replicating form mssql 2005 it is entering space for null in oracle clob col.so i wanted to know.

1)can check constaint modify the content of column on which it is defined?

like i want to enter null in a column , if entered date is greater than current date else the entered date.i do not want to use triggers or client side script.

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

SQL & PL/SQL :: Grouping Based On Condition

Aug 4, 2013

i have a table where there are codes of length 6 or length 12 some times, i need to add the summary of amount based on two different types of codes, one is adding the distinct codes which are of 6 char long and other sum will be based on from substr(7) till last.

create table strings ( strings_var varchar2(12),strings_amt number);
insert into strings (strings_var,strings_amt) values ('02.01',10 );
insert into strings (strings_var,strings_amt) values ('02.01_A11111',15);
insert into strings (strings_var,strings_amt) values ('02.02_A11111',15);
insert into strings (strings_var,strings_amt) values ('03.01_B11111',15);
insert into strings (strings_var,strings_amt) values ('03.02_B11111',15);

the output which i want is as below.

string value
'02.01' 10
'A11111' 30
'B11111' 30

View 4 Replies View Related

PL/SQL :: Display Value Based On Condition

Dec 13, 2012

I have wells that can have multiple statuses (one record for each status). I need to create a column to display a Y or N based on whether or not a given well has only certain types statuses. If all records for a given well are Susp and/or Abd, then I want to display a Y. If the well records include Susp or Abd, but also have other statuses (or do not even have a status of susp or abd) then I want to display a N.


Well           Status       ident
12345         SUSP          Y
12345         SUSP          Y
12345         ABD           Y
98765         SUSP          N
98765         PROD         N
98765         ABD           N
45678         SUSP          Y
45678         SUSP          Y
ASDFG         ABD           Y
ASDEG         ABD           Y
TTTTT         PROD       N
TTTTT         TEMP

View 4 Replies View Related

SQL & PL/SQL :: Aggregation Two Different Columns Based On Same Condition?

Apr 3, 2011

I have a result-set which has 4 columns like (Region, PaymentDate, DebitAmt, CreditAmt). This result-set will always have a maximum of one month's records in it.

Suppose, imagine i have data for a month (ex, Mar 1 to Mar 31)...

Now... to aggregate (sum) the amount columns (DebitAmt, CreditAmt) in my resultset based on different date ranges, i wrote a sql like below...



My doubt is, in the above query, to aggregate two different columns based on same condition, i am checking the same condition twice...

View 2 Replies View Related

SQL & PL/SQL :: Add Null In Select Based On Certain Condition?

Oct 20, 2011

I got a requirement to check if a particular range of values are present , if few values missing then have to add null for the values. This has to be a SQL statement.

table 1 :

fiscal_month_id, value1, value2

This is the table. the fiscal month id denotes that the values is from month Oct(20101000) to May (20110500). The fiscal year cycle for me is from OCT to SEP. Hence when we select this table, the select should retreive the below data

fiscal_month_id, value1, value2


How to do this in a select statement ?

This fiscal_month_ID is actual from another table where all time level information are kept.

View 4 Replies View Related

SQL & PL/SQL :: Dataset From Two Sets Of Tables Based On Condition

Nov 26, 2010

have two queries that will return same columns from two different set of tables ( column mapping has been taken care of). The return type is out ref cursor. (P_SUPPLY_REORDER )

Query 1-SO
so.fk_CUST_ID as CUST_ID,

Query-2 Xcom
null as sMO_NO,
xso.created_date as SPLY_ORD_DT,
xso.fk_cust_id as cust_id,
cust.cust_po_no as cust_PO_NO
,(sta.SHIP_TO_ATTN_FIRST_NAME||''||sta.SHIP_TO_ATTN_LAST_NAME) as attention_name,

Now the requirement is
One of four conditions are possible for each Supply Reorder Number:

. Both table queries return no records- Populate all the P_SUPPLY_REORDER output fields with nulls
. SUPPLY_ORDER returns a record, but XCOM_ORDER_HEADER returns no records
- Populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
. SUPPLY_ORDER returns no records, but XCOM_ORDER_HEADER returns one record
- Populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
. SUPPLY_ORDER returns a record, and XCOM_ORDER_HEADER returns a record; find out the latest order by comapring max(SPLY_ORD_DT)
- If the latest order is in SUPPLY_ORDER, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If order dates are equal from both join results, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If the latest order is in XCOM_ORDER_HEADER, then populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.

Question is how can we switch over the queries to pull respective dataset based on these conditions ( checking that which table join is going to return a row and then based upon latest order if both tables return a row) and all this logic as part of single SQL statement that is returned as OUT Ref Cursor.

View 7 Replies View Related

SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

ct_id and limit_id is match column used for mapping.

now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.

how to achieve the same using sql/oracle query.

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

SQL & PL/SQL :: To Delete Data From Tables Based On A Condition

Feb 3, 2013

i have a list of 500 tables. I want to delete data from those tables based on a condition. (Data before 2008 year needs to be deleted). Each table has a column based on which data needs to be deleted. Provide a code which does this efficiently and fast. Bulk collect is preferable.

View 17 Replies View Related

SQL & PL/SQL :: Create Multiple Records Based On Condition

Oct 10, 2012

(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');


-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ

The requirement is the records should be split based on below conditions

1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM

This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.

Expected Output.

------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10

10 20 AB
21 25 AB

12 22 PC
23 32 PC
33 35 PC

22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM

View 7 Replies View Related

Forms :: How To Retrieve Only That Data Which Is Based On Given Condition

Jun 22, 2011

,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.

View 2 Replies View Related

XML DB :: Increment Counter In Loop Based On Condition?

Jan 24, 2013

I want to increment a counter in a loop based on a condition.

Here is how my xml looks like

<resultset id=1>


I need to look at each and every resultset and check if the value is >400 and if it is then display some text. Something like this

Yes, there is a value greater than 400

In my XSL, I declared a variable called count with value 0. I created a for-loop which goes through these results. Then inside the loop there is a condition to cehck if the value is > 400. If the value is >400, then the counter is incremented.

<xsl:variable name="Count" select="0"></xsl:variable>
<xsl:for-each select="/results/resultset">


There is a problem with the above code is that result is like this

Yes there is a resultset with greater than 400

Is there a way I can just remove the character 1 from the output? Or is there a better way to increment?

View 3 Replies View Related

SQL & PL/SQL :: Find Logic To Query Data Based On A Condition

Aug 18, 2013

find the logic to query data based on a condition like..I am having a table dummy

-------- --------- ------------------ ------------

The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.

View 22 Replies View Related

PL/SQL :: Assign Number To Row Based On Condition Analytic Function

Oct 24, 2013

Oracle 11g databaseidval1val2100a110b120c200a220b 
WITH input AS
(SELECT 1  id


input; Output:idval1val2assigned_number100a0110b0120c2200a0220b1 The dense numbering sequence should be assigned to each row based on id and val1 column. For a given Id, the numbering only starts after val1 > 1 till then the assigned_number will be zero.

View 1 Replies View Related

PL/SQL :: Updating CSV Values In Oracle Table Based On Condition

Aug 27, 2013

I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Update Table1 Based On Condition Of Values In Table2

Feb 20, 2012

I have two tables as

Column Type Null Description
APP_NO Number(6,0) Not Null PK Leave Application Number
ECN Number(6,0) Not Null FK Employee Code Number
APP_Date Date Not Null Date of Application
From_Date Date Not Null Date from which the leave starts
TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
Remark VARCHAR2(100) Nullable Reason to be put if status is rejected

What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.

For example if 3 rows are inserted in the LEAVES table as
(1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
(2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
(3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');

Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'

I tried with the following trigger, but is compiling with a warning (not showing what the warning is)


View 9 Replies View Related

SQL & PL/SQL :: Join For Bringing Master And Child Records Based On Condition?

Jul 30, 2013

The attachment contains the table info. The condition is that when there are childer for the master ,the master record shd be negated . Excuse me if this very easy. Example -DEL HAS children so the record with DEL and DEL SHD not be in the result query. Example -RAG HAS NO children. so the MASTER record should be taken.



My expected output

View 3 Replies View Related

Application Express :: Default Value Of Radio Button Based On A Condition?

Oct 1, 2013

i have used radio button under dynamic conditions for an interactive report.eg: radio button values are like a) front end b)middlware c)back end.so based on the selected value of the radio button, interactive report appears. and each fields in radio button have separate set of users. now my issue is,based on the user logging in, the default value should set up while the page is loaded...[i.e... if a front end user logs in, the default value of tat radio button shd be front end...] 

View 5 Replies View Related

Forms :: Fetch Single Record From Multiple Records Based On Condition

Jan 27, 2012

I have made a travel booking system which comprises of 3 forms

1)Travel Booking form
2)Reservation Form
3)Cancellation Form

Under one booking number i can add multiple users in which they can have there multiple travels.

Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.

What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.

For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.

View 9 Replies View Related

Server Utilities :: Export / Import Tables Based On Date Condition?

Jul 26, 2012

i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:

exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";

is it possible to do like this, that it should prompt you to enter the start and end date?

then my import script:

imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;

the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.

View 12 Replies View Related

Application Express :: Navigate From A Report To Different Updatable Forms Based On Condition?

Oct 11, 2013

navigate from interactive report to different forms.. 

View 10 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically


500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494

View 1 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related

SQL & PL/SQL :: Trigger Which Changes Content Of One Column Based On Information Within Another Column

Sep 10, 2010

I am trying to create a trigger which changes the content of one column based on the information within another column.

For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.

View 23 Replies View Related

SQL & PL/SQL :: Join Condition - One Column Vs Many?

Apr 8, 2012

create table ptab(pid number);
create table ctab(aphone varchar2(20));
drop table ctable
create table xtab(pid number,phone varchar2(20), tel1 varchar2(20), tel2 varchar2(20), tel3 varchar2(20))

insert into ptab values(1);
insert into ptab values(2);
insert into ptab values(3);
insert into ptab values(4);


i have 3 tables, xtab, ctab and ptab

join condition

ptab.pid = xtab.pid

what I want the join between xtab and ctab is, aphone should match with phone, and then tel1, then tel2, and then tel3, (if phone, tel1, tel2, tel3 are not null in that order only), if aphone matches with any of these,then just print that particular pid, important point is, aphone should be checked against phone, tel1, tel2, tel3 that order only

so the results should simply print

5 should not be printed here because for pid 5 in xtab, none of the phone numbers match with aphone of ctab

I tried this:
DECODE (ctab.aphone,
xtab.phone, 1,
xtab.tel1, 1,
xtab.tel2, 1,


but i cannot join ctab and ptab, i dont want to use intersect etc, because we are looking at millions of rows here

View 3 Replies View Related

SQL & PL/SQL :: Take Sum Of Data From One Table But Three Different Condition With Three Column

Oct 5, 2010

Now am on a need to take sum of data from one table but three different condition with three column.I give example Lets consider I have a table called Sales in which it has three columns


There are 3 different flag for status A,B & C.I want display data in the following format in five columns.

Dept Status_A Status_B Status_C
1 30 50 20
1 10 60 30

total 40 110 50

But data is stored in the table in rows like
1 30 A
1 50 B
1 20 C like all rows.

For this I wrote union operation and sum.Am
creating a view for this with there columns dept,Sales_A,Sales_B & Sales C for sum of data.My view is

select dept,Sum(sales_amt),0,0 from sales where status='A' group by dept union
select dept,0,sum(sales_amt),0 from sales where status='B' group by dept union
select dept,0,0,sum(sales_amt) from sales where status='C' group by dept

Am assigning 0 for other two status of data because i want the data to be displayed on different columns based on row level condition.The view will return data like

Dept Sales_A Sales_B Sales_C
1 30 0 0
1 10 0 0
1 0 50 0
1 0 60 0
1 0 0 20
1 0 0 30

So finally am using sum function again and selection total sum of value for each status from this view.So by using union operation how can i display data from different condition of data into seperate columns.

View 2 Replies View Related

SQL & PL/SQL :: Selecting Value Of Column Into Different Variables Depending On Condition?

Aug 10, 2011

I need to select the value of a column into different variables depending on the condition.

As in, I have


I need to get the Rention days into the 4 variables v_color_file_type_code, v_size_file_type_code, v_buyer_file_type_code AND v_uom_file_type_code Depending whether the FILE_TYPE_CODE is 'CLR' or 'SIZ' or 'ADB' or 'UOM'

View 3 Replies View Related

PL/SQL :: Match Condition - Add Additional Column Indicator

Jan 9, 2013

I would like add an additional column to the data below:

create table test(
id number
cust_num varchar2(5));



001, AODER, 'Y'
001, BODER, 'Y'
001, CODER, 'Y'
001, DODER, 'Y'
001, 'NONE', 'Y'
001, 'NONE', 'Y'


I would like to add an additional column indicator (Y or N) to specify which ID's do not contain all records of 'NONE'. There can be an occurrence of 'NONE' as long as there is another cust_num different to 'NONE' These should be marked as 'Y' but in cases where all the ID's cust_num = 'NONE' only then these should be marked as 'N'.

View 5 Replies View Related

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