SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies

SQL & PL/SQL :: ORA 01427 - Single Row Sub Query Returns More Than One Record?

Oct 10, 2013

I am not able to update a table cloumn using below query and getting ORA 01427: single row subquery returns more than one record error.

update fuctions f set id = (Select g.fn_id from access g where g.level = 'A' )
where f.u_id = "ABC";

I understand that problem is there with inner select query.

View 3 Replies View Related

SQL & PL/SQL :: Create Record Type With Rowtype And One Field

Oct 11, 2012

using as template this table:

create table t1 (c1 number,c2 number);
CREATE OR REPLACE
TYPE REC IS RECORD (
R1 T1%ROWTYPE,
R2 NUMBER
);

I'm trying to create one RECORD type with all the columns from table T1 + one new field R2. But gives me an error.

The point to use T1%ROWTYPE and not to hardcode the columns from T1, is due to if we add a new column to T1, is created when is executed again RECORD definition and not to add the column.

View 3 Replies View Related

Forms ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.

No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.

Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.

View 4 Replies View Related

PL/SQL :: Create Record Variable That Refers Dynamic Query Assigned To Ref Cursor?

Nov 11, 2012

Just explaining what I am trying to achieve:

1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)

2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012

3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)

4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.

5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point

6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.

PS:

1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.

2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)

Code :
===================================================================================================
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)
is

[Code].....

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

Connect By Level Using Field From Record Instead Of Constant?

May 20, 2011

I am generating a start date, stop date, count and frequency from data in one of my tables.

The result is as follows:

Id,RecType,RecCode,PeriodCode,StartDate,StopDate,Cnt,Freq

10S1M6/1/200711/30/20081812
10S2M11/30/20089/30/20091012
10S2Q11/30/20108/18/201134
10L8A6/1/20079/30/200931
10L8A11/30/20108/18/201111

From this data, I need a record for each individual month,quarter,etc.:

select y.*,MonthNo,Add_Months(StartDate,MonthNo*Frequency) from (
... Code to generate data ...
) y,(select rownum MonthNo from dual connect by level <= Cnt)

This returns ORA-00904: "CNT": Invalid Identifier. I don't get an error if I use a constant:

select y.*,MonthNo,Add_Months(StartDate,MonthNo*Frequency) from (
... Code to generate data ...
) y,(select rownum MonthNo from dual connect by level <= 3)

How can I get this to work using the "CNT" value instead of a constant?

View 7 Replies View Related

Maximum Of VALUE Field Of Each Record With Different POINT-NUMBER?

Oct 21, 2011

I need to get the maximum value of the VALUE field of each record with different POINTNUMBER, then do an update on the 2nd table.

table 1
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ

table 2 (idem structure)
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ

Where POINTNUMBER is six thousand different values My query only returns me the maximum of a single record:

SELECT * FROM table1 WHERE value = (SELECT MAX (value) FROM table2);

And the update:
UPDATE table2 SET to a.value = (SELECT MAX (b.value) FROM table2 b);

so does on a single record.That needed to maximize each pointnumber different? I can use a cursor to do this easier?

Insertion should first make a one-time, and then updates every 1 hour

View 1 Replies View Related

Reports & Discoverer :: Record Field - All Consumers Pictures Not Available

Feb 23, 2012

Every consumers have a record field containing picture path. all consumers pictures are not available certificate.rdf returns rep-0108 error message.

View 9 Replies View Related

Forms :: Calling Form From Multi-record Field?

Apr 29, 2013

i have one multiple record field in frm....contain 5 field.....now i display the values...3 fields have values and two are empty...

the value of 1st field is A

2nd field is B

3rd field is C

i want if i click B it will open another form,

i want if i click c it will open another form,

View 9 Replies View Related

Forms :: Populating Values To Record Group From Multi Record Datablock

Jul 26, 2012

My procedure proc_ex is in when_validate_item trigger

I have one Multi Record data block in my form with values in its items

I need to Populate multi record block values to one Record Group using

add_group_row,
add_group_column,
set_group_char_cell to populate values to record group

Let us suppose my multi record data block looks like

item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80

how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies

View 3 Replies View Related

Forms :: Make A Field As Mandatory At Current Record Level

May 27, 2010

I have one requirement i.e i want to make a particular item as mandatory for the current record in the tabular form.

so i have written code as below:

DECLARE
lv_item item;
BEGIN
lv_item := FIND_ITEM ('XXMZ_DETAIL.QTY_ACT');
SET_ITEM_INSTANCE_PROPERTY (lv_item,
current_record,
required,
property_true);
END;

