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.
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.
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:
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.
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.
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 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...
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.
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..
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.
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?
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.
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
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.
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!
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
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
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.28.155.79)(PORT=1521))))
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
/*---*/ 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.
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
