PL/SQL :: Find The Range Of Number Between Two Columns

Jul 12, 2012

I have a table with two columns Column1 and column2

Like such

create table testTable (column1 number(15), column2 number(15));

insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.

so my answer set should be
1,2,3,4,5

View 4 Replies


ADVERTISEMENT

PL/SQL :: Query To Find First And Last Call Made By Selected Number For Date Range

Apr 27, 2013

create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));

insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);

Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';

Result :

date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10

[Code]....

View 5 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

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 :: Find Years Between Any Given Date Range

Apr 5, 2011

I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?

View 2 Replies View Related

Range Partitioning With Number Column

Apr 21, 2011

i have a requirement To partition a Table byRange partition with a Number column. but the issue is the range Must be in a Date datatype ,
For example

partition by range (date_key)
( PARTITION DEF VALUES LESS THAN ('01-SEP-10'))

the date_key column has values of date in number format. like "20101014"

View 2 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

Performance Tuning :: How To Find Out Queries That Are Executed In Particular Range Of Time

Sep 9, 2011

here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session

View 3 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

SQL & PL/SQL :: Generate Range Based On Start And End Number Dynamically?

Jul 14, 2011

I have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.

e.g Start Number : 1 and End Number : 1001.

Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.

Means

Range 1 1 -101
Range 2 102-200
Range 3 201-300
...
Range 10 901-1001

Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.

SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id
from
(

[Code].....

I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.

View 22 Replies View Related

PL/SQL :: Number Range Query Causes Repeated Scan By Index

Oct 16, 2012

I have a query that seems to repeatedly call an index scan on a table for reasons I'm not sure about. Why it would be doing the index scan on totaldwellingarea in the dimensions table (DIMEN_PID_TDWELLAREA) repeatedly? This only seems to happen when I put on the range clause d.totaldwellingarea between scr.lowvalue and scr.highvalue.

I am using Oracle version 9.2.0.3.

select d.propertyid,d.totaldwellingarea, e.size_,  scr.size_
from  eqid e, dimensions d,  brt_eval.size_code_ranges scr
where e.style not in ('1','A','G','L') and e.size_  = '0'
and d.propertyid = e.propertyid and e.style = scr.style and d.totaldwellingarea between scr.lowvalue and  scr.highvalue;

CREATE
  TABLE "BRT_ADMIN"."EQID"
  (
    "PROPERTYID"   VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "EQID"         CHAR(10 BYTE),
    "ZONE_"        CHAR(1 BYTE),
    "AREA"         CHAR(1 BYTE),
[code].....

View 3 Replies View Related

Table Partitioning - Range On Datatype Number (21, 7) And Varchar2

Nov 15, 2013

Database Version : DB : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionOS : HP-UX nduhi18 B.11.31 U ia64 1022072414 unlimited-user licenseAPP : SAP - ERP I have to RANGE partition on UPDATED_ON  or PROFILE  either one table which is having below

structure :   Name                Null?    Type
-------------------- -------- --------------------------------
MANDT                NOT NULL VARCHAR2(9) MR_ID                NOT NULL VARCHAR2(60) PROFILE              NOT NULL VARCHAR2(54) REGISTER_ID          NOT NULL VARCHAR2(30) INTERVAL_DATE        NOT NULL VARCHAR2(24) AGGR_CONSUMPTION     NOT NULL NUMBER(21,6) MDM_VERS_NO          NOT NULL VARCHAR2(9) MDP_UPDATE_DATE      NOT NULL VARCHAR2(24) MDP_UPDATE_TIME      NOT NULL VARCHAR2(18) NMI_CONFIG           NOT NULL VARCHAR2(120) NMI_CONFIG_FLAG      NOT NULL VARCHAR2(3) MDM_DATA_STRM_ID     NOT NULL VARCHAR2(6) NSRD                 NOT NULL VARCHAR2

[Code]....

 As per my knowledge, RANGE is better suited for DATE or NUMBER. and INTERVAL partition is possible on DATE or NUMBEr . Column PROFILEIts is of VARCHAR2 datatype. I know still I can partition as Oracle internally convert varchar2 to number while inserting data. But INTERVAL is not possible.  How to RANGE partition on PROFILE ? Column CREATED_ON :It is of NUMBER with decimal

View 0 Replies View Related

SQL & PL/SQL :: Find Out Columns In Database Which Are Not Used

Jul 1, 2013

How to find in the oracle10g database if there is any column which is not being used at all.If the data is there in the column,how to check when was it last populated.

View 3 Replies View Related

SQL & PL/SQL :: How To Find Tables Which Does Not Have Specific Columns

Jan 11, 2013

I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.

View 8 Replies View Related

Query To Find Out Whether Exactly 2 Columns Are Used In Any Index

Dec 17, 2012

Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index

Ex. create index test1 on customer (ID , Status )

But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?

View 23 Replies View Related

Group By Multiple Columns / Count And Then Find A Max

May 24, 2008

I have three tables,let's say

table stores
sid | store_name
1 | one
2 | two
3 | three

table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree

table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2

and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)

View 5 Replies View Related

Server Administration :: How To Find Unused Columns

Nov 9, 2011

is there a way to find out which unused columns in oracle?

View 8 Replies View Related

PL/SQL :: Find Common Data In 2 Columns In Two Different Tables

Oct 22, 2012

query to find out common data from 2 columns in two different tables??

View 6 Replies View Related

PL/SQL :: Find Out Column / Combination Of Columns From Given Table

Sep 23, 2013

Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.

View 10 Replies View Related

SQL & PL/SQL :: Cursor For N Number Of Columns

Apr 21, 2010

in retrieve column data in the cursor.My requirement is I created a table dynamically as I don't know how many fields will be there.And the table structure would be like this

Filed1 varchar2(10)
Filed2 varchar2(10)
-----------
-----------
Filedn-1 varchar2(10)
Filedn varchar2(10)
[code]...

As in the cur.filed value.

View 1 Replies View Related

SQL & PL/SQL :: Query To Find Missing Dates Between Two Columns In Given Month?

Sep 26, 2012

I need query to find the missing dates between two columns in given month.

CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);

