I have a table and want to calculate the stdev of a parameter over a rolling window (past 250 records, sorted by ID):
...
stddev(parameter) over (order by ID range between 250 preceding and current row) as SD
...
I don't get any error, but if I calculate the same in excel (=STDEV(250 row range)), it seems that this code does not the same. Is there something wrong with the 250 row rolling window?
I am getting the below error when executed the query below in production database.The database version of both the database is 10.2.0.4
ORA-01008: not all variables bound
ORA-02063: preceding line from DBLINK.PROD.NIC.CMS
select count(*)
from order_tbl@DBLINK.PROD.NIC.CMS a
group by a.col1
having count(distinct a.col2) > 1
the same sql is working fine in dev environment.Is this a bug?
Following on from this answered thread: (xmlagg(xmlelement) - Distinct Values Required..Whereby the last poster recommended the use of
RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (segment1|| ',' AS "Seg")
)
ORDER BY segment1 ASC
).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
','
)
To get a distinct list of elements. Works great, but I actually need to use the concept of "distinct immediately preceding".
Sample data:
create table xml_test
(emp_number number, seq number)
insert into xml_test values ('12345',1);
insert into xml_test values ('23456',2);
insert into xml_test values ('44323',3);
insert into xml_test values ('12345',4);
[code]....
I have two values:
'AA:10%:1'
'AC:P35:20%'
I need to extract the numerical value preceding the '%' sign. for first row 10 should be extracted and for the second 20.
What is the difference between the following . In my schema all are giving the same results with some different format
SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM
I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
View 5 Replies View RelatedI have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
I have a table with the following fields :
RGS_CAN_PERIOD NUMBER(4)
RGS_ID_ELECTOR NUMBER(
FBD_NID VARCHAR2(14)
RGS_SURNAME VARCHAR2(40)
RGS_O_NAME VARCHAR2(40)
RGS_ALIAS VARCHAR2(30)
RGS_ADDR VARCHAR2(75)
RGS_ID_REG_AREA VARCHAR2(3)
[Code]..
I need a report as hereunder :
Reg. Area Age <=19 20 <= Age <= 24 25 <=Age <= 29 Total No. of Voters
xxxx 10 15 7 32
yyyy 5 7 3 15
I have work out a script but the age is not in a range
select *
FROM (select rgs_id_reg_area,
count(decode(fbd_age,19,fbd_age)) Age19,
count(decode(fbd_age,20,fbd_age)) Age20
FROM rubyvoterstat where vote ='Y'
GROUP by rgs_id_reg_area)
order by rgs_id_reg_area
In my prod table few partition need to be added.
I tried below both syntax, but I am getting error
1)
Alter table STS.DNA_ACCESSION add PARTITION DNA_ACC_P143 VALUES LESS THAN (286000000);
ORA-14074: partition bound must collate higher than that of the last partition
2)
ALTER TABLE STS_RO.DNA_ACCESSION
SPLIT PARTITION DNA_ACC_P999 AT VALUES LESS THAN (286000000)
INTO (PARTITION DNA_ACC_P143,
[Code]....
I have table :TABLE_X and want to select some data locate into specific range of Day/Month. But so far i couldn't find out the way to.
For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.
e.g. a range could be from today:Sept,20 to Apr,18.
so what i was trying is to select doing the following.
select TABLE_X_ID, TABLE_X_BIRTH_DATE
from TABLE_X
where to_date(TABLE_X_BIRTH_DATE, 'DD/MM')
between to_date (to_char(SYSDATE, 'DD/MM'), 'DD/MM')
and to_date (to_char(SYSDATE+210, 'DD/MM'), 'DD/MM')
first am not sure if BETWEEN & AND will work for this case, bt it was the most logical way i could think about to get such range.
I have a table like this:
x y AMNT
---------------------------------------
1 120 12
1 120 93
1 125 31
1 260 15
2 56 16
2 115 49
3 45 71
4 19 11
4 16 48
5 94 52
5 98 47
I want to group records on x , y columns and aggregate on amnt column, in which difference between values of y column be less than 10.
The result is like this:
x y sum(AMNT)
------------------------------------------------
1 ? 136
1 260 15
2 56 16
2 115 49
3 45 71
4 ? 59
5 ? 99
What query can I use?
I have an employee table that has a paygrp_id that will be used for my subset of employees. For all the employees that have the paygrp_id = 10212 on the employee table I need to update the workbrain_user table to set the flag wbu_cansee_self to 'N'. The join between the employee table and the workbrain_user table is the emp_id.I get the following error when I run this cursor.
[error]
Error on line 0
DECLARE
CURSOR wbuFlag_cur
IS SELECT e.emp_fullname, e.paygrp_id,wbu.WBU_CANS
[code]...
I have a procedure like below:
create or replace procedure sp_test
is
vs_proc_name varchar2(40);
begin
--get the name of current procedure ,here is "sp_test"
--[color=red]but ,i do not want the hard code here[/color]
insert into test_Page(proc) values(vs_proc_name);
---
--
commit;
end;
I don't want to coding like this:
vs_proc_name:='sp_test';
so ,is there any building function to get that ?
following is my input data:
SELECT TO_DATE('21-NOV-2010') DAY, 0 RATE FROM DUAL
UNION
SELECT TO_DATE('22-NOV-2010') DAY, 10.5 RATE FROM DUAL
UNION
SELECT TO_DATE('23-NOV-2010') DAY, 0 RATE FROM DUAL
UNION
SELECT TO_DATE('24-NOV-2010') DAY, 0 RATE FROM DUAL
UNION
[code]....
I have a table I need to update called employee_history. It has almost the exact same values as another table called Employee. The difference being that the employee_history has a start date and end date. So it allows you to have overrides in the future. The employee table is static. So it only allows you to see the current data as of today.
I need to write an insert statement that will pull values from employee table and populate the employee_history table but I also have columns in the employee_history table that requires a next value and a start and end date.
From the employee table I want to select e.emp_id, e.emp_firstname, e.emp_lastname, e.shftpat_id, e.client_id from employee e
I want to INSERT INTO EMPLOYEE_HISTORY (EMHIST_ID, EMPHIST_START_DATE, EMPHIST_END_DATE, EMP_ID, EMP_FIRSTNAME, EMP_LASTNAME, SHFTPAT_ID, CLIENT_ID)
with the VALUES being (SEQ_EMPHIST_ID.nextval, TRUNC(sysdate), 01/01/3000 and the employee data from the employee table)). I bought the book Easy Oracle PL/SQL Programming but I am not finding what I need. It gives an example of a CURSOR FOR UPDATE and a reference to an UPDATE WHERE CURRENT OF.
The examples are straight forward where you take two columns from table one and update a new table with those values, or where you select a subset of employees that need a commission and update the emp table with that commission.
this is my table
create table sequence ( id int not null primary key);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
I am trying to break down the balance_date to display the following groupings:
7:00-17:30 CDT
18:00-4:30 CDT
I currently have the query setup to display by day instead of these time ranges. I would like the output to read
19 May Day
19 May Night
20 May Day
20 May Night
I am fairly new to this, but how would I go about making this change?
SELECT
TO_CHAR(TRUNC(balance_date,'D') + 4,'YYYY') || '-' ||
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW') as year_wk,
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW')as wk,
[Code] ........
I am trying to create a SQL query which will check that various postcode formats are valid, but I am having trouble getting oracle sql to check for values within ranges - for example the following returns no rows, even though most of the postcodes I am dealing with start with 'P'.
select postcode from mytable.addresses
where postcode like '[N-R]%'
;
Am I getting my syntax wrong somewhere?
this is my table
create table sequence (
id int not null primary key
);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
I have a cost data for effective date range as below.
MODEL_NOTIER_COSTEFFECTIVE_START_DATEEFFECTIVE_END_DATE
I3 3770 265 2/3/2013 3/31/2013
I3 3770 269 4/1/2013 5/4/2013
This data needs to be represented in a SQL report as below. The Date Range in the below i.e., Starts with FEB, by checking against the sysdate.. That is from sysdate it will display the Quarter data for 4 months as below.
MODEL NOFEB FY13MAR FY13APR FY13MAY FY13
I3 3770 265 265 269 269
Currently I am using a procedural logic to populate data into a different table in the above format. Is there any method to do with a single SQL using PIVOT. Below given is the table structure and Insert scripts.
CREATE TABLE ITEM_TAG(MODEL_NO VARCHAR2(50), TIER_COST NUMBER(13,4), EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE );
Insert statements
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 265, TO_DATE('02/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 269, TO_DATE('04/01/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
I want output like given below. Using EMp table . if sal is <1000 means count of Emp's who are having lessthan 1000 like that.
Deptno sal sal sal sal
<=1000 <=2000 <=3000 <= 5000
10 2 4
20 2.
@test.sql
accept 1 num prompt 'enter begin value '
accept 2 num prompt 'enter end value '
Need to display all values between the above given inputs.
No tables involved.
Need SQL level solution in version 8i.
I have a field called percentage , where user doesnt want to enter any data , instead he wants a poplist like 0-100 when he clik the range will come as 0-10,10-20 and so on like 90-100 how i can do it he will increase and decrease it.
View 1 Replies View RelatedI want my user to be restricted for entering duplicate time within two times.
create table asd(dt_frm date,dt_to date);
insert into asd VALUES(to_date('01-04-2012 08:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 10:00','dd-mm-yyyy hh24:mi'));
insert into asd VALUES(to_date('01-04-2012 09:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 11:00','dd-mm-yyyy hh24:mi'));
now in the second insertion I want to alert the entry user that 9am already falls in the saved record which is 8am to 10am and so that this record can't be saved.
I have a table partitioned by Range and subparitioned by hash.
i am issuing the following query .Bcos of security purposes i cannot post the full query.
Select <most of the columns>
FROM qosTesthr PARTITION(PARTITION_ON_2011_11_17)
WHERE starttime BETWEEN to_date('1111170000', 'yymmddHH24mi') AND to_date('1111172359', 'yymmddHH24mi')
Explain plan is
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 826K| 171M| 2330 (4)| 00:00:28 | | | | | |
|* 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 826K| 171M| 2330 (4)| 00:00:28 | | | Q1,00 | P->S | QC (RAND)
[code]...
When i use INDEX_SS hint in the query then only the partition scan is happening.
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 826K| 171M| 810K (1)| 02:42:06 | | |
|* 1 | FILTER | | | | | | | |
[code]...
Performance while using the index is very very slow.Is there any way without using the index and just the partition full scan can be forced by using some hint..
I have to create partition on 1 table where i have date field and id.
i want to create range parttion for both the column.
I have a table with a date field. This field storage dates with hours like this:
01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...
I have to generate a report with the day and the frequency :
__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3
The block of the hour must be 1 hour (8-9,9-10 and so on)
how generate the hours range.
My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).
write the query for the following requirement.I need to generate the records for the range (Difference between From_val & to_val) ) for each ID
create table test_base(id varchar2(20) , from_val number , to_val number);
insert all
into test_base values ('A', 6,10)
into test_base values ('B', 22,30)
into test_base values ('C', 123,130)
into test_base values ('D', 852,860)
into test_base values ('E', 30 ,30)
select * from dual ;
[code]....
No need to generate any thing for E as the difference is equal to Zero
I am creating a table where ID is an integer and the where clause will always be in range(for e.g between 200 and 400).
for best performance which index can i create on table such that query runs faster ?(a)clustered(b)non-clustered(c)unique(d)both clustered and non clustered
I need to pick a value based on range like if the range is as below
[code]
if value =2000 then its 2000
elsif value >=2001 and value <=2499 it should be 2000
elsif value =2500 then 2500
elsif value >=2501 and value <=2999 it should be 2500
elsif value = 3000 then it should be 3000
[code]
Like this i need to pick a value by hardcoding this range and this look cumbersome in my program , is there a simple way to substitute this entire thing by passing one single value and getting one single value using a command or builtin.