I have 2 question....
1. i have a table and i want to pick data from one field randomly..
i write this query for this purpose...
SELECT SUBJECTIVE_QUES_PK FROM
(SELECT SUBJECTIVE_QUES_PK FROM PMS.SUBJECTIVE_QUES
ORDER BY SYS.DBMS_RANDOM.VALUE)
WHERE QUES_TYPE_FK=3
AND ROWNUM<=5;
2. can i write this query in forms 6i..
i know that this question not relevant but if i write it in forms section then i will be duplicate.
I need to generate random and unique 6 digit number in Oracle. I need to insert these numbers into a table. I tried using DBMS_RANDOM package, which generates random 6 digit numbers, but fails to generate UNIQUE numbers.
View 3 Replies View RelatedI need to generate random numbers from the range 71 million to 90 million. While generatind the same iam getting the value with decimal places.
How to generate with out decimal places ?
SQL> select ABS(dbms_random.value(71000000,90000000)) from dual;
ABS(DBMS_RANDOM.VALUE(71000000,90000000))
-----------------------------------------
87283730.7
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
how do you generate serial numbers? i have an invoice where i have to add items, so i want the numbering to start from 1 everytime i start a new form and increment by 1 as i i enter a new record. For this i have put my code in when-new-record-instance but problem is if i delete a record and enter a new one, the serial number starts from the nex number.
For ex. if i have 4 items with serial numbers 1, 2, 3, 4 and i delete the item at no. 3 and add a new item , the serial number comes as 5.
proper code to generate the serial numbers?
I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.
create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));
insert into tb_test
(select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');
I am having ORA-00937 : not a single-group group function error
How to generate a PAN number by concatenation of data from different tables
View 3 Replies View RelatedI want the query to generate the sequenctial number from 1 for the new financial year in some query.
View 6 Replies View Relatedwhich method is more efficient in terms of performance to generate an auto increment number in oracle.
Step1
CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Step2
CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;
[code]....
generate the number of rows based on table field.
Sample code is given below.
CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING
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 table called 'orders' and having date and time of order placed in the field timeplaced. I need to generate a report which is having hourly based number of orders placed . If no order is placed in an hour then it should show 0 for that hour in the result .
create table orders (order_id integer, timeplaced date,last_updated date);
Insert into ORDERS
(ORDER_ID,
TIMEPLACED, LAST_UPDATED)
Values
(1, TO_DATE('10/11/2011 12:53:39', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2011 10:53:39', 'MM/DD/YYYY HH24:MI:SS'));
[code]........
But I need the result for all the 24 hours. Actually row for the hour 1,2,3,4,5,6,8,9,13-23 are not present in the DB
TO_CHAR(TIMEPLACED,'HH')COUNT(*)
00
10
20
30
40
50
60
71
80
90
102
111
122
130
140
150
160
170
180
190
200
210
220
230
I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?
for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?
I run follwing query.
select * from crp_00_08
it shows result like
BAS_CODBAS_DES
1MAIN GATE (FRONT)
2NEAR MILL # 3, 3-1
3NEAR MILL # 3, 3-2
4NEAR MILL # 3, 3-3
[Code]...
i want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,
i mean whenever i run above query,order of bas_cod must be change from last.
how can i do it.
I am trying to create a csv file with summarized data. We have a huge table with claim details that is constantly being updated. I am pulling a subset of records that match my criteria into a tempory table (not technically an Oracle temporary table, a regular table that will only exist until I drop it when I am done). This table has multiple entries per claim with different effective dates, paid dates and amounts paid. The result file needs to have one entry per claim with the oldest effective and paid dates and the total of all of the amounts paid on that claim.
Originally I was doing create table new_table as select claim_nbr,other data...,min(ymdeff),min(ymdpaid),sum(amtpay) from my_table group by claim_nbr,other data...
If I ran a select sum(amtpay) from my_table and select sum(amtpay) from new_table I was not getting the same results... If I ran select count (*) from (select distinct claim_nbr from my_table) and select count (*) from (select distinct claim_nbr from new_table) or select count (*) from new_table I was getting the same number of rows. So I wasn't completely losing claims from one table to the next, just some of the details. So, I tried running this:
select * from
(select claim_nbr,sum(amtpay) paysum from my_table
group by claim_nbr
order by claim_nbr) m,
(select claim_nbr,sum(amtpay) paysum from new_table
group by claim_nbr
order by claim_nbr) n
where
m.claim_nbr = n.claim_nbr and
m.paysum <> n.paysum;
It came back with the claim number causing the issue. I looked at all the entries in my_table for that claim and every field was identical except the ymdeff, ymdpaid and amtpay. There were 4 records in my_table however the amtpay in new_table was only a sum of 2 of the records... I our admin look over my shoulder to see what was wrong and they wanted me to recreate new_table. So I dropped new_table and ran the exact same SQL to recreate the table. The number of distinct claim numbers was still the same in both tables and the sum of new_table was off but not by the same amount. I ran my comparison to see which claim was off and now there were two claims where the totals didn't match and neither were the same as the claim that was wrong that first time. We dropped new_table and recreated it several times and every time we got different results... No one else knows the name of my_table so no one was messing with it at the same time plus the sum of amtpay in my_table always comes back the same.
Our admin said he thought he remembered there being something "funny" with the min function sometime so he had me remove those fields. Ran the query several times and the total came out correct each time. Well I still need the dates so I came up with another way (very convoluted) using subqueries and ranking. It seemed to work at first then it started losing random numbers of claims (fewer rows in new_table than distinct claims in my_table) or keeping all the claims but dropping detail lines like I had using the min functions.
Here is the backwards way around using min that drops whole claims sometimes but works fine other times:
CREATE table new_table
as
(select claim_nbr,other data...,amtpay,ymdeff,ymdpaid
from
(select claim_nbr,other data... ,sum(amtpay) amtpay
from my_table
group by claim_nbr,other data...
[Code] ...........
i have a parameter form where user will input from item to item and items within this range will be come as output , but i want an option to select the random items from the list, like i may not select all the items from 1 to 10 , but instead i want to pick or select randomly , i may opt for 1,3,4 and 7.
View 4 Replies View RelatedI am in urgent need of Generating Random numbers for one of the application .The number once generated is getting stored in table
For this purpose i am using Oracle In-Built function
Quote:Select round(dbms_random.value(1,30)) into a from dual;
Problem is :How to eliminate those numbers to generate which are already stored in Table.
I wanted to create shift rota for the employees as per the below conditions.
1). Shift should be rotated.(it should check previous 2 shifts and assign to another shift).
2). The same employees should not be allocated for the next shift .
Ex : Now jones, jekku, henry and villiams are in "C" shift . So the same employees should not be allocated to the next shift, it should be random. Some of these employees should be allocated to "B" and some employees should allocated to "A" shift....
Below are the SQL statements to create the table and input the data..
create table resource_details
(empno number(5) ,
ename varchar2(30),
shift char(1));
alter table resource_details add constraint res_pk primary key(empno) ;
insert into resource_details values(51370 , 'Finney' , 'A') ;
insert into resource_details values(51371, 'Jerry' , 'B') ;
insert into resource_details values(51372, 'Jones' , 'C') ;
insert into resource_details values(51373, 'Smith' , 'A') ;
insert into resource_details values(51374, 'Krishna' , 'B') ;
insert into resource_details values(51375,'Chenna','A');
[code]....
I have tried with employee number to check which shift he is/was in & assigned the shift accordingly , but here shift is getting rotated but not employees(same employees are going to the next shift, which should not be accepted..)
I have this requirement in Oracle FORMS ver 6i where I populate some records in a table in a datablock and display them on the screen. A facility needs to be provided to the end users to select the records randomly by just entering the count of the records in a non-d/b text field. The number entered in the count field will decide how many records are to be selected randomly.
I tried using the query -
UPDATE <table a>
SET <col 1> = <value 1>
WHERE rowid IN (SELECT rowid FROM (SELECT * FROM <table a> ORDER BY DBMS_RANDOM.VALUE)
WHERE <col 2> = <value 2>)
/
which I found on the link [URL] but while compiling, the Form gives a compilation error for the ORDER by clause - i'm sure there's no syntax error in the way I have written it...
i need to copy data from certain table's from one DB to another at random intervals. Table structure for the one's getting copied can be same in both the DB.
After reading various posts here i have understood that it can be done using Oracle Replication and Oracle stream.
how these 2 methods work and how they are different from each other.
We have a pie chart which has select like
select null, '0-10' Days , count(*) from tableunionselect null, '11-20' Days, count(*) from tableunionselect null, '21-30' Days, count(*) from tableunionselect null, '>30' Days, count(*) from table
The 3d pie chart was showing the slices is the correct order 0-10, 11-20, 21-30, >30 but now shows it in random order. how to get the order by working. I suspect since the Days column in the select is a Character column this issue is occurring, however, with the restrictions in the number of select items we can have in the Apex charts, was wondering how do you get the order by working?
DECLARE
P_LAST_UPDATE_DATE KPC_MMS_STD_CHKPOINTS.LAST_UPDATE_DATE%TYPE;
BEGIN
INSERT INTO KPC_MMS_STD_CHKPOINTS (CHK_POINT_CODE,CHK_POINT_DESC,CHK_POINT_FREQUENCY,CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
VALUES (:P13_CHK_POINT_CODE,:P13_CHK_POINT_DESC,:P13_CHK_POINT_FREQUENCY,:P13_CREATED_BY,:P13_CREATION_DATE,
:P13_LAST_UPDATED_BY,:P13_LAST_U PDATE_DATE);
COMMIT;
END;
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.
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting
For example i/p asdfasfsdS251432dasdasd o/p should be S251432
I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?
View 1 Replies View Relatedhow 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 Relatedmy column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?
View 6 Replies View RelatedI have a requirement to generate a report in csv file, attached scripts, sql query and actual output and expected output.
In the query, I am hard-coding month&year, when I run in march 2012 it should include march 2012 in output (similarly for every month going forward), to do that I have to modify my query to include march 2012 & preceding months in every individual query, this report needs to be generated once every month.
I am looking for a generic solution which does not need to be modified every month, also in the output,under "Mail File" data should be in ascending order, in the "Total Mailed" all the months should have grand total.
oracle version : 10.2.0.1.0
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options
How i can get all possible combinations of a given value using SQL or PL/SQL
for example:
1234 - input value
4321
2314
2143
3412
and so on