[code].....

Required output is

MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788

View 8 Replies View Related

View With Variable Number Of Columns

Oct 21, 2011

I've created a stored procedure which creates itself a view (a MV to be honest); the instructions to create this m.view are dinamically built insinde my procedure, so each time i run it, based on the different input parameters, i've got a different result (my output m.view can have three colums the first time, or ten the next time) how can I read my output view to put the data into file? I've tried with "select * bulk collect into my_array from my_ output_ view"...after declaring "my_array" as a varying array with the max number of colums I could ever have...but nothing: if the array dimension doesn't match the number of columns that i've on my view, i.e. i receive "ora-00947 not enough values" error.

Is there a method to dimension dinamucally the array to store my data? Or should I change the code to fetch some other way the data i need to put to a file?

View 2 Replies View Related

Query To Know Number Of Columns In A Table?

Apr 4, 2008

query to know number of columns in a table i.e.

if I want to know how many number of colums are present in a specific table then what would be the query.

View 1 Replies View Related

SQL & PL/SQL :: Checking Variable Number Of Columns?

Apr 12, 2010

My goal is this:I have a table which is being updated/changed by lay people with certain types of values. Constraints are given to them, but they need not conform to them as they update the table in excel.Now, I want to validate this table every-time before I use it. i.e. implement a script which can be run to verify is all the values are in the right format for further usage.

I have a variable number of columns (i.e. users can add further columns as their requirements change).From columns 3 to 'n' (depending on table given) the values should be 'Yes' or 'No'. How do I check this for a variable number of columns in PL/SQL?

View 11 Replies View Related

Precompilers, OCI & OCCI :: How To Get Columns As NUMBER(30)

Aug 13, 2008

in my oci applications,if i get a column of number that is in the scope of int,i can use value = *(int *)field.data; get the value,but if the column size is larger than 10,the code can't be available,how can i get the value.

View 6 Replies View Related

SQL & PL/SQL :: Query Two Columns With Datatypes As Number And Varchar

Jul 17, 2012

Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.

View 4 Replies View Related

SQL & PL/SQL :: How To Pass Different Number Of Columns Dynamically To A Query

Apr 26, 2010

I want to pass Number of columns dynamically to a query. I got success in SQL.

SQL> select &column_list from emp;
Enter value for column_list: empno,ename,sal

EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300

14 rows selected.

But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.

View 15 Replies View Related

SQL & PL/SQL :: Update Number Of Rows In Three Columns In Table?

Dec 13, 2011

how to update the middle of plenty rows in the middle of the columns

sample_data

id name state REGION LOC
1 v A.p 1 1
2 a
3 g K.A 0 3
4 y
5 i T.N 1 0
6 l M.P 0 1
7 c U.P

This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,

View 8 Replies View Related

Server Administration :: How To Find Non-indexed Queries And Columns That Require Indexes

Sep 1, 2011

How to find non-indexed queries and columns that require indexes?

View 3 Replies View Related

SQL & PL/SQL :: Add Variable Number Of New Columns To Existing Table Temp

Feb 24, 2010

I want to add a variable number of new columns to an existing table temp (with column provided).

Example:

NewColumnNo = 4
-> the columns shall be named rate_1, rate_2, rate_3 and rate_4
-> the values shall be = Column / NewColumnNo

The result shall be like this:

create table temp_res (prodid integer, rate_1 number, rate_2 number, rate_3 number, rate_4 number);
insert into temp_res values(1, 0.25, 0.5, 0.75, 1);
insert into temp_res values(2, 0.25, 0.5, 0.75, 1);
insert into temp_res values(3, 0.25, 0.5, 0.75, 1);
insert into temp_res values(4, 0.25, 0.5, 0.75, 1);
insert into temp_res values(5, 0.25, 0.5, 0.75, 1);
insert into temp_res values(6, 0.25, 0.5, 0.75, 1);
insert into temp_res values(7, 0.25, 0.5, 0.75, 1);
insert into temp_res values(8, 0.25, 0.5, 0.75, 1);
insert into temp_res values(9, 0.25, 0.5, 0.75, 1);

View 8 Replies View Related







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