SQL & PL/SQL :: Show Result Random
Mar 24, 2010
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.
View 12 Replies
ADVERTISEMENT
Aug 17, 2010
I made one code for understanding purpose its complied successfully, but not show result...its give me message procedure successfully completed.
Code is :
BEGIN
For emp_rec in (SELECT * FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
END LOOP;
END;
/
View 4 Replies
View Related
Jul 20, 2011
me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.
View 1 Replies
View Related
Mar 22, 2010
i have a little curiosity in result of a select statement.
One database has following characterset settings:
NLS_CHARACTERSET WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
BUP
OPEN
Another database has following characterset settings:
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
and the same select shows only a 2 line result:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
View 3 Replies
View Related
Jun 26, 2012
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
View 16 Replies
View Related
Jul 1, 2010
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 ?
View 3 Replies
View Related
Jul 6, 2010
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.
View 27 Replies
View Related
Jan 19, 2012
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] ...........
View 8 Replies
View Related
Oct 21, 2013
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 Related
Jun 25, 2011
I 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.
View 6 Replies
View Related
May 8, 2012
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..)
View 2 Replies
View Related
May 24, 2010
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...
View 5 Replies
View Related
Apr 20, 2010
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.
View 4 Replies
View Related
Jun 13, 2013
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?
View 2 Replies
View Related
Jan 28, 2011
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;
View 1 Replies
View Related
Sep 29, 2008
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 Related
Nov 5, 2012
I 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
View 6 Replies
View Related
Mar 4, 2010
I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4
eg: - 23115645
after masking should be - xxxx5645,
create table test1(x varchar2(12))
insert into test1 values('34567745')
/
insert into test1 values('438765311')
/
insert into test1 values('1112345')
/
commit
[Code].....
y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx
I tried the above update, but using substr, i am able to get this if x has 9 characters
not sure how to achieve this if x has 7, or 4 characters
View 22 Replies
View Related
Mar 1, 2012
I have a table which have three column.
1) empid
2) date
3) status
I want to show the value empid intime outime.
select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend
where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1,
(select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012'
order by empid,atttime desc) out
where in1.empid=out.empid and in1.empid='02256'
order by in1.atttime,out.atttime
But this query do .one value relation with all column.means first february in time with all out time.
View 1 Replies
View Related
Feb 17, 2011
i am using oracle 10 forms ! in report i have used form Query "select sum(sale_price) from (table) expense. i attached it to oracle forms but at that time it does nt show any value!
View 3 Replies
View Related
Jan 20, 2011
I am using 10g database on Microsoft Windows family OS
when i run following query
sql> SELECT CURRENT_TIMESTAMP FROM DUAL;
then i get result
20-01-2011 11:52:17.921000 AM +05:00
but i want to get result as
20-01-2011 11:52:17.921256 AM +05:00
is there any way to get up mentioned result.
View 1 Replies
View Related
Apr 8, 2013
i want to show data quarterly.My quarter structure is 3 mOnths.
q1 - apr-2013 to jun-2013
q2 - jul-2013 to sep-2013
q3 - oct-2013 to dec-2013
q4 - jan-2014 to apr-2014
I input parameter is from date and to date..
Suppose user enter from date - '01-apr-2012' and to date - '01-aug-2012',
only q1 shows..
and user enter from date - '01-apr-2012' and to date - '30-sep-2012',
only q1,q2 shows..
how i do that..My data is like this
create table t1( t1_month VARCHAR2(10),
t1_val1 number(10),
t1_val2 NUmber(10)
);
INSERT INTO t1('APR-13',100,200);
INSERT INTO t1('MAY-13',100,200);
INSERT INTO t1('JUN-13',100,200);
INSERT INTO t1('JUL-13',100,200);
INSERT INTO t1('AUG-13',100,200);
[code]...
View 2 Replies
View Related
May 19, 2010
How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.
For Example:I have the data as shown below,
Start date End date
1-Jan-10
3-Mar-10
3-May-10
5-Jul-10
1-Aug-10
I wanted to output as shown below
Start date End date
1-Jan-10 2-Mar-10
3-Mar-10 2-May-10
3-May-10 4-Jul-10
5-Jul-10 31-Jul-10
1-Aug-10 1-Aug-10
View 10 Replies
View Related
Mar 31, 2004
In MySQL, I can get a list of all tables w/ this query: "SHOW TABLES"
Whats the syntax w/ Oracle 9i ?
Looked through the docs online, but w/o knowing what you're trying to look up, you can't really look anything up (kind of a catch22).
View 9 Replies
View Related
Feb 7, 2013
I have below mentioned records in mount so whenever i select any mount it should now show the the last row of that mount i.e. test no-3 any records of mount which will not show last row of test number
mount day org1 test_No org2 org3 org4
299.00 1 0 1 1 0 7186
299.00 1 0 2 1 0 7186
299.00 0 3 3 0 7186
[code]...
but i want test_no should be in asceding order
View 3 Replies
View Related
Jan 31, 2013
Though I have it all configures:
On node1:
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.28.155.79)(PORT=1521))))
[code]....
View 9 Replies
View Related
Dec 16, 2010
Is there any way to get audit_trail.VALUE? I know the statement: show parameter audit_trail; can show that value. But, i want to build a function which will return a string that contain audit_trail.VALUE
View 1 Replies
View Related
Oct 4, 2010
MyTable has missing records:
id_group id_column
-------------------------
1 1
1 2
1 3
1 5
1 9
2 1
2 2
2 4
2 6
As you see, it could be missed record 4,6,7 and 8 of group 1 and record 3 and 5 of group 2. That's is an example.
I need to show all sequential records, incluing missing ones, like this:
Id_group Id_column
-------------------------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6
I try this query:
/*---*/
Select G.id_group, C.rownum id_column
from (select distinct id_group from MyTable) G
cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
max(id_column) from MyTable)) C
/*---*/
but it shows record 7, 8 and 9 as members of group 2.
View 9 Replies
View Related
Feb 7, 2012
SQL> select * from t1;
NAME
----------
JACK
JOHN
JENN
SQL> select * from t2;
NAME
----------
JACK
PAUL
SQL> select a.name from t1 a, t2 b where a.name <> b.name
2 order by name;
NAME
----------
JACK
JENN
JENN
JOHN
JOHN
I would have expected to see the following:
name
-----
JOHN
JENN
PAUL
how to fix this query? In additon, is there a way to print the table name or some arrows (>>, <<) to show which table the values came from
I.e
name
=====
JOHN t1 or <<
JENN t1 or <<
PAUL t2 or >>
View 14 Replies
View Related
Jun 21, 2011
I use oracle 11g r2
i have created a table for objects
create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);
and inserted many values (examples below )
INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
View 2 Replies
View Related
