Dynamic Record Group Not Working When Apostrophes Used?
Aug 14, 2012
I have developed a Form for the most part everything is working as expected. However, there is a search functionality that is giving me problems. The issue is that when the user enters search criteria for last_name that has an apostrophe (O'brian) the search lov doesn't get populated because the dynamic record group is not getting created when the string has an apostrophe (ie O'brian). I have a dynamic record group that takes the user's search criteria and populates an LOV on the screen with the records that matched their criteria.
Here is the code that is behind my search button where the dynamic RG gets created. It works fine for all searches that don't contain an apostrophe.
DECLARE
p_where_debtor varchar2(2000);
p_where_liab varchar2(2000);
rg_id RecordGroup;
[Code]....
errcode := Populate_Group(rg_id);
View 5 Replies
ADVERTISEMENT
Feb 22, 2011
I want to create a Dynamic record group and associate it to a LOV.for example: I want that for a set of people A, i want to fire the query in lov:
select * from emp where (condition 1)
and for another set of people B, I want to fire
select * from emp where (condition 2)...
How could I achieve it?
View 4 Replies
View Related
Aug 25, 2012
i have a question regarding dynamic record group creation.I have a text item(department) attached with LOV ,and a list item is also there.i want whatever the department name i will select from ma LOV according to that corresponding manager name should be display in the list item.
my table structure is :-
table- QRY
column- DEPT_ID,DEPT_NAME,MANAGER_NAME
View 2 Replies
View Related
Jul 9, 2013
I am working on Oracle forms 10g (Windows 7 OS).I have got one requirement to show one LOV for COLOR table. Say i have three colors BLACK, RED, BLUE in color table. Suppose in a multi record block if a user selects BLACK from a lov for one record then for the next record lov it should show only RED and BLUE. How to filter the COLOR from a LOV record group query which are already selected?
View 1 Replies
View Related
Jul 26, 2012
My procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
View 3 Replies
View Related
Dec 1, 2007
I am using oracle, and have the following relations.
Patient(Patno, name,docno*)
Doctor(Docno, name, specialization)
Treatment(treatno, startdate, reason, docno*, patno*)
I want to list every patients number and name, and the number of treatments given by a certain doctor (e.g doctor name = 'smith'). the problem i am having is that if i specify the doctor is WHERE clause it only gives me the doctors who are under 'smith' but i want all even if 'smith' has given them 0 treatments. below is code that i have so far to view all patients with corresponding data.
select pa.patno, pa.lname, tr.treatno
from patient pa full join treatment tr on tr.patno=pa.patno;
View 1 Replies
View Related
Dec 12, 2012
I have this very simple SQL statement:
SELECT e_id AS id,
'javascript:$(''P4_ID'','|| e_id ||'); openModal(showEvent);' AS url
FROM event_info evtwhich returns
86 javascript:$('P4_ID',86); openModal(showEvent);
87 javascript:$('P4_ID',87); openModal(showEvent);
88 javascript:$('P4_ID',88); openModal(showEvent);
89 javascript:$('P4_ID',89); openModal(showEvent);
But I'd like it to return the following (apostrophes surrounding the e_id)
86 javascript:$('P4_ID','86'); openModal(showEvent);
87 javascript:$('P4_ID','87'); openModal(showEvent);
88 javascript:$('P4_ID','88'); openModal(showEvent);
89 javascript:$('P4_ID','89'); openModal(showEvent);
And I am struggling with figuring out how to get this working.
View 2 Replies
View Related
Oct 31, 2012
I have two columns Department and EmpName:
Department EmpName
____________________________________
Sales empname1
Sales empname2
Marketing empname3
Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total
______________________
Sales 2
Marketing 1
Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM
( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee
FROM Employer A, EmployeeInfo B
WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2')
GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
View 2 Replies
View Related
Mar 6, 2011
I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.
p.s: That record group is built in run-time.
View 1 Replies
View Related
Apr 3, 2010
How can I get record group query in code and chang it ?( for example change its where clus). I need to get its Query.
the record group exists and I must get its query not make it.
and I know about Populate_Group_With_Query for changing but cant I have the query to change?
FOR EXAMPLE :
my record group query is :
select a ,b from A where A.c=1;
and I use it in an LOv.
Now i want get this record group in code and change its WHERE cluse after pressing A BUTTON How can I do it?
View 2 Replies
View Related
Dec 2, 2008
I have 2 record groups names RG1,RG2 , I attached RG1 record group to LOV1 at design time,I want to change the record group RG1 to RG2 for LOV1 at run time.
View 11 Replies
View Related
Nov 17, 2011
I am working on forms 6i, ebs 11i and data base 9i. I have below requirement to achieve.
I want to create a lov for a column called 'FORM'.
I have lookup for this as follows
lookup code meaning description
FORM FORM XXSMCINV_FORM
in the description, it's value set name.
I am manually checking the value set name and writing static record group.
Right now, i have static record group for this like below.
select ffvv.flex_value "FORM"
from fnd_flex_value_sets ffvs,fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id=ffvv.flex_value_set_id
and ffvs.flex_value_set_name='XXSMCINV_FORM' order by flex_value;
It's working fine.
But in the lookup, they can add one more FORM like below.
LOOKUP_CODE MEANING DESCRIPTION
FORM FORM XXSMCINV_FORM
FORM1 FORM1 XXSMCINV_FORM1
Now i have to change the record group query manullay like below.
select ffvv.flex_value "FORM"
from fnd_flex_value_sets ffvs,fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id=ffvv.flex_value_set_id
and ffvs.flex_value_set_name='XXSMCINV_FORM' order by flex_value;
UNION
select ffvv.flex_value "FORM"
from fnd_flex_value_sets ffvs,fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id=ffvv.flex_value_set_id
and ffvs.flex_value_set_name='XXSMCINV_FORM1' order by flex_value;
Is there anyway i can make this automatic whenever they add new FORM lookup.
Is this possible to achieve using dynamic record group.
View 1 Replies
View Related
Apr 10, 2013
i want to create a lov and record group dynamically at run time. i have the code in when-form-instance..
Declare
v_lov lov;
rg_id recordgroup;
gc_id groupcolumn;
[Code]....
whether i create first a lov and record group at design time or not? if i create a lov and have the recordgroup column then what is the benefit of dynamically lov.?
View 5 Replies
View Related
Feb 2, 2010
earlier the query was running on same record group but i hav to delete some code so i did but after made such changes itreturns this error.but still query is correct i complied on toad.
View 10 Replies
View Related
Jun 22, 2012
I have a problem with passing procedure name dynamically with record type i/p parameter..I'm not attaching any insert/create table statements, as I'm unsure of forming the sql statement dynamically..
CREATE OR REPLACE PACKAGE med_order_pkg AS
TYPE deid_med_order_typ IS RECORD(....)
L_deid_med_order_typ deid_med_order_typ;
PROCEDURE RULE_MASTER_PRC (P_IN_RULE IN deid_med_order_typ);
END;
[code]....
From the above, I need to execute a procedure with record type as i/p parameter..V_SQL should form the statement & execute another procedure which comes into the variable V_MSG_PROC .I'm having difficuly in forming the statement...(I did it by hard-coding the procedure with parameter in the next line which is commented out & it works...So how can I modify V_SQL in the above statement?
View 3 Replies
View Related
Jul 26, 2012
I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"
CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR,
P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR)
IS
l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ;
V_MSG_SHELL_NAME VARCHAR2(1000);
V_MESG_TEXT_SEGMENT VARCHAR2(1000);
V_TEXT VARCHAR2(1000);
V_MSG_TEXT VARCHAR2(4000);
V_MSG_FINAL_TEXT VARCHAR2(4000);
V_MSG_PROC VARCHAR2(1000);
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
L_STATUS VARCHAR2(100);
L_REASON VARCHAR2(1000);
[code]...
I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...
View 10 Replies
View Related
Apr 23, 2010
i want to populate value into list item from below record group
DECLARE
channel recordgroup;
banks recordgroup;
errcode NUMBER;
errcode1 NUMBER;
m_stmt VARCHAR2 (100);
BEGIN
m_stmt :='SELECT DISTINCT RI_TYPE FROM COMPETETITION_DATA_UPLOAD';
[code]....
but i'm getting following error in forms FRM-41334 Invalid Record Group for list population
View 1 Replies
View Related
Jul 5, 2010
I created 3 radio buttons and one list item. Different options will appear according to the radio button the user has clicked on. i've created 3 recordgroups and 3 LOV. the first two for seven_eleven and mannings work fine, but not for GNC. is it because there is only one option for list item in GNC? Must it be more than one? I've tried to another option as space but it still doesnt work.
DECLARE
it_id1 item;
V_SEVEN_ELEVEN RECORDGROUP:=NULL;
V_MANNINGS RECORDGROUP:=NULL;
V_GNC RECORDGROUP:=NULL;
[code]....
View 13 Replies
View Related
Jul 23, 2010
I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.
My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),
[code].....
View 5 Replies
View Related
Nov 11, 2012
Just explaining what I am trying to achieve:
1) i have a hr.departments table that was loaded in hr schema on 1st oct 2012 with 4 columns(department_id, department_name, manager_id, location_id)
2) now I have a new schema by my name 'rahul' and I have loaded departments table but now an additional column has come into picture,ie created_date, this table got loaded on 1st-Nov-2012
3) Now going forward my columns could be dropped from the departments table (it can be a case), for example might be my departments table in my schema 'rahul' one day could comprise of only 3 columns(department_id,department_name,manager_id)
4) Now in the next step, I have managed to extract common column names(in a single line where columns are delimited using a comma) from both the tables(hr.departments and rahul.departments) which are (department_id, department_name, manager_id, location_id) using all_tab_cols table and I have written a function for it which i will be pasting below.
5) now going forward, using the above column names line with column names delimited using comma, I have used a ref cursor and assigned a query to it using the line of columns that I have extracted from the above point
6) Now I want to create a record variable which refers to my ref cursor, something like we do when we create a record variable by reffering to an explicit cursor defination that we give in the declaration block.
PS:
1) I have been out of touch with plsql for a long time so I have lost a lot of mmeory regarding plsql.
2) basically I need to compare data in hr.departments table with rahul.departments table for only columns that are common to both the tables, rest new or discarded columns information will go in one of the log tables that I have created(this is done already)
Code :
===================================================================================================
create or replace procedure p_compare_data(fp_old_table_name in varchar2, fp_new_table_name in varchar2)
is
[Code].....
View 5 Replies
View Related
Nov 1, 2013
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
View 4 Replies
View Related
Jun 23, 2011
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
View 16 Replies
View Related
Apr 1, 2013
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
View 3 Replies
View Related
Mar 22, 2013
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
View 1 Replies
View Related
May 17, 2011
Refer to the txt file to create table and insert data.
I executed the following query-
SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail
and got the following result-
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1
Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1
View 3 Replies
View Related
Apr 8, 2013
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9
Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....
Is there a solution or bypass?
View 3 Replies
View Related
May 16, 2012
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,
[code]....
A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?
View 11 Replies
View Related
Aug 26, 2013
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
View 4 Replies
View Related
Sep 13, 2011
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
View 4 Replies
View Related
Apr 21, 2010
I have the following case to solve:
Example Table:
Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00
View 8 Replies
View Related