This code is not effecting qty field. If i write set_item_property built-in that item becomes mandatory.But it's not effecting at current record level.It's effecting block level.

So how can i make a field as mandatory at current record level?

View 7 Replies View Related

Forms :: Hide A Single Row Field Of Multi Record Item?

Nov 3, 2010

Consider below is a multi record block rows, i want to hide "23". Is it possible using Set_Item_Instance_Property or any other built-in is there in oracle forms to hide a single row field in a multi record block.

11 12 13
21 22 23
31 32 33
. . .

View 3 Replies View Related

Server Utilities :: Field / Record Terminator - Trailing Null Col

Oct 7, 2010

I need to load a file with fields separated by '|^|' and at end of each record has '||*||'.

So in my ctl file what do i mention ? fields terminated by '|^|' ? for the record termination wat should I say?
Should I still mention 'trailing null col' in my ctl file...?

Sample data file:
Name|^|Age|^|city||*||
john|^|33|^|||*||
james|^||^|nyc||*||
ken|^|44|^|
washington||*||

the fields are properly terminated with |^| and the records are terminated with ||*||. Is it true that a file with |^| as field terminator cannot be loaded with sqlldr?

View 3 Replies View Related

Server Utilities :: How To Associate Header Record To Its Corresponding Detail Record

May 16, 2012

I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the

LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,

[code]....

A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).

THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002

What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?

View 11 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name

Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM

View 4 Replies View Related

Forms :: Only New Record Have Saved Last Record No Longer Exist

Jun 29, 2013

I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.

View 4 Replies View Related

Forms :: Choose Lov In First Record That Won't Be Display In Second Record Lov

Sep 10, 2012

I have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...

View 4 Replies View Related

Can Create Unique Index On Subset Of Record

Apr 9, 2013

I have following records in table (T)

Idtype_cdNbrDt
1A1001/1/2013
2A101Null
3B100Null
4C100Null
5A100Null

Based on the type-cd, I need to create Unique index on Nbr & Dt columns

In above record set, I want to create Unique index on type_cd=A and not on B&C (Here i expect duplictes for a combination of Nbr & Dt)

Can we create Unique index on subset of record?

View 1 Replies View Related

WebService - How To Create Record Type And PL/SQL Table

Oct 7, 2010

I am working on a webservice call from ORacle.I have a button on my form application called verify.Wheni click on verify button , a pl.sql procedure should be invoked and that procedure will call .net webserive to validate the address , the result from the webserivce will be in xml.I have to extract the xml into some variables and return these varibles to Forms application..I am plannig to use pl/sql table to store the result from web service call.

here are the output values:

Customer_Id varchar2(20),
ErrorCode varchar2(30),
ErrorDesc varchar2(3000),
Fcount number,
FErrorCode -- this is array,-- if fcount >1 then these values will be repeted.
FErrorDesc -- this is array,
FStatusCode -- this is array,
FStatusDesc -- this is array,
Street varchar2(3000),
Street2 varchar2(3000),
Suite varchar2(20),
City varchar2(20),
State varchar2(2),
Zip_Code varchar2(10)

create record type and pl/sql table for these.I want to return pl/sql table as a out parameter to the form.

View 1 Replies View Related

Forms :: Firing Of When-create-record Trigger

Nov 5, 2012

I am navigating from a Master form to a child form. The when-create-trigger in the child form is sometimes executing and sometimes not. Not firing of this trigger is causing the child form to open without any initially assigned values. What is the root cause and also the scenarios where the when-create-trigger fires.

View 15 Replies View Related

Create Trigger So That Whenever Record In Employee Table Deleted?

Dec 7, 2009

I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.

CREATE TABLE EmployeeArchive
(EmployeeID Int, FirstName Char, LastName Char,
EMail Char, PhoneNumber Int, HireDate Date, JobID Char, Salary Int,
Commission Int, ManagerID Int, DepartmentID Char);

[Code]....

View 11 Replies View Related

SQL & PL/SQL :: Create External Table Based On Record Identifier?

Oct 21, 2011

I have a csv file which is in this format -

