I have Table Data in format :-
--------------------------
ABC
ABC
ABC
XYZ
XYZ
and i have a requirement where I need the output in this format.
-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2
what query to be used for this
I ve a data in table as follows
code status seqnce Length
B1 AVL 1 10
B2 AVL 2 10
B3 ASG 3 10
B4 AVL 4 10
B5 AVL 5 10
B6 AVL 6 10
B4 ASG 7 10
B4 ASG 8 10
I need to group the AVL status codes with the sum of lengths Ex) B1 to B2 - 20 B4 to B6 - 30 like that.
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
I want to group the following records and mark them in a sequence order.
drop table test;
CREATE TABLE TEST
(
key0 NUMBER,
key1 VARCHAR2(1),
key2 NUMBER)
;
INSERT INTO TEST VALUES (4,'A',1);
INSERT INTO TEST VALUES (4,'A',2);
[code]......
key0 key1 key2
4A1
4A2
4A3
4A4
3A1
3A2
3A3
1A1
1A1
Now I want the records in the following way, where group_no would be in sequence when key2 starts with 1 again.
key0 key1 key2 group_no
4A1 1
4A2 1
4A3 1
4A4 1
3A1 2
3A2 2
3A3 2
1A1 3
1A1 4
Can I achieve this using SQL only and not by PL/SQL.
I found nothing in SQL (all in PL/SQL).I have a table:
create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.
And I have a child table:
create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.
This is the current scenario -
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03
When I try to query based on the time stamp, primary should also go up. To be very clear on what I would like to have, please consider the following example.Without using NOCACHE option, I need to have the data in the following order.
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03
In other words, sequence number should always increment along with the time.
I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.
I have query like this
"SELECT
AER_ID_TEMP.AER_ID,
AER_ID_TEMP.D__PRNO,
AER_ID_TEMP.D__PCNT,
CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID
FROM AER_ID_TEMP"
but still encountering a error "ORA-02287 sequence number not allowed"
CREATE OR REPLACE TRIGGER audit_emp AFTER
INSERT OR UPDATE OR DELETE ON Employee FOR EACH ROW
declare
my emp_audit%ROWTYPE;
begin
if inserting then my.action := 'I';
[code].......
It works alright but i need to enter a sequenced value to a column audit_id which is a column in the emp_audit table when a change takes place, but when i define the variable it gives be an error as bad bind variable so where can i define this column in this trigger so get a sequenced value every time i d or u occurs .
Is there any way to get the last Sequence Number generated regardless the Sequence name? Something like dbinfo('sqlca.sqlerrd1') within Informix?
Imagine I have 2 sequences. Imagine that I run both several times until 20:00 hours. Imagine that at 21:00 hours I would like to know what was the last generated number created by any of the sequences. I don't wann know wich sequence generated the last NEXTVAL, I only want to know the value of the last NEXTVAL used.
Is it possible without mentionating the Sequence name?
I am using following Procedure/function for inserting DATA into New table:
PROCEDURE NEMPID IS
OFFICER CHAR(1) := '0';
OFFICIAL CHAR(1) := '9';
CURSOR DOJSUP IS
[Code]....
Get/Retrieved data from personal and cat9 files and Inserted Data stored in file NEMP as
EMP_ID NEMP_ID
------- ---------
111 01-8722
132 01-9224
88 01-8318
114 01-8717
106 01-8517
[Code]....
I want also generate a sequence/serial number for same category/designation of personals/employees exist in personal file ?
Like 001, 002 ,003 at the end of NEMPID field.
EMP_ID NEMP_ID
------- ---------
111 01-8722-001
132 01-9224-002
88 01-8318-003
114 01-8717-004
[Code]....
HOW CAN GENERATE SEQUENCE/SERIAL NUMBER OF SAME CATEGORY EMPLOYEES AND INSERT INTO TABLE NEMP?
I am facing issue with my sequence number about sequence number generated..I want it to follow the sequence all the time, i.e. it should take next incremental number to last generated seauence, but it generates random one all the time, i.e
s.nextval() = 4
s.nextval() = 5
s.nextval() = 10
s.nextval() = 543
s.nextval() = 544
My sequence defination is as below
CREATE SEQUENCE s
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
I want to insert same sequence number for repeated data else new sequence.
my data is as below
EMP_NAMEEMP_INV
PrasCTI
PrasCTI
ShariqWitness
KateCTI
ShariqAssisted
ShariqWitness
the first 2 record are same Pras CTI and Pras CTI so i want my sequence generate value once as below
1 Pras CTI
1 Pras CTI
2 Shariq Witness
3 Kate CTI
4 Shariq Assisted
2 Shariq Witness
Shariq and witness are repeating so same sequence number but Kate CTI occured once so nextval of sequence
How can i acivee it
CREATE TABLE EMP_INV
(
EMP_NAME VARCHAR2(100 BYTE),
EMP_INV VARCHAR2(100 BYTE)
)
Insert into EMP_INV
(EMP_NAME, EMP_INV)
Values
('Shariq', 'Witness');
[Code]...
I have a result set with three columns as 'Product Category', 'Product' & 'QtySales' and 10 rows, sorted in the order Product Category, Product. This means, a product category will have one or more products under it.
Now i want to add a fourth column to my result set, which should display a incremental number sequence from starting from 1, 2, 3.. for each row. Also when the value of the Product Category (1st column) changes, this sequence should be restarted again from 1.
Col1 Col2 Col3 Col4
PC1 P1 10 1
PC1 P2 20 2
PC2 P3 30 1
PC2 P4 10 2
PC2 P5 15 3
PC3 P6 25 1
I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..
Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.
create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));
insert into tb_test
(select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');
I am having ORA-00937 : not a single-group group function error
create sequence seq minvalue 1 maxvalue 99999 start with 1 increment by 1;
create table t ( id number, b date);
insert into t values (seq.nextval,sysdate);
update t set b=sysdate-1 where id=seq.currval;
update t set b=sysdate-1 where id=seq.currval
*
ERROR at line 1:
ORA-02287: sequence number not allowed here
how can i update as above ..
I have a table:
Name
_____
Smith Street
Smith Street
John Street
Ed Street
Ed Street
Ed Street
and need to assign sequence numbers only when the record (Name) changes, e.g. :
Name Seq
_____ ____
Smith Street 1
Smith Street 1
John Street 2
Ed Street 3
Ed Street 3
Ed Street 3
I have experimented with row_number partition but then i just get the sequence returning to 1 when the name value changes.
If I grouped the records by Name I would like to have unique, sequential numbers: 1, 2, 3 but where there is the same name I would like the sequence to stop and the number to replicate?
In my form I have a submit button, when i click on it a sequence number to be generated and displayed in a text box.
I have created a sequence and tried inserting into table, it works fine however it's not getting displayed in form.
Note: Tried giving sequence.sequence_name.nextval in initial value of the text box, didn't work showed FRM-11324 error.
--When button pressed--
DECLARE
v_seq number(20);
BEGIN
select seq_name.nextval
into v_seq
from dual;
[Code]...
i want to get serial numbers in oracle forms. i have one data block with 10 rows and a non database display item with corresponding length as that of the original data block .
when ever i am executing the form i want to see the serial number generated in the display item and when i inserted a new item or row it should show the corresponding rownum.
eg:
empno name serialno
5 tom 1
4 tinil 2
6 sofy 3
7 john 4
1 albert 5
2 michel 6
i want to get the above answer using forms.
I want the query to generate the sequenctial number from 1 for the new financial year in some query.
View 6 Replies View RelatedI have a CSV file with 100 records and one of the column as FILE_ID. I want to load one unique number for all 100 recs not for every records.
suppose my sequence returns 3 as next val i want to load 3 for all 100 records. How to implement this in control file or sh file . I am using shell script to call sqlldr.
SELECT
DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,
[Code]....
My problem, I have the query which works correctly. However as you can see there is always a sequence number in the end of variables and I am not sure the total number of cases, so I have to create a loop for the same query.
I am doing the same things on BREINV1 BREINV2 BREINV3 BREINV4... AND BRENUMINV1 BRENUMINV2... so the sequence should add a number in the end of variables...
I have a table called table 1 and I am trying to insert a few values in this table and i am constantly getting bogged down by a primary key error. Strange thing is this primary key called "ID" on the table is a system generated sequence value number. The error is
"ORA-00001: unique constraint (Schema1.LICN_PK) violated"
Table structure:
*****************************************
CREATE TABLE schema1.table1
(
ID NUMBER(12),
LITM_ID NUMBER(12),
PROG_PROGRAM_CD VARCHAR2(2 BYTE),
SCHED_NBR VARCHAR2(3 BYTE),
SCHD_VERSION_YR NUMBER(4),
SCHD_VERSION_NBR NUMBER(3),
[code]........
****************Insert statement*************
insert into table1
(id,
litm_id,
prog_program_cd,
sched_nbr,
schd_version_yr,
[code].......
why the error is coming up and any way to resolve it. I checked the max sequence number value and kept running it for a while and then tried to run my insert statement but no luck yet.
I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.
So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....
I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.
So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?
My requirement is I have to get a sequence no generated for 2 set of groups. Here is an example to better resolve it.
Gr1
Seq Data
--------------
1 Data1
2 Data2
3 Data3
GR
Seq Data
--------------
4 Data1
5 Data2
SQL> select checkpoint_change#,controlfile_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
203454 204955
what's the difference between checkpoint_change# and controlfile_change#.
what's the checkpoint_change# use for ? does it use for recover ?
what's the controlfile_change# use for ?
when the controlfile_change# will be increase ?
SQL> select controlfile_sequence# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
293
Qs.) what is controlfile_sequence# ?
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 am having a table employees with columns
1.employee_id
2.department_id
3.hire_date
Display department ID, year, and Number of employees joined?
I have query in that query i should display the number of row. I have tried to select the row num but when i use it the data will be duplicated. I have found something that related to use temporary table, or subquery but all of them will not work because the query depends on many tables and if i use temporary table filling the table and then reading from it will take too long time.
View 11 Replies View Relatedhow do i get runtime sequence number at report level.
View 1 Replies View Related