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 Repliesi am facing a problem in functionality of a form as there are different search parameters:
date from: text item-->mandatory
date to: text item---->mandatory
house type: list item-->optional
form_status: list item->optional
order by: list item---->optinal
untill i don't select house type by list item everything is working fine and gives desired result, even with house type if i select any house type result is fine but as i select last list item of house type which is null as i want result without house type then it gives no any data as data is present in database.
for example:
date from: 20-05-2009
date to: 21-05-2009
with these two i got the correct result, then i select
house type: 3
i got the correct result and when i change list item to any value like: 1,2,3.....it is working fine but as i want result without house type by selecting the null value in list item as:
date from: 20-05-2009
date to: 21-05-2009
it is not picking any data from database. but the same search criteria in 1st case as i described above giving result.
declare
qry varchar2(5000);
n number;
alert number;
[code]...
How to get the label (not value) of current selection in a List Item.
View 7 Replies View RelatedI have a requirement to select multiple values from a list item and do some manipulations with those values. I tried by integrating the LOV with a text item, but couldn't able to select multiple items. Is there a way to do multiple selection in a list item ?
View 1 Replies View RelatedI 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 have a question regarding a selection limit from a list of values. The table where my IDs (unit_codes) come from has another column called 'points'. each ID has its own number of points. Is there a way to put a selection limit on the display field that points will be placed on?
FOR EXAMPLE: a course block has a selection limit of 70 points, A person named someguy selects :
music = 20 points
maths = 20 points
mathsXTRA = 40 points
**ALERT!!! - U can ONLY select 70 points worth of units**
I have to create a functionality on a button through which all nodes of hierarchical tree will be selected.
I know that this is possible with mouse (one-by-one+Ctrl). But,I have to done this on button.
How to create multi selection tree in oracle forms ?
View 1 Replies View RelatedI am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
I have created the below stored procedure and calling the procedure in when-button-pressed trigger. Problem here is that when I cancel the file selection in file open dialogue box its raising exception.
PROCEDURE TEST
IS
buffer_lines client_text_io.file_type;
v_outputstr VARCHAR2 (32767);
p_delimiter VARCHAR2 (10) := '","';
v_transaction_no VARCHAR2 (10);
BEGIN
[code].......
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 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 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 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 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 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 am trying to develop an ongoing interview schedule.
interviews may be held on FRIDAY, SATURDAY, TUESDAY or any other day or Days in a every week selected by the user and also at some specific time.
we need to schedule the closest comming day based on Application date.
for example: today is WEDNESDAY and our schedule interview day is FRIDAY and SUNDAY. If one application is received today we need to schedule interview on FRIDAY because this is the nearest/closest day of WEDNESDAY. and if the application is received on Saturday then of course Sunday will be scheduled.
if Application is received on same day e.g. FRIDAY is scheduled and we receive Application on FRIDAY then comes the concept of time. interview is schedules on FRIDAY 11:30 AM.
if application is received on before 11:30AM, the same day will be scheduled. If application is received after 11:30 AM then of course next closest day will be scheduled.
I'd like to select one of two fields from a table, based on which of them is greater for that row.
e.g. For every row in JOINING_TBL, I'd like to select either JOINING_DATE or REJOINING_DATE, whichever is greater. I do dream about being able to use a Java-like ternary operator in here, something like this:-
SELECT (JOINING_DATE>REJOINING_DATE?JOINING_DATE:REJOINING_DATE) FROM JOINING_TBL
However, I understand(through other fora) that this can't be used in here.
we are currently migrating from forms 6i to 11g. We would like to cleanup our global variables at runtime.
Is there any way to list the global variables at runtime?
Problem:
Our term (strm) is dictated by the term_begin_dt and term_end_dt dates but I want to keep selecting that term until 1 week before the next term opens and then switch to that term.
Basically, I don't want any gaps between a term.
Output:
select strm when sysdate is between term_begin_dt and term_end_dt (strm would equal 3943)
select strm until 1 week before the start of the next term (4027) (strm would equal 3943)
select strm when 1 week before term_begin_dt (strm would equal 4027)
Repeat for the next term and so on 12/16/2011
select strm
from term
where trunc(sysdate) between trunc(term_begin_dt) and trunc(term_end_dt)
Output: 3943
12/17/2011 - 01/01/2012
select strm
from term
where ?
Output: 3943
01/02/2012 - 5/4/2012
select strm
from term
where ?
Output: 4027
Repeat.
Test Case:
CREATE TABLE TERM
(
STRM VARCHAR2(4 BYTE),
DESCR VARCHAR2(20 BYTE),
TERM_BEGIN_DT DATE,
TERM_END_DT DATE
)
Insert into TERM
(STRM, DESCR, TERM_BEGIN_DT, TERM_END_DT)
Values
('3943', '2011 Fall Semester', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TERM
[code].....
I've got a Source Data in complex relational formFor reporting purposes, a simpler, less normalized data model is neededThere are two Target views from the Source Data: one of them with full access to all datathe second one with access only to a subset of the data (same columns, but not all the records)For both target groups, a separate schema shall be available, each containing only relevant dataToday, these schemas are physically located on the same DB instance and host as the source dataA daily refresh is sufficientA later relocation of the reporting schemes to other DB instances shall be possible without major changes neededOracle 10g should be used I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem: when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.
I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).
in one query i am selecting 3 columns from emp table
1)select ename,empno,dname from emp where ename='ABC'
in other query i am selecting 2 columns
2)select ename,empno from emp where ename='ABC'
my question is - if i added dname with the above two columns will it effect any performance of the query.
n.b-suppose dname is not a mandatory column for selection
how to select multiple rows in an OAF table and access them in the code.
View 0 Replies View RelatedI have created three accordians where there will be sub tabs under each Accordian. There will be two logins. Where when i login with Admin all the 3 accordians will be shown. But in the user logins 1st Accordian will be hidden and only 2nd & 3rd will be shown with their sub tabs.
In order to highlight the tab & also for Accordian to be open based on the selection, i wrote code as follows
$("#R16463536713741058563").accordion('activate',0);
in the page under the Execute When Page Loads region where ('activate',0) means the position of the accordian which will be taken as 0,1,2..etc.But when i login as user then as the 1st accordian will be hidden & the 2nd and 3rd accordians will become 1st & 2nd. So 1st & 2nd accordian wil be consider as 0,1 positions here.
So when i click on the 2nd accordian tabs then the accordian is not opening because in the pages of this accordian tabs i have given the code as
$("#R16463536713741058563").accordion('activate',1);
as the active is 1 so the accordian in position 1 is opening but i want the accordian in 0 position to be opened as i selected the tab under that accordion only.