CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1
i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?
I wish to create a table with week-number as suffix _36 or _30
CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);
$WEEKNUMBER is this statement:
SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL
CREATE TABLE doesn't accept subselect
How to do that with SQL?
I'm new to Oracle so I try to become familiar with SQL.
In my script I want to become only this results back in the select of the Where clause which have more than 1 entrys per job_id.
But my try didn't work ... error: too much entries ??!!
SELECT
last_name
, job_id
, salary
, AVG(salary) OVER(PARTITION BY job_id) Average
[Code]....
I want to use a sequence to populate a field for my insert statement. Should be simple right?
So here is an example
insert into sometable ( seq, else)
select
myseq.nextval
,somethingelse
from sometable@dblink
where somethingelse = 10
now if i remove my sequence call for nextval it correctly uses the where clause but when I use the sequence in the select statement it is ignoring the where clause completely. I've never had this issue before...is this because i'm using a dblink now?
I'm using toad 10 on oracle 11g in multi-schema environment.
Create a sequence E_SQ which start with the (current max empno + 1) and increment by 1.
View 5 Replies View Relatedim creating sequences for an already created db and need to set MINVALUE to sequences.
create sequence tb_1
minvalue X
start with X;
so, X == select Max(id_tb1) from tb_1.
I need that select returns an integer.
How can I do this??
i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
[code]...
trigger created sucessfully.And i try to delete data from the test
delete from test where id=5;
Output:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
Every time sequence should be start from 1.can i know how to create sequence? for ex:-once i have uploaded 100 records this time sequence is generating from 1 to 100.next time i will upload again 100 records this time sequence is generating(Starting) from 200 to 300.but it should be generate from 1. how? i have created like this
CREATE SEQUENCE XX_SEQUENCE
MINVALUE 1
MAXVALUE 99999999999
START WITH 1
INCREMENT BY 1
CYCLE
NOCACHE;
I know how to create sequence in the database.But I want to create this sequence from the form by giving the name of the sequence from a filed in the form.
View 1 Replies View Related i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '
end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;
Output:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
create table t (a varchar2(20),b number(8));
insert into t values ('aa',4);
insert into t values ('ba',6);
insert into t values ('ca',7);
insert into t values ('da',8);
in place of 8 there can be any number between 1 to 100
if in place of 8 number is <10
insert into t values('ea',10); ---- this i need dynamic insert
if in place of the number between 11-19 then
insert into t values('ea',20); ---- this i need dynamic insert
and so on
i tried as below
select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;
but i cant write case for again and again upto nth
Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.
DECLARE
TEMP VARCHAR2(20);
BEGIN
TEMP := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;
This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.
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?
I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1. I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.
Here's a snippet from my script:
create table image
(
image_id int NOT NULL,
source_id int NOT NULL,
CONSTRAINT image_id_pk PRIMARY KEY (image_id),
CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id)
);
Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?
running on 10.2.0.5 and above satisfies the following:
having locally managed tablespace with assm enabled
create table t1 (n1 number) storage (next 1M);
insert into t1 values (1); commit;
create table t2 storage (next 2M) as select * from t1;
This will indeed set the storage option next to 2M;
The same situation in 10.2.0.4 will allways set next option for the ctas statement to the initial value of table t1.
Is this an impact of patch set 4 (10.2.0.5)? Why does it differ from 10.2.0.4 to newer releases?
Can we create a table from a Select query ?
View 5 Replies View RelatedI have a query like this:
*************************
SELECT vendor_id, summary_flag
FROM ap_suppliers
WHERE vendor_id = :param; --'4551'
The Results:
************
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N
Then I create the procedure:
*****************************
CREATE OR REPLACE PROCEDURE myproc4 (
p_vendor_id IN ap_suppliers.vendor_id,
p_summary_flag OUT ap_suppliers.summary_flag
)
AS
BEGIN
[code]....
Warning: compiled but with compilation errors.
I want to create a procedure that call vendor_id (parameter) and the output like the this:
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N
I have a table structure like :-
Create table test(A varchar2(50),B NUMBER);
The data in that table is like that:-
A B
----------------------
2*3
2*4*5
4*5
column B contain no data.
I want to create a function which can be used in a select query,and the output should come like that :-
A B
----------------------
2*3 6
2*4*5 40
4*5 20
Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.
Is it a possible to create table using clause below together with index ?
create table the_table
as
select col1, col2 from table2
I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index
then I got a error:
ORA-01031: insufficient privileges when
...executing
Therefore I think about to create table together with index.
begin
B.proc.cre_table;
end;
I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:
SQL Select:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-4CG-3
Create Table as <SQL Select>:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-3CG-4
The SQL Select is correct and the Create Table As <SQL Select> is wrong.
Here is my SQL:
Create table ALTERNATENUMBERS as
SELECT ctry,
id,
MAX(DECODE(tp,'EN', RN)) EN,
MAX(DECODE(tp,'RN', RN)) RN,
MAX(DECODE(tp,'AN', RN)) AN
[code]....
Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.
Now, I've tried:
1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables
I do understand that without data it's hard to replicate the issue but why this statement works as a SELECT but when written to a table has data anomolies?
I'm trying to create a sort of nested-query within my select of attributes . i.e.
select A.a,
B.b,
(select count(C.*)
from C
where C. = B.d
group by C.y)
from A a,
B b
where A.d = B.d
and ...
Over-simplifying my query:
select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
c.desc "Status"
[code]....
I need to incorporate a count of the number of units from TableG that have a certain status. I tried the following but when I tried to run it, I get an error saying that it's not a Group By expression -the red part is highlighted in TOAD.
select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
(select count(*)
from TableG G2
where G2.D_ID = D.ID
and G2.status = 10
group by G2.D_ID)"Count",
c.desc "Status"
[code]....
Any thoughts how I can incorporate a query in my select of attributes?how to Group By something.
how do I create a procedure for a SELECT query like the following?
When I create a procedure; I get an error "Error(80,1): PLS-00428: an INTO clause is expected in this SELECT statement" PROCEDURE MyProcISBEGINselect 'Dakota' as ALIAS ,A.StartDate ,B.EndDatefrom Customer A ,Clients bwhere a.cType = b.cTypeand b.Active =0ORDER BY StartDate, EndDateEND MyProc;
I have a table with columns job_id, jan, feb, mar ... , and year
I need to create a select query that will get the data from 18 months ago based on sysdate.
So something like:
Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');
However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.
i am using Apex 3.2 ver.
i want to use below code in LOV select list
BEGIN
IF UPPER(:P23_SERVICE_TYPE) like 'GUIDE%' THEN
SELECT NAME D, CODE R FROM SPECIAL_SERV_MAS
WHERE NVL(ACTIVE_FLG,'N') = 'Y'
AND NVL(GUIDE_FLAG,'N') = 'Y'
and CITY_CODE LIKE NVL (:P23_CITY_CODE, '%')
[code]....
When i put this code in my LOV Select list Section then display me Error
Not Found The requested URL /pls/apex/f was not found on this server.
Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at tidevserv1 Port 7777
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
i want to select dynamic column names in my select statement in my function.
View 4 Replies View RelatedI M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
Why Blind select is better than Conditional select Statement?
View 10 Replies View RelatedHow do I get a query of each sequence and who has the permissions to it?
View 1 Replies View RelatedI have created a trigger for after insert which updates a table when there is a row inserted in that table. The update is on a column which stores the application description along with the sequence number. Now my requirement is that sequence number should be unique only with in an application but not with in the table.Say the row entry can be as follows:
App_Desc Request_ID
-----------------------
DEV 100 1
DEV 101 2
STG 100 3
STG 101 4
Here Request_ID is unique But the sequqnce thats created for DEV (100,101) should take an entry of 102 for the next entry for DEV and same applies for STG. So I have to use the same sequqnce for all the application.