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
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?
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 need to display the data as range , i have created one table along with inserts as below , i need the desired output as mentioned below.The range will start from field STMH_PM_CODE , it will get the first and last stmh_pm_code and stmh_pm_desc which are in the same combination of STMH_NO,STMH_JOB_NO,STMH_batch_no and stmh_rev_no,
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1001','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002',FRAME','0'); ERROR: ORA-01756: quoted string not properly terminated
SQL> ED Wrote file afiedt.buf
1* INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002','FRAME','0') SQL> /
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1003','COLUMN','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0002','1107040-1004','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1005','FRAME','0');
1 row created.
SQL> COMMIT;
Commit complete.
-- i want it as below see the line in stmh_pm_desc the description gets added until there is a change in either stmh_batch_no or stmh_rev_no since we have stmh_pm_code 1107040-1004 with stmh_batch_no 0002 it goes to other line as a seperate new range and first one will stop with 1107-1003 as that is the last combination and new line or range will get added over again.
BANNER -------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - ProductionSET DEFINE OFF; [code]....
10 rows selected.I want the output like as follows, all those missing date i need to carry on the last one's number
The date value I have created for this sample is monthly, based on the condition the data value I may need to generate weekly also. That's Monthly or weekly either one.
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE: CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT ( CALENDAR_KEY INTEGER NOT NULL, DAY_TIME_KEY INTEGER NOT NULL, SITE_KEY NUMBER NOT NULL, RESERVATION_AGENT_KEY INTEGER NOT NULL, LOSS_CODE VARCHAR2(30) NOT NULL, PROP_ID VARCHAR2(5) NOT NULL, [code].....
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS del_records NUMBER:=0; BEGIN DELETE FROM candidate c WHERE empid in (select c.empid from employee e, candidate c where e.empid = c.empid and e.emp_stat = 'TERMINATED' ); [code]....
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description from niku.srm_resources res, niku.pac_mnt_resources pac, niku.departments dep where res.unique_name = pac.resource_code and pac.departcode = dep.departcode and res.is_active = 1 and description like 'IMS%' and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be: 1. Identify Support Project 2. Identify Resources allocated to the project team 3. Compare with List of Support Resources 4. If any Resources in that list do NOT appear on the project, then return project.
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
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,
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 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]%' ;
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.
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.
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 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.
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.
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')
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 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.
I am trying to run copy command to copy data from one database to other.I 'm getting this error while running copy command. "ORA-01727: numeric precision specifier is out of range (1 to 38)"
