Forms :: How To Sum Rows In 6i

Mar 10, 2012

i want to sum different rows and want get wum in nest column.

for example i have fee column and there is some different student fee in specific month for example(jan).i want to sum this fee against the month of Jan.

View 4 Replies


ADVERTISEMENT

Forms :: FRM-40654 On Some Rows Only?

Jun 27, 2013

I developed a small app using forms, which queries a table in our DB and allows the user to edit some of the values in a grid-like format. Sometimes I get this FRM-40654 error when trying to key in a value in a column. Sometimes means that it may happen or not happen at all, but also that it happens on some of the queries rows and not on others. IN one case I have 4 rows and it happens on all but the 2nd The table has a small trigger on it:

CREATE OR REPLACE TRIGGER MFI_CT_T_TRG1
before UPDATE ON MFI_CITY_TAX_T
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
:new.modified_on := sysdate;
:new.modified_by := pms_p.logged_uid;
END;
/

But I tried already to remove it and it didn't work.I set the DML Returning Values of the block to Y, still the same.

View 2 Replies View Related

Forms :: Update Random Rows In Table?

May 24, 2010

I have this requirement in Oracle FORMS ver 6i where I populate some records in a table in a datablock and display them on the screen. A facility needs to be provided to the end users to select the records randomly by just entering the count of the records in a non-d/b text field. The number entered in the count field will decide how many records are to be selected randomly.

I tried using the query -

UPDATE <table a>
SET <col 1> = <value 1>
WHERE rowid IN (SELECT rowid FROM (SELECT * FROM <table a> ORDER BY DBMS_RANDOM.VALUE)
WHERE <col 2> = <value 2>)
/

which I found on the link [URL] but while compiling, the Form gives a compilation error for the ORDER by clause - i'm sure there's no syntax error in the way I have written it...

View 5 Replies View Related

Forms :: Convert Rows Into Columns As Tabular?

Mar 8, 2010

how i can convert rows into columns as tabular form.

I have two table : om_supplier
supp_code
supp_name

om_supplier_term
st_supp_code
terms

like if i want to make query form from supp_code to supp_code the output is comming

[code]
select supp_code,terms from om_supplier,om_supplier_term
where supp_Code = st_supp_code
[code]

supp_code terms
1 90days
1 180days
1 120days

i want the output like

supp_code
1 90days-180days-120days

how i can achieve this in form.

View 8 Replies View Related

Forms :: WUT-113 - Too Many Rows Error Using Webutil To Upload Doc Or PDF

Jun 1, 2010

I'm new to using Webutil to upload and view files and am receiving the error msg 'WUT-113 Too many rows matched the supplied where clause' The process works fine for uploading photos but not when I try and upload documents and pdf files.

The code example (for documents) is outlined below but think that the issue must be to do with some sort of incorrect Webutil configuration on my machine.

declare
vfilename varchar2(3000);
vboolean boolean;

[Code]...

The :blob_id column is populated on the form with the when-create-record trigger. Why is the program at time of upload to the db saying that more than 1 row is being written to the db table?

View 4 Replies View Related

Forms :: Dynamic Form According To Rows In Another Table?

Oct 28, 2010

i want to create a form & show some columns accordingly rows entered in another table

for eg.

a table name temp have three columns (no,name,date) in name it shows some rows like
a
b
c
d

i want to create a new dummy form which shows
a b c d

if i remove b from temp table then new form shows only
a c d

is it possible

View 2 Replies View Related

Forms :: Copy Rows From Excel And Paste In 6i Program?

Sep 28, 2007

I want to copy some rows in one column from Excel and Paste it in a Oracle Forms Progam. Normaly with the clipboard. But my Oracle Forms 6i Program copy alle the rows in one Field, separated with a | character. But I need it in several rows.

View 12 Replies View Related

Forms :: ROWNUM Returns NULL Rows When Value Greater Than 1

Dec 9, 2011

I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:

SELECT on_time
INTO on_time2
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date
WHERE ROWNUM = 2;
--RETURN NULL

I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query

SELECT on_time
INTO on_time2
FROM (SELECT on_time
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date)
WHERE ROWNUM = 2;

I can't move forward in my form until I figure out why this is not returning records

View 4 Replies View Related

Forms :: Handle Huge Number Of Rows In Data Block

Jun 14, 2011

I need to read a huge number of rows, say in lakhs and then need to populate it in data block. Since it is having huge data am never able to run the form. it hangs after some time. when i test with few rows it is working. so no problem in coding.

View 4 Replies View Related

Forms :: Tabular Form - Restrict User To Enter Only 10 Rows

Dec 18, 2012

i have master and detail form. In detail(tabular form) i want to restrict user to enter only 10 rows.if he tries to go for 11th row he shud get a message (Only 10 records can be created at a time).

View 3 Replies View Related

