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:
DATE      AMT      BILL_AMT     BILL_DATE     BALANCE
6-Oct-11     38533 38533     6-Oct-11 38533
20-Sep-12     -6000           0
6-Dec-11     78552 78552     6-Dec-11 78552
[code]....

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


ADVERTISEMENT

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

CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);

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

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

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......

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

IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;

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

BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......

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.

I have a tab named APPROPRIATIONS it has 10 fields. Among which is PROJECT NUMBER, AUTHORIZAION DATE (CALENDER TYPE POPUP), And FUNDS CHECK FLAG.

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

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
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011

Tbale 2:Customer

Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly

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

CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),

[Code]....

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

Search For A Field

Mar 19, 2010

I'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables

View 1 Replies View Related

SQL & PL/SQL :: Add A Mask To Field

Jun 23, 2010

need to add a mask to my field. I'm trying this:TO_CHAR (IMPORTADOR.RUC, .000.000./0000-00 '00 ') IMP_RUC,

View 2 Replies View Related

SQL & PL/SQL :: Field From Two Tables?

Sep 10, 2012

I have two master tables , one is supplier master and the other customer, the requirement is , i will pass a code as IN parameter and the result it should bring the appropriate name of that code , for example if i pass a code which exists in supplier master , it should get me the supplier name for that code , if i pass code which exists in customer ,it should bring customer name.

CREATE TABLE OM_CUST (CUST_CODE VARCHAR2(30),CUST_NAME VARCHAR2(240));
INSERT INTO OM_CUST VALUES ('1001','CUST1');
INSERT INTO OM_CUST VALUES ('1002','CUST2');
CREATE TABLE OM_SUPP (SUPP_CODE VARCHAR2(30),SUPP_NAME VARCHAR2(240));
INSERT INTO OM_SUPP VALUES ('2001','SUPP1');
INSERT INTO OM_SUPP VALUES ('2002','SUPP2');

View 6 Replies View Related

Forms :: One Lov For Different Field

Feb 22, 2011

i want to call same lov for 3 diff. field.....if possible how can i call it...because while desiging lov , returns item only 1 out of 3 field..how can it possible.

View 1 Replies View Related

SQL & PL/SQL :: Update A Field

Jun 16, 2011

Trying my best to follow the posting guidelines.

There are two tables.

orderheader and orderdetails. orderheader is a master table and orderdetails is a detail table. So for every one record in orderheader table, there are many records in orderdetails table. I want to update a field in orderdetails table from a orderheader table using inner join.

update orderdetails
set orderdetails.conversion_dt =
(select orderheader.conversion_dt
from orderheader, orderdetails
where orderheader.number= orderdetails.number )

I get the following error:

Error report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

*Cause:
*Action:

What mistake am I doing here?

View 16 Replies View Related

CTX-DDL And Blob Field?

Sep 28, 2012

Using Oracle 10g2 I'm trying to create a ctx index, using CTX_DLL atributtes.

