PL/SQL :: Pick All Continues Range Of Values Into One Bundle
Jul 11, 2013
I got a situation to develop a complex query. Here is the scenario. I have to pick all the continues range of values into one bundle. like in following example, rows having values between 20 to 25 should have one bunch and 27 to 28 should have another as there is no from or to value from 25 to 28. with q1 ( select 20 as Frm, 25 as tto from dualunion allselect 20 as Frm, 21 as tto from dualunion allselect 20 as Frm, 22 as tto from dualunion allselect 20 as Frm, 20 as tto from dualunion allselect 21 as Frm, 22 as tto from dualunion allselect 28 as Frm, 28 as tto from dualunion allselect 27 as Frm, 28 as tto from dual)select * from q1;
View 10 Replies
ADVERTISEMENT
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
Mar 19, 2012
i have to pick single value from the given range based on value entered by user, for example if user enter the length 2499 it should be considered as 2000 if he enters 2000 its 2000 or he enters 2501 it will return 2500 , i have written a huge if condition for this purpose in my program which looks very cumbersome,is there a simple to do this other than this method.
the code is as below.
[CODE]
IF
( :CSDO_STORE_RECD_LENGTH - SUBSTR(:CSDO_STORE_RECD_LENGTH,(LENGTH(:CSDO_STORE_RECD_LENGTH)-1),LENGTH(:CSDO_STORE_RECD_LENGTH))) =2000
THEN M_RCVD_LENGTH := 2000;
ELSIF
[code].....
View 9 Replies
View Related
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
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
Mar 15, 2012
I am using PL/SQL Developer.I have two tables: A and BTable A contains serial_from and serial_to values.This is used to define the serial numbers issued to customers (i.e. the start and end range of serial numbers issued).Shown here for a client:
Table B contains the individual numbers, B.serial (within the serial_from and serial_to range) along with other data, and is only created when the serial is used by the client.
I would like to create a list of records for individual clients containing serial numbers issued but not used. i.e. they are in between the serial_from and serial_to values in Table A, but not in Table B.
How can I create a list of numbers issued, but not yet used? Because Table A contains only two values (to and from) no record exists for them, so how do I generate a list and check against it?
View 3 Replies
View Related
Jun 8, 2010
I bought Selftestsoftware for 1z0-147 for 9i and 10g. Selftestsoftware is endorsed by Oracle, should be high quality. But its below sample question and answer seem to be wrong, or I could not understand it properly. It says "Network traffic is not decreased by bundling commands. Executing procedures and functions stored in an Oracle Developer application will process each PL/SQL statement and pass each SQL statement across the network to the database to be processed, dramatically increasing network roundtrips."
But PLSQL online reference manual says "However, with PL/SQL, an entire block of statements (bundling commands?) can be sent to Oracle at one time."
Selftestsoftware says pass each sql statement, but PLSQL online manual says send an entire block of statements like below diagram. Now which one is right, or I misunderstood something?
(I cant post the diagram from PLSQL online manual because of my quota)
Procedures and functions can be created and stored in the database or in an Oracle Developer application. How is performance improved when storing procedures and functions in the database?
Network roundtrips are reduced.The object code is created during execution.Network traffic is decreased by bundling commands.The source code is stored externally, and the object code is stored internally.
Explanation:Network roundtrips are reduced when storing procedures and functions in the database. The source and object code of database stored procedures and functions are stored like other objects, in physical files assigned to the appropriate tablespace. When executed from a client application, they require only one call. Because the object code is stored on the database side, there is no need to send it across the network.The object code is not created during execution. The object code is created when creating the procedure or function and is stored in the database.Network traffic is not decreased by bundling commands. Executing procedures and functions stored in an Oracle Developer application will process each PL/SQL statement and pass each SQL statement across the network to the database to be processed, dramatically increasing network roundtrips.The source code is not stored externally. The source code and object code are stored in the database.
View 3 Replies
View Related
Mar 2, 2013
I'm writing on behalf of the MacPorts package management system. I am trying to update the MacPorts port of Oracle Instant Client from 10.2.0.4.0 to 11.2.0.3.0 [URL}...and while many of our ports including the PHP oracle module continue to work fine with this version, soci [URL}.... and TOra [URL]...
ld: can't link with bundle (MH_BUNDLE) only dylibs (MH_DYLIB) for architecture x86_64
This is on OS X 10.8.2 compiling with Xcode 4.6's version of clang ("Apple LLVM version 4.2 (clang-425.0.24) (based on LLVM 3.2svn)").I suspect this is because soci and TOra are doing something with static libraries, and Oracle Instant Client is only shipped as a dynamic library. But I can't explain why this worked with 10.2.0.4.0, unless something has changed about how Oracle Instant
Client is being built. It's not clear whether this is an intentional change in Oracle Instant Client, and if it is, what developers should do to fix their software.I have not heard back from the developers of TOra, and the developers of soci
View 3 Replies
View Related
May 29, 2013
My client asked me to "apply the newest patch" on his RAC 10.2.0.5 environment on Windows.
I've downloaded 10.2.0.5 Patch 20 (the newest version).
My question is: Should i apply it using OPatch to both: ORACLE_HOME and CRS_HOME? or only in ORACLE_HOME? What with 10.2.0.5.2 CRS Patch? Should i apply earlier CPU patches?
View 1 Replies
View Related
Oct 12, 2010
create table test1(x number, y date)
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
[code]....
I need an sql which would pick 3% of the records per day
select count(*), y from test1
group by y
21 10/1/2010
14 10/2/2010
6 10/3/2010
So I need 3% of 21 (.63) randomely picked up for 10/1.
similarly 3% of total number of rows from 10/2, 10/3 to be picked up.
View 2 Replies
View Related
Jul 19, 2010
I have need, where I need to pick-up the one of the records out of 2, which have all the fields same but one key column? how should I do it?
sample data:
IDnamecreation dateupdate date
121233ATR7/19/2010 15:307/19/2010 15:30
1213344MTR7/19/2010 15:307/19/2010 15:30
For most of the cases, i am picking latest data using update date, but here where dates are same, I am stuck.
View 2 Replies
View Related
Oct 17, 2012
I have a 2 node RAC environment (11.2.0.3) where each node has there own local Grid_home and RDBMS_home.
I am installing a Rolling Bundle Patch with OPatch in this environment. The installation document says that "The order of patching in RAC install is GRID_HOME, then RDBMS_HOME" so i did the following.
1. stopped all oracle related services on node1
2. set oracle_home=<Grid_home>
3. applied the opatch
4. opatch succeeded on node1 and it says "The node 'NODE2' will be patched next... Is the node ready for patching?
1. Should i shutdown the oracle services in Node2 and continue to patch the Grid_home ? If yes then the DB will be completely down for user access. This defeats the purpose of rolling mode which says there is no downtime.
2. Should i patch the RDBMS_home on node1 , start all the oracle services on node1 , stop the oracle services on node2 and then resume the opatch on node1 which is waiting to patch the Grid_home on node2 ?
View 4 Replies
View Related
Jul 9, 2010
I have a reqmt that is little complex.
Example: (Real Data)
Table1 (Column Name)
Height
Width
Color
Table2
ID,Height, Width, Color, Remarks
1,2,3,NULL,NULL
2,NULL,NULL,2,NULL
3,1,2,3,4
What I require is pick up the data from table1 and for each row in table1, check the corresponding column to see if null. If null create a counter and add 1 to it.
I would only check for 1 record at a time in table2 (ID is input parameter in pl/sql) In other words, if my ID is 1, my count should be 1 (since COLOr is null in table2 for id=1 and color is a row in table1) if my id is 2, count is 2 (ht and width are NULL and are both separate rows in table1) if id is 3, count=0 since all fields have data even though some of these columsn exists as rows in table1.
View 25 Replies
View Related
Jun 18, 2013
How to pick excel data in oracle form 6i. and data show in forms column ?
View 1 Replies
View Related
Jun 13, 2010
I want to make a trigger , to store values that are affected during any DML(insert,update,delete).
CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE
[code]....
View 8 Replies
View Related
Sep 1, 2010
Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this
name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010
I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1
For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'
View 3 Replies
View Related
Jul 25, 2013
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies
View Related
Dec 3, 2010
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
View 3 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Oct 28, 2013
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
