SQL & PL/SQL :: Range Between X Preceding And Current Row?

Aug 26, 2011

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?

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: ORA-02063 - Preceding Line From?

Sep 16, 2011

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?

View 12 Replies View Related

PL/SQL :: Distinct Preceding Value Required

Sep 12, 2012

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]....

View 7 Replies View Related

SQL & PL/SQL :: Extract Numerical Value Preceding Percentage Sign

Jan 17, 2012

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.

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

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

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

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 Related

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

I 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.

View 15 Replies View Related

SQL & PL/SQL :: Count Age In A Range

Mar 26, 2012

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

View 1 Replies View Related

SQL & PL/SQL :: How To Add Range Partition

Dec 5, 2011

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]....

View 4 Replies View Related

SQL & PL/SQL :: Range By Day And Month

Sep 20, 2010

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.

View 13 Replies View Related

PL/SQL :: How To Group By Over A Range

Jun 12, 2012

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?

View 6 Replies View Related

Where Current Of - For Update Of

Oct 5, 2010

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]...

View 3 Replies View Related

SQL & PL/SQL :: How To Get The Name Of Current Procedure

Jul 13, 2011

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 ?

View 5 Replies View Related

SQL & PL/SQL :: How To Get Next Not Null Value For Current Row

Dec 14, 2010

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]....

View 4 Replies View Related

Update Where Current Of

Sep 27, 2010

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.

View 1 Replies View Related

How To Find Summary Value By Range

Jul 22, 2011

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?

View 1 Replies View Related

Grouping By Time Range

May 19, 2013

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] ........

View 1 Replies View Related

Using Range In Oracle Like Statement?

Oct 8, 2009

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?

View 3 Replies View Related

SQL & PL/SQL :: How To Find Summary Value By Range

Jul 22, 2011

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?

View 7 Replies View Related

SQL & PL/SQL :: Report With Date Range

Feb 4, 2013

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;

View 10 Replies View Related

SQL & PL/SQL :: Query On Range Wise

Jul 26, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: Finding Values In A Range?

Nov 1, 2011

@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.

View 4 Replies View Related

Forms :: Range Of Values?

Dec 11, 2010

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 Related

SQL & PL/SQL :: Restriction In Date Range?

May 7, 2012

I 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.

View 8 Replies View Related

SQL & PL/SQL :: Query Not Using Partition Range?

Dec 8, 2011

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..

View 1 Replies View Related

SQL & PL/SQL :: Create Range Partition?

May 3, 2012

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.

View 2 Replies View Related

PL/SQL :: Generate Hours Range

Oct 4, 2012

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).

View 5 Replies View Related

SQL & PL/SQL :: Generate Records For Range Of Each ID

Sep 13, 2013

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

View 4 Replies View Related

PL/SQL :: Reg - Index On Range In Where Clause?

Oct 29, 2013

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

View 0 Replies View Related

SQL & PL/SQL :: Pick A Value Based On Range

Mar 10, 2012

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.

View 2 Replies View Related







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