begin
CTX_DDL.CREATE_PREFERENCE ('LEXER_SINTILDES', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('LEXER_SINTILDES', 'BASE_LETTER', 'YES');
end;

drop index se.INDEX_PRONUMJNE_CTX;

CREATE INDEX INDEX_PRONUMJNE_CTX on TBL_PRONUM (MyBlobColumn) INDEXTYPE IS CTXSYS.CONTEXT parameters('sync (on commit) LEXER LEXER_SINTILDES');However, I got errors:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: LEXER_SINTILDES
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Is it possible to create this kind of indexes for blob fields? Or just for varchar field

View 1 Replies View Related

Oracle SQL Plus Inserting Into A New Field?

May 29, 2007

Ive added a new field to a table The new field is called Release_confirmation. How do I add the same value to this field for all rows ie. confirm.

So Id basically be setting Release_confirmation to confirm for all existing rows.

Should I use update or insert?

View 2 Replies View Related

11g - Dynamic Field Reference

Jul 21, 2011

I am working on an application that requires very dynamic access to data. Users will build queries through the interface and the queries will be stored in VARCHAR fields for later use. The function that later uses the query has no way of knowing the field names or data types used in the selection query.

This isn't an issue in any program language that I have used before but in this case, we want to do this in a package within Oracle rather than an external application.

how to reference a field by its location or position in the query.

We need to get the list of fields so we know what each field is named and we need to be able to get at its value dynamically as well. If possible, checking the data type would be useful too but that is less important in this case.If we were doing this in say PHP, we could simply reference the query row and use a command like...

foreach($myrow as $field=>$value)

and this would walk through each field in the row giving us the field name and its value.We need to do this same type of thing in our package.

View 1 Replies View Related

Select Field Where Results Are The Same

Feb 26, 2008

I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.

lets see .. so a count of records where widget = widget or something along those lines.

View 9 Replies View Related

Execute UPDATE From Field That May Have More Than Row

Jun 29, 2010

I am trying to execute an UPDATE from a field that may have more than row. I get ORA-01427: single-row subquery returns more than one row.

I need to set the careof column in owner table to hold the name (NAM) from the alt table if the alttype from alt is ATT. We could have more than one id and the careof names can be different.

This is my query:

UPDATE owner s
SET s.careof = (SELECT a.NAM
FROM alt a
WHERE a.taxyr = '2011'
AND a.alttype = 'ATT'
AND a.card in ('0')

[code]....

View 1 Replies View Related

Get Left Chars Of A Field?

Oct 15, 2008

I have many different file names within my table and I want to remove the .TXT extension from each one. I want to try this SQL but being a newbie in Oracle, I don't know how to say "Left" characters. "Left" is an invalid identifier.

Update TableName
Set File_Name = Left(File_Name, Len(File_Name)-4)
Where File_Name LIKE '%.TXT'

View 2 Replies View Related

SQL & PL/SQL :: Field Name Based On Calculation

Apr 19, 2010

I want to display Week No as the heading.So, I have these:

define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;

I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;

The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?

View 19 Replies View Related

SQL & PL/SQL :: Calculation On A Time Field?

Jul 5, 2012

I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.

I need to be able to determine the difference between column A and B eg.

Column A: Column B:
11:00 14:00

I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.

View 8 Replies View Related

SQL & PL/SQL :: Get MAX Length Of Column Value Along With Field Value

Oct 4, 2012

create table test_schema(
col1 varchar2(50)
)
insert all
into test_schema values ('this_is_a_test')
into test_schema values ('this_is_a_test_test')
into test_schema values ('this_is_a_test_test_xxxx')
into test_schema values ('this_is_a_test_test_aaaaaaaa')
select * from dual;

I want to get the length of the col1 value with maximum length of characters also with that field value.

i.e o/p is

this_is_a_test_test_aaaaaaaa length 28

how can I do it??

View 3 Replies View Related

SQL & PL/SQL :: Conditional Selection Of A Field

Feb 27, 2013

I'd like to select one of two fields from a table, based on which of them is greater for that row.

e.g. For every row in JOINING_TBL, I'd like to select either JOINING_DATE or REJOINING_DATE, whichever is greater. I do dream about being able to use a Java-like ternary operator in here, something like this:-

SELECT (JOINING_DATE>REJOINING_DATE?JOINING_DATE:REJOINING_DATE) FROM JOINING_TBL

However, I understand(through other fora) that this can't be used in here.

View 3 Replies View Related

SQL & PL/SQL :: Update Table Field From Another?

Mar 28, 2011

I'm trying to update a field from another field in the same table.

I got a Table with 3 fields.

CREATE TABLE CONTENT
( LID_id NUMBER(6,0) NOT NULL ENABLE,
LPRECIS VARCHAR2(4000 CHAR),
LCOMMENT VARCHAR2(4000 CHAR)
)

How to update "LPRECIS" from "lCOMMENT" only where "LPRECIS" is NULL ?I used this statement but it's wrong.

UPDATE CONTENT
SET LPRECIS=(SELECT LCOMMENT
FROM CONTENT
WHERE LPRECIS IS NULL)

View 2 Replies View Related

SQL & PL/SQL :: How To ZIP Clob Field In Oracle

Oct 7, 2013

We have oracle 10 g and a table contains a clob field . The table size is getting increased day by day . We have decided to zip all the clob data inside the table except last 1 month records.

Table : GENERIC_MESSAGE
Column:
message_id(number)
received_date(date)
message_xml(clob)

How we can do that keeping in mind this is huge data is present in table and huge transaction happened (10k transaction/hr)?

View 1 Replies View Related

SQL & PL/SQL :: Separate CSV Values In A Field?

Nov 6, 2012

create table nov06(
f_name varchar2(20),
l_name varchar2(50),
m_name varchar2(10)
)

insert into nov06 (l_name) values (aaa,bbb);

insert into nov06 (l_name) values (ddd,nnn,jr);

insert into nov06 (l_name) values (fff,mmm);

here the values in the l_name are seperated by ','.
comma seperated values represents "f_name,l_name,m_name".

I've to seperate the strings and put in their respective fields.

Quote:
e.g in second insert statement value (ddd,nnn,jr), so the values will be
f_name -> ddd
l_name -> nnn
m_name -> jr

how can I do it???

View 2 Replies View Related

SQL & PL/SQL :: Multiple Field Update

Jun 11, 2011

I have a requirement to load data into database column based on the value coming from input field.

E.g. :

Input:

EmpNo, DeptNo, Sal
1234, 10,1000
1234,20,2000
1234,30, 3000
1234,40,4000
2345,10,100
2345,20,200
2345,30,300

Output:

Emp No, Dept_10_Sal,Dept_20_Sal,Dept_30_Sal,Dept_40_Sal
1234, 1000, 2000,3000,4000
2345,100, 200, 300,0

Here we have more than 500 columns like this Dept_10_Sal, Dept_20_Sal........Dept_500_Sal.

View 32 Replies View Related

SQL & PL/SQL :: Number In Character Field

Dec 1, 2011

A column in a table contains the Numeric and Character. I Require the Numeric Value in that Column, if the Value is Character then null value is require to display.

View 4 Replies View Related

SQL & PL/SQL :: Querying Same Field In A Table Twice

Jul 5, 2012

I am trying to subtract the time from where status = Delivered from the status = Picked Up. All the data is in the same table. Let's call it table1 and I want to return the values: PTN, NAME and the time difference. Is this possible to do?

PTN NAME DATE STATUS
11014419 Joe Dickson 2012-06-25 14:55:58 Delivered
11014419 Mike Draia 2012-06-25 14:28:17 Loaded
11014419 Bob Geber 2012-06-25 13:14:31 Received
11014419 Bob Geber 2012-06-25 13:14:31 Picked Up
11014419 Bob Geber 2012-06-25 13:14:31 Printed

View 3 Replies View Related

SQL & PL/SQL :: Datapump Field Length

May 21, 2012

I have a problem to read in data from dmp file to target table.

I've created a dmp file using:

CREATE TABLE table_name
ORGANIZATION EXTERNAL
(TYPE oracle_datapump
DEFAULT DIRECTORY directory_name
LOCATION ('file_name.dmp')
) AS SELECT col1, col2, col3 from source_table;

[Code]...

The file created via db_link from target db to remote db and then the data are insterted into target db table target_table.

desc target_table
col1 varchar2(20)
col2 number
col3 number

There are no values longer than 20 characters in source table, but when I insert into target_table(col1, col2, col3) as select col1, col2 col3 from source_table; I get ORA-12899: value too large for column "target_table"."col1" (actual: 25, maximum: 20).

I gues it has something to do with how the oracle datapump stored the data in dmp file. When I select col1 from source_table where length(col1) > 20; I get two values which clearly are not longer than 20 characters.

Selected values are:

748473358           
693197674           

where the bug is hidden or any "normal" workaround?

View 17 Replies View Related

Reports & Discoverer :: Add New Field To RTF

Dec 1, 2010

I am using Oracle 11.5.10 ebusiness suite.I have modified a custom report (an rdf) that it now picks up field xx for the address rather than POH_ SHIP_ ADDRESS_LINE1..I modified the template.rtf and uploaded it using the responsibility Oracle XML publisher. However instead of getting the field value in the report. I get XX (i.e. the field name not the field value). How can I ensure that the report picks up the value?

View 2 Replies View Related







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