I have Leave Balances In such Format
EMP_COD CategoryJON_DTE LEV_BAL
3900000385101/02/200760
3900002678107/01/200840
Now I want to Separate it into Three and Two Partitions Year Wise.
Like this
3900000385101/02/200720 2012
3900000385101/02/200720 2011
3900000385101/02/200720 2010
3900002678107/01/200820 2012
3900002678107/01/200820 2011
I'm using Oracle forms 10g. In the form i have this multiple records under one block. After performing the query, all records are cannot be updated. And i have this buttons at the right side that will enable the certain item that can only be updated. My problem is i cannot enable the item in the first record,instead of first record all records in that item was enable.
View 8 Replies View RelatedI 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.
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
this is the case:
Table 1
c1 c2
1 null
2 null
3 2
[code]...
I need to select n records where c2 is null but only from one partition.In example :
In P1 (1,2,3,4)
In P2 (5,6,7)
If n = 4 --> Must be retrieve 1,2,4 because 6 is in P2.
What I tried is
(select c1 from T1 where c1 is null and rownum<=4)but retrieve 1,2,4,6 and I only need the retrieved data from P1 partition.
Can i partition by list on multiple columns? i am trying the following code, and it is returning an error. Is there a way round this?
CREATE TABLE
(...)
PARTITION BY LIST (col1)
SUBPARTITION BY LIST (col2)
SUBPARTITION TEMPLATE (
SUBPARTITION DETAIL VALUES ('DETAIL')
SUBPARTITION ROLLUP VALUES ('ROLLUP'))
(
PARTITION RT VALUES('RT')
)
I have a scenario where the requirement is to create a "SEQ NO". Based on the 'Tasks' & it's creation date. Where there are multiple 'tasks' for a single SR.
The following example will make it clear
SR TASK Created on SEQ NO
11009 2345 14/10/1988 12:15:17 1
11009 2346 14/10/1988 12:15:57 2
11234 - - -
If there is a SR which has no task then the SEQ NO should be 0. And the seq no should be in the order of the created on field & if 2 tasks are created at the same time i.e suppose 2345 & 2346 have the same time stamp then the SEQ no should be 1,2 respectively n not both as 1.
I want to use multiple partitions in from clause of SQL statement. select c1 from tab1 partition (part1,part2); However getting below error message.
ORA-00933: SQL command not properly ended00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:Error at Line: 3 Column: 43 Above query is sample query.
Real query is big and hence i cant write using UNION ALL to access multiple partitions.
If I run the following query I got 997 records by using GROUP BY.
SELECT c.ins_no, b.pd_date,a.project_id,
a.tech_no
FROM mis.tranche_balance a,
FMSRPT.fund_reporting_period b,
ods.proj_info_lookup c,
ods.institution d
WHERE a.su_date = b.pd_date
AND a.project_id = c.project_id
AND c.ins_no = d.ins_no
AND d.sif_code LIKE 'P%'
AND d.sif_code <> 'P-DA'
AND a.date_stamp >='01-JAN-2011'
AND pd_date='31-MAR-2011'
GROUP BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no;
I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.
SELECT c.ins_no, b.pd_date,a.date_stamp,a.su_date, a.project_id,
a.tech_no,
COUNT(*) OVER(PARTITION BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no)c
[code]....
why I got 1079 records.how to show the two extra columns in the out put whcich are not used in GROUP BY clause.
I have used sql for my school projects and work projects. not too extensively though. I came across this issue and read a lot of blogs but still not luck.
hopefully my issue can be solved here.
Data:
Sub_grp_nbr Prodt_ctgy_cd Extra_column
ABC123 05 1
ABC123 02 2
ABC012 05 3
ABC456 02 4
ABC456 05 5
ABC789 05 6
I need to obtain all the sub_grp_nbr's that have a prodt_ctgy_cd of 05 but not 02. so according to the data above, i should only get the results of where extra_column = 3 and 6
I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?
Let's consider an example with 5 records:
Table data:
CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/
[Code]...
So when we check the table, we get:
SELECT * FROM enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
What I would like to have when I run the select query:
SELECT * from enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.
How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions
I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query
Test Scripts as below
CREATE TABLE TEST_Prev
(
ID1 NUMBER(3),
Name1 VARCHAR2(10),
OFFICE VARCHAR2(20),
PRODUCT VARCHAR2(20),
Overall VARCHAR2(20),
DATE1 DATE,
VALUE1 NUMBER(1)
);
commit;
[code]......
Expected output as in attached sheet.
I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed
The following is the example
WITH t1 as
(select 159435 ky from dual)
,t3 as
(select 78 id ,'Z-' rk,'SL' cd from dual union all
select 78 id ,'Z+' rk,'SL' cd from dual union all
select 78 id ,'Z-' rk,'SL' cd from dual union all
[code].....
In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.
Scenario 1 Query should check for priority record(25), if the start_date and end_date of that priority record is the max in that group, records will not have any split.output will be the same.
DC Store St Date End date Priority
955 3 1/1/2010 12/31/9999 25
966 3 4/5/2011 10/10/2011 50
977 3 10/12/2011 12/12/2012 100
output
DC store St Date End date Priority Rank
955 3 1/1/2010 12/31/9999 25 1
966 3 4/5/2011 10/10/2011 50 2
977 3 10/12/2011 12/12/2012 100 3
Scenario 2 If priority record is not covering the max range, then split the records as shown below,
1. during the time period 1/1/2011 & 4/30/2011 there were no other DC for that store so rank would be 1
2. the next range would be 5/1/2011 to 6/29/2011 we have 2 records in service so the record with low priortiy would be ranked 1 and second priority would be ranked 2
3. similarly, for 6/30/2011 to 10/1/2011 we have 3 records in service and it will be ranked accordingly on the priority.
DC Store St Date End date Priority
966 3 6/30/2011 10/1/2011 25
955 3 5/1/2011 11/30/2011 50
977 3 1/1/2011 12/31/2011 100
output
DC store St Date End date Priority Rank
977 3 1/1/2011 4/30/2011 100 1
955 3 5/1/2011 6/29/2011 50 1
977 3 5/1/2011 6/29/2011 100 2
[code]....
Scenario 3 This works similar to scenario 2
DC Store St Date End date Priority
966 3 2/1/2011 12/31/2011 25
955 3 1/1/2011 12/31/2012 50
977 3 5/1/2011 06/31/2011 100
output
DC store St Date End date Priority Rank
955 3 1/1/2011 1/31/2011 50 1
966 3 2/1/2011 12/31/2011 25 1
955 3 2/1/2011 12/31/2011 50 2
977 3 5/1/2011 6/30/2011 100 3
955 3 1/1/2012 12/31/2012 50 1
Note: Number of records in the input can vary and ther can be duplicates in the date interval
I have a table as col_1 number,col_2 varchar2(10)
col_1 col_2
1 abcdefghijkl
what i want is i've to split
col_1 col_2
1 abcdefghij
1 kl
I have a table formatted like this
ID|First Name|Second Name
1|X|X
2|X|X
3|X|X
4|X|X
and I want to insert First Name and Second Name records using the ID as the reference. The new data is currently in a .csv file, but I could put them in another table if that's easier.
ID|First Name|Second Name
1|Adam|Adamson
2|Ben|Benson
3|Chris|Christophers
4|Dave|Davidson
I understand how to do individual updates, I want to know how it is possible for me to do this as one query, as I have several thousand records to update.
I have a small requirement...
Create table temp_a (source_code varchar2(100), target_code varchar2(1000));
Insert into temp_a values ('1','002.0 AND 002.9');
Insert into temp_a values ('2','729.90 AND 079.99 AND 002.9');
Output :
1 002.0
1 002.9
2 729.90
2 079.99
2 002.9
So, once we get the output, it needs to be joined to another table. I did Google search, but most of them are retuning collections / arrays as output. Not sure how I join the collection with the table.
create or replace function splits
(
p_list varchar2,
p_del varchar2
) return split_tbl pipelined
is
l_idx pls_integer;
[code].......
I'm looking to create a STORED PROCEDURE to do the following per v1...but I seem to misunderstanding some ORACLE concept(s) as when called in a PLSQL window I'm getting an ORA-01422 message when using the V2 syntax.
Ideally I want this code called from VB, but for the moment I'm trying to get it to work when called from the PLSQL Developer toolkit. Am I trying the wrong concept? per previous posts my background is MSSQL, but I've used Oracle SQL from approx 2 years (at a SQL level) only.
v1
create procedure myabc
as
begin
SELECT custNAME
FROM MYCUSTOMER
[code]....
I have created the following function in oracle database 10g then I used it in Oracle reports 6 . It is working fine for single row(one value). But when there is multiple rows then it show error .
Note:ot.po_code is number datatype and clr_name is varchar2 data type.
this is the script
create or replace FUNCTION get_colour_name (POC number)
RETURN VARCHAR2 IS
CLR_NAME VARCHAR2(100);
BEGIN
SELECT DISTINCT BC.COLOUR_NAME INTO CLR_NAME
[Code].....
I want to insert multiple records in a single row. Example: I have a below query
select '"'||c1||'","'||c2||'","'||c3||'"'from (select 'ABC' as C1,'ZYX' as C2,'TEST' AS c3from dual unionselect 'A1' as C1,'a2' as C2,'A3' AS c3from dual)
And I want to insert the above 2 records in table T1 as a single row as below:
"A1","a2","A3""ABC","ZYX","TEST" Column1"A1","a2","A3""ABC","ZYX","TEST"
i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.
View 6 Replies View RelatedI am using toad to output the details of the database. (I need these details in a format which i can then import into excel) However, When I run sporadic individual records through my query, the results come back correct and as expected. But when I run the full data set through my query a lot of the records have different values than what they do when run individually!
Ive spent all day on this script and its really a hack of various other cursors and scripts brought together. Its driving me mad as my code doesn't seem to be wrong (since the results are correct when a single record is run) yet there must be something wrong as my full data set does not return correctly!
My query is: I have created a master-child forms on one canvas. In a child form i have to enter multiple records, but after entering every record its prompting me to save it. I want to avoid it and save the entire thing once all my records are entered.
View 4 Replies View RelatedInsert multiple record in table. I have a table of customers . It has column cus_name, cus_fruit, cus_date, cus_qty.
Select * from customers;
cus_name cus_fruit cus_date cus_qty
Maria Anders Apple 18-July-2013 10
Maria Anders Apricot 18-July-2013 20
Maria Anders Asparagus 18-July-2013 100
Maria Anders Avocado 18-July-2013 5
Ana Trujillo Apple 18-July-2013 10
Ana Trujillo Apricot 18-July-2013 20
Ana Trujillo Asparagus 18-July-2013 100
Ana Trujillo Avocado 18-July-2013 5
how I can insert record in one time in table. All table data same only change the cus_name.
Thomas Hardy Apple 18-July-2013 10
Thomas Hardy Apricot 18-July-2013 20
Thomas Hardy Asparagus 18-July-2013 100
Thomas Hardy Avocado 18-July-2013 5
After Insert record result.
Select * from customers;
cus_name cus_fruit cus_date cus_qty
Maria Anders Apple 18-July-2013 10
Maria Anders Apricot 18-July-2013 20
Maria Anders Asparagus 18-July-2013 100
Maria Anders Avocado 18-July-2013 5
Ana Trujillo Apple 18-July-2013 10
Ana Trujillo Apricot 18-July-2013 20
Ana Trujillo Asparagus 18-July-2013 100
Ana Trujillo Avocado 18-July-2013 5
Thomas Hardy Apple 18-July-2013 10
Thomas Hardy Apricot 18-July-2013 20
Thomas Hardy Asparagus 18-July-2013 100
Thomas Hardy Avocado 18-July-2013 5
I'd want to select multiple records in my multi-record block. I'd also want to do that with checkboxes. When the user clicks on a particular checkbox, that should be selected and whatever record the user wants to check it should be added to selected records.
View 39 Replies View Relatedhow to save multiple records at once i did coding like this:
declare
cnt number;
l_rec number;
f_rec number;
begin
cnt := 0;
go_block('aan');
last_record;
l_rec := :system.cursor_record;
first_record;
[code]....
but this works as saving the last record only, not saving the all records.
I have a form with two data blocks, one parent, one child block.
The parent is holds mineral lease info while the child holds the mineral owner info, such as addresses and phone numbers. One owner can be in the owner block multiple times (different owner types). The form only displays one owner at a time.
We have a separate master owner table which holds owner address. (We set it up this way because we get electronic info from mineral companies that we have to load each year).
As you tab through the owner block, it checks the FEIN against the master table and pulls updated address info from the master table.
I have a problem in which if an owner is on the lease multiple times, when you tab through the first instance, it pulls in the new address info, but when you go to the next instance, it won't update. If you requery, it seems that the first update actually updated all the owner records on that lease. How can I turn this off?
OS:Solaris
DB:10G
I have a situation where there are multiple records for a join criteria. I am trying to find a way to update a particular column for all the records returned by the join criteria. Example :
Table A
id number
1 1000
2 2000
3 3000
4 4000
Table B
id number
1 9999
1 9999
1 9999
1 9999
1 9999
1 9999
2 8888
2 8888
3 6666
3 6666
Result after update:
Table B
id number
1 1000
1 1000
1 1000
1 1000
1 1000
1 1000
2 2000
2 2000
3 3000
3 3000
update query ? When I use a update statement with where exists it errors out because the query returns more than one row for the join condition.
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in textindexmethods.ODCIIndexDelete
ORA-20000: Oracle Text error:
DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA
DRG-50857: oracle error in drexrdml
ORA-01031: insufficient privileges
[code]....
I am working on forms 6i.
I have datablock 'EMP', it's multi record block. I have a button 'CREATE', I have 'commit_form' in 'When button pressed' trigger, to commit records. It's working fine.
Now, i have a check box in the same block, i want to insert records which are checked only.