qwerty SCHEMATIC FILE
; Version 2007.7.1
Project,qwerty Project,,1,,7,1.5,1.5,1,1,0,,0,1,0,0,0,0,3,1,1,0,1,0,0,0,0,2,3,1,0,1,0,0,0,0,3,3,1,1,1,0,0,0,0,0,0,0,0,
abc,25150-28407.dat,,102,192,42,12632256,1,1,102,192,42,1,12632256,0,6,1,0,32896,1,0,0,0,0,,,-1,-1,-1,-1,0,0,0,-1,-1,-1
xyz, , , 0, 102, 0, 0, 0, 0, 0, 0, 0, 0, 0, , , , , , , , , , , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Product,00094416505678,19133,"24-36""X80"" FOLD DR VIA MEH",,4.5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,0,
Product,00094416502345,37154,"24-36""X80"" xyz WHITE",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0,0,
Product,00094416501111,83120,"24-36""X80"" abc WH",,5,81,3, 8421504,,0,,L.T.L. WHOLESALE.,abc,,0

Can I create an external table for loading this file based on selection of fields driven by the record identifier which is the first field of each record, except for the firt two?

For e.g In the above file, for records which say 'abc' in it's first field, I want to load the 2,3,6,8 fields delimited by a comma (the values may as well be null in those fields) and for records which say 'Product' in their first fields, I want to load 5,8,9 and 10 fields from the same file. Basically, I want to know if we can use instr function while choosing the fields line by line based on a search criteria in the file. There will be less columns in my table than the number of fields in the csv file, so I guess I got to mention 'MISSING FIELD VALUES ARE NULL' option. There is another challenge too - I have to skip loading the first two lines from the file to the table.

I have written a big pl/sql proc for doing the same, using utl_file.get_line option, but it is still untested and I would be extremely happy to believe that this can be achieved by creating an external table too.

View 11 Replies View Related

Forms :: Create Lov And Record Group Dynamically At Run Time?

Apr 10, 2013

i want to create a lov and record group dynamically at run time. i have the code in when-form-instance..

Declare
v_lov lov;
rg_id recordgroup;
gc_id groupcolumn;

[Code]....

whether i create first a lov and record group at design time or not? if i create a lov and have the recordgroup column then what is the benefit of dynamically lov.?

View 5 Replies View Related

Forms :: Query In Record Group

Mar 6, 2011

I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.

p.s: That record group is built in run-time.

View 1 Replies View Related

Forms :: Get Record Group Query

Apr 3, 2010

How can I get record group query in code and chang it ?( for example change its where clus). I need to get its Query.

the record group exists and I must get its query not make it.

and I know about Populate_Group_With_Query for changing but cant I have the query to change?

FOR EXAMPLE :
my record group query is :
select a ,b from A where A.c=1;

and I use it in an LOv.

Now i want get this record group in code and change its WHERE cluse after pressing A BUTTON How can I do it?

View 2 Replies View Related

SQL & PL/SQL :: How To Show Unique Record Only In Query

Jun 20, 2012

I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:

SELECT
'Enquiry' as Origin,
job.job_number,
enquiry.enquiry_number as Defect_or_Enquiry,
job.site_code,
central_site.site_name,
job.priority_code,
priority.priority_name,
job.target_comp_date,
'Target Completion Date required' as Due_Date
[code]....

Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.

View 11 Replies View Related

SQL & PL/SQL :: Table Type Of RECORD In A Query?

Apr 2, 2012

I am in the need of using a table type object in SQL query.

I have a package which has a spec in which I have declared :

TYPE TESTREC IS RECORD
(
RE_ID NUMBER(9),
RATING_TARIFF_NAME VARCHAR2(40),
);
TYPE TESTTABTYPE IS TABLE OF TESTREC;
TTR TESTTABTYPE;

In one of the package procedures I am collecting data into the above indicated table type object (TTR):

SELECT
RE_ID,
RATING_TARIFF_NAME
BULK COLLECT INTO TTR
FROM TESTPACKTAB;

This works fine. The values get collected into TTR and am able to print them too.

But when I :

SELECT
AA.RATING_TARIFF_NAME
INTO v_name
FROM
TABLE( TTR ) AA ;

in the same procedure immediately after the collection I get the error while running the procedure :

ORA-21700 : Object does not exist or is marked for delete.

View 5 Replies View Related

SQL & PL/SQL :: Show Same Record Twice With A Single Query?

Sep 24, 2012

I have a scenario for which I need to show same record twice.

i.e

SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul

Is this possible with a single query.

View 7 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57
2013             February     loc1          47960          3660               53                      24
2013             March          loc1          55007          78               57                      28
2013             April          loc1          54345          72               58                  77The code:

UPDATE missing_volume g

[Code]....

The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57
2013             February     loc1          47960          3660               53
2013             March          loc1          55007          78               57
2013             April          loc1          54345          72               58                   77

why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).

View 5 Replies View Related