Forms :: ORA-01422 / Exact Fetch Returns More Than Requested Number Of Rows

Aug 18, 2010

ORA-01422: exact fetch returns more than requested number of rows.

I receive this error because i tried to introduce the below coding in a post query of the block.

begin
select supplier_cd into :fin_ex_rev_head.vendor_code
from fin_ex_rev_receipts
where receipt_date
between :keyblock.receipt_date

[code].....

View 10 Replies View Related

SQL & PL/SQL :: Compare Current Rows With Previous Rows

Apr 12, 2013

The Table having 3 columns

col0col1 , col2
P112
P112
P102
P113
P115

I want to retrieve the changes rows only.

Output like
col0col1 , col2
P112
P102
P113
P115

View 5 Replies View Related

PL/SQL :: Select 4 Rows And Then Next 4 Rows?

May 27, 2013

in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?

I have fifty rows inside the table

select * from student where rownum between 0 and 4 order by stuid ->it work

select * from student where rownum between 5 and 9 order by stuid -> did not work

what is the correct way to do it ?

View 10 Replies View Related

SQL & PL/SQL :: Subdivide Rows Into Rows

Apr 10, 2013

drop table test
/
create table test
(
lib varchar2(100)
)
/
insert into test values ('111/aaa/bbb/ccc');
insert into test values ('222/aaa/bbb/ccc');
insert into test values ('333+444/aaa/bbb/ccc');
insert into test values ('333/aaa/bbb/ccc');
insert into test values ('222+333+444/aaa/bbb/ccc');
insert into test values ('222+333+444+555/aaa/bbb/ccc');

I want to get this :

lib count
--------------- -----
111/aaa/bbb/ccc 1
222/aaa/bbb/ccc 3
333/aaa/bbb/ccc 4
444/aaa/bbb/ccc 3
555/aaa/bbb/ccc 1

View 4 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

Oct 6, 2010

I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query

MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762

and I need to display this as

COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762

View 3 Replies View Related

SQL & PL/SQL :: Put Two Rows Into One Row?

Sep 14, 2013

We are using PL/SQL Release 11.2.0.2 .I would like to pull a query with each student each day an attendance record.Our database setup an AM and PM Period for all elementary students. I will pull if they absent both periods(AM, PM), then count that as one day absent.The hard part is I need to put the AM absent code and PM absent code - which is basically to put two records for each student's AM and PM absent code into one row.

Below is the query I use, but it violates the key of database, for PK is studentid+ attendance date. My query result turns out for some students they have different attendance code in AM vs PM, there are two records returned.

