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';
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?
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
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.
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;
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
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.
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)
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.
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
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
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?
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?
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.
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.
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.
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,