SELECT DISTINCT TO_CHAR(S.Student_Number) ,
TO_CHAR(Am.Schoolid) ,
Sps_School_Year('C') ,
TO_CHAR(Am.Att_Date,'MM/DD/YYYY') ,
(

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Best Way To Get Several Rows From SCD For Each Key

Nov 20, 2012

We have large (millions of records) Slow changind dimension (SCD) type 2 (see "Creating another dimension record " URL>.....We need to get several rows from this SCD for each key (AGREEMENT_ID) in a SQL query - to join to facts table and get several data points of each agreement (on several different points in time) stored in SCD.Here is SCD table structure:

CREATE TABLE AGREEMENT
(
"AGREEMENT_ID" NUMBER(*,0) NOT NULL ENABLE,
"ACTUAL_DATE" DATE NOT NULL ENABLE,
"ACTUAL_END_DATE" DATE NOT NULL ENABLE,
"OPEN_DATE" DATE NOT NULL ENABLE,
"LIMIT" NUMBER(23,8)
--++ a lot of other fields not needed for this task ....
CONSTRAINT "PK_MD_AGREEMENT" PRIMARY KEY ("AGREEMENT_ID", "ACTUAL_DATE") USING INDEX
)

The 1st simple approach would be to join facts to SCD as many (N) times as many different points of time you need - resulting in N Full Table Scans for SCD:

select ...
from fact,
AGREEMENT agr1,
AGREEMENT agr2,
AGREEMENT agr3
where
fact.AGREEMENT_ID = agr1.AGREEMENT_ID and agr1.open_date between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr2.AGREEMENT_ID and :dateBOP between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr3.AGREEMENT_ID and :dateEOP between actual_date and actual_end_date

2nd approach: 1 Full Table Scan for SCD + group by:

select ...
from fact,
(
Select AGREEMENT_ID,
max(case when open_date between actual_date and actual_end_date then LIMIT end) LIMIT_At_Open_DATE,
max(case when :dateBOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_BeginOfPeriod_DATE,
max(case when :dateEOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_EndOfPeriod_DATE

from agreement

-- ++optionally WHERE for those 3 dates, but possibly with no effect on non-partitioned table? Or WHERE to put less data on MAX() input (3 row for each agreement instead of 4...1000 without WHERE?)

group by AGREEMENT_ID
) agr
where fact.AGREEMENT_ID = agr.AGREEMENT_ID

View 2 Replies View Related

PL/SQL :: ANY ALL When No Rows

Jun 5, 2013

Simple question, Why comparison operator ANY returns FALSE if no rows returned, and why operator ALL return TRUE if no rows returned? I dont know is this some kind of language or math assumption or is this just oracle rule?

View 18 Replies View Related

Converting 2 Rows Into 1

Aug 16, 2007

i have 2 tables

A B
id a.id
name
type

There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:

id male_name female_name
1 paul paula

the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.

View 2 Replies View Related

Throughput Rows Per Second

Nov 7, 2012

I would want to know how can we predict how many rows are fetched per second for a particular query. What are the factors which are responsible for this.hoe does this whole process of fetching records from Source happens. Like when a query is fired how does it try to access the table and fetch records and how what are the factors whichc are responsible for this and how can we predict how many rows can be fetched per sec

View 8 Replies View Related

SQL & PL/SQL :: How To Get Reduplicate Rows

Jul 12, 2011

There are many reduplicate rows in a big table,what is the best way to get the reduplicate rows?

View 5 Replies View Related

SQL & PL/SQL :: Rows Into Column

Mar 18, 2011

I have following table.

CREATE TABLE ORAFAQ
(
DPT NUMBER,
EMP NUMBER,
SAL NUMBER
)

[Code]....

DPTEMPSAL
1110
12100
131000
241500
25100
26500
27100

but i need data like

Dept
1
1 2 3 --3 columns show each employee
10 100 1000 --3 columns show salary below each employee
2
4 5 6 7
1500 100 500 100

View 1 Replies View Related

SQL & PL/SQL :: How To Commit For Every 500 Rows

Jul 10, 2010

How can we commit for every 500 rows in pl/sql block.

begin
for i in 1 to 10000
loop
Insert into t1 values(i);
commit;
end loop;
end;

Here I am commiting after all the rows are inserted ,but i want to commit for every 500 rows are inserted .

View 7 Replies View Related

SQL & PL/SQL :: All Rows Value To Single Row

Jul 15, 2011

i want to all rows value into single rows.

for example;

Input data:
Sec_SSC_ID Column_nameAs of dateOld valu New Value
IBM Mat_dt 10/10/20101/1/2001 1/1/2002
IBM Bid Market 10/10/201075 85
IBM asset_nm 1/1/2011International IBM
MSFT asset_nm 1/2/2011Microsoft Intel
MSFT Bid Market price 1/1/201189 90

Output data

I have attached sample output file with it.

how to get this via sql?

View 8 Replies View Related

SQL & PL/SQL :: Rows From Each Table In DB

May 5, 2011

Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding

system tables ..

how can i write query for this.

View 2 Replies View Related

SQL & PL/SQL :: Multiple Rows Into One Row

Apr 10, 2011

tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.

Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.

CREATE TABLE Person_Lang
(
Person_ID NUMBER NOT NULL,
Language_ID NUMBER NOT NULL,
Contact_Name VARCHAR2(255 CHAR),
Main_Phone VARCHAR2(255 CHAR),
Secondary_Phone VARCHAR2(255 CHAR),

[Code]...

Data Returned from Select is:

1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown

Ideally what is required back is:

1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown

View 7 Replies View Related

SQL & PL/SQL :: Get Previous Value Of Rows?

Dec 29, 2010

How to get the previous value of row with calling function to add value in SELECT statement for the row value.

Consider the example Table A1 having column a with values 1,NULL,NULL,NULL

SELECT CASE WHEN a IS NULL THEN (prev_row_value+function_return_Value) ELSE a END as A from A1

And my result-set should be like

a
----------------------
1
1+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Below is sample code but doesn't fulfill my criteria

[code]....

Output is

A A2
---------------------------
1 1
3
3
3

View 8 Replies View Related

SQL & PL/SQL :: Convert Many Rows To One

Sep 17, 2012

I use Oracle 11g and a Middleware which sends the SQL Statements to the database. That's why I can't send you loggings.

However, I' got this SQL:

With CTE
AS
(
select * from
data A2
WHERE
A2.ID ='536031'
AND
A2.AttrID = '6'
)

That code gives me the following result:

ID.........Version....AttrID.......group
536031......3.........6..............test5
536031......3.........6..............test6

What I try to do. I want to get all groups. The Groups should be in one column...the the result must be the following:

ID.........group
536031...test5, test6

In SQL server I always used

SELECT ID,
STUFF((SELECT ',' + [group] FROM CTE WHERE ID = c.ID ORDER BY [Version] FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ID FROM CTE)c

to achieve that. Is there a similar function in oracle like XML PATH? Or another way the get the result?

View 12 Replies View Related







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