list item runtime [message #690148] |
Fri, 01 November 2024 12:14 |
shahzad-ul-hasan
Messages: 639 Registered: August 2002
|
Senior Member |
|
|
Dear i have a table
create table fees1 (
chl number(7),ffdat date,
sno number(5),ftut number(6),ftot number(6));
insert into fees1
values
(123,'01-OCT-2024',1,1300,1300);
insert into fees1
values
(123,'11-OCT-2024',2,1500,1500);
insert into fees1
values
(124,'03-OCT-2024',1,1500,1500);
insert into fees1
values
(124,'09-OCT-2024',2,1500,1500);
insert into fees1
values
(223,'02-OCT-2024',1,1500,1500);
insert into fees1
values
(224,'04-OCT-2024',1,1700,1700);
COMMIT;
i want to display record in tabular form. i want to display at runtime the fdate,ftut,ftot in runtime list item (tlist/poplist) with distinct chl_no using record group.
|
|
|
|
Re: list item runtime [message #690150 is a reply to message #690149] |
Tue, 05 November 2024 08:40 |
shahzad-ul-hasan
Messages: 639 Registered: August 2002
|
Senior Member |
|
|
dear
Begin
If Populate_Group_With_Query('R_G','Select fdate,ftut,ftot from fees1
Where chl=:chl_no
Order by fdate') Then
Clear_List('Std');
Populate_List('fees1.fdate','R_G');
Populate_List('fees1.ftut','R_G');
Populate_List('fees1.ftot','R_G');
End if;
Exception
When Others Then
Message(Sqlerrm);
End;}
my fields datatypes are fdate date, ftut number,ftot number.but cannot populate in required fields. please advised.
|
|
|
Re: list item runtime [message #690151 is a reply to message #690150] |
Wed, 06 November 2024 04:09 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The populate_list builtin requires that the record group passed to it has a 2 column structure - 1st column is the value shown on screen, 2nd column is the value stored.
Your record group has 3 columns.
I'm not sure what you're trying to do here, but using lists the way you're trying to simply won't work.
|
|
|
Re: list item runtime [message #690175 is a reply to message #690151] |
Wed, 13 November 2024 03:41 |
shahzad-ul-hasan
Messages: 639 Registered: August 2002
|
Senior Member |
|
|
field: ftut Number(10);
DECLARE
group_id RecordGroup;
group_name varchar2(10) :='abc';
status NUMBER;
BEGIN
group_id := find_group(group_name);
if not id_null(group_id) then
delete_group(group_id);
end if;
group_id := Create_Group_From_Query(group_name,'select ftut,to_number(ftut) from fees1 where chl=:chl_no');
/* Select statement must have two column*/
status := Populate_Group(group_id);
Populate_List('DUMMY.ft',group_id);
END;
FRM-41335: Populate_List: invalid column type for column 1
kindly check it gave me error.
|
|
|
Re: list item runtime [message #690176 is a reply to message #690175] |
Wed, 13 November 2024 03:59 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Poplist labels need to be varchar, ftut is a number.
So you need to to_char ftut for the first column.
You also need to stop trying to to_number ftut as to_number on a number makes no sense.
|
|
|
Re: list item runtime [message #690177 is a reply to message #690176] |
Wed, 13 November 2024 09:06 |
shahzad-ul-hasan
Messages: 639 Registered: August 2002
|
Senior Member |
|
|
DECLARE
rg_name VARCHAR2(20) := 'LEASE_RG';
rg_id RECORDGROUP;
err_code NUMBER;
con_num VARCHAR2(40) ;
list_id ITEM := FIND_ITEM('dummy.ft');
qry VARCHAR2(1000);
a varchar2(40);
BEGIN
con_num := :fees1.chl;
select distinct substr(to_char(ffdat,'MON'),1,1) into a from fees1
where chl=:chl;
qry :='SELECT ftut,to_char('||a||'ftut) from fees1 where chl=:chl';
rg_id := FIND_GROUP(rg_name);
IF ID_NULL(rg_id) THEN
rg_id := CREATE_GROUP_FROM_QUERY(rg_name,qry);
DELETE_GROUP_ROW(rg_id, ALL_ROWS);
err_code := POPULATE_GROUP(rg_name);
CLEAR_LIST(list_id);
POPULATE_LIST(list_id,rg_id);
ELSE
DELETE_GROUP(rg_id);
END IF;
END;
frm: 41072 Error popluating group.
|
|
|
Re: list item runtime [message #690178 is a reply to message #690177] |
Wed, 13 November 2024 11:07 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I don't understand what you're trying to do here.
this:
qry :='SELECT ftut,to_char('||a||'ftut) from fees1 where chl=:chl';
will, with your sample data from the first post, result in the following query being used to populate the group:
SELECT ftut,to_char(oftut) from fees1 where chl=:chl
your table doesn't contain a column called oftut, or, for example, jftut (when the month is jan, june or july).
Also this
select distinct substr(to_char(ffdat,'MON'),1,1) into a from fees1
where chl=:chl;
is going to throw an "ORA-01422: exact fetch returns more than requested number of rows" error as soon as fees1 contains data for months starting with more than 1 letter for a given chl value.
You need to explain what you're trying to do.
|
|
|
|
Re: list item runtime [message #690180 is a reply to message #690179] |
Thu, 14 November 2024 04:23 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Do you want the display value or the stored value to start with a letter?
If it's the display value then you should be modifying the first column in the select not the second.
Secondly which month start letter do you want to use? Because as I already pointed out your current approach for getting that letter is likely to break soon.
Is there any reason why you don't use the month from the current fees row?
If that's what you need then the query should probably be:
SELECT substr(to_char(ffdat,'MON'),1,1)||to_char(ftut), ftut from fees1 where chl=:chl
|
|
|
Re: list item runtime [message #690181 is a reply to message #690180] |
Thu, 14 November 2024 05:05 |
shahzad-ul-hasan
Messages: 639 Registered: August 2002
|
Senior Member |
|
|
DECLARE
group_id RecordGroup;
group_name varchar2(10) :='abc';
status NUMBER;
BEGIN
group_id := find_group(group_name);
if not id_null(group_id) then
delete_group(group_id);
end if;
group_id := Create_Group_From_Query(group_name,'
SELECT substr(to_char(ffdat,''MON''),1,1)||to_char(ftut),to_char(ftut) from fees1 where chl=:chl');
/* Select statement must have two column*/
status := Populate_Group(group_id);
Populate_List('fees1.ft',group_id);
END;
solution is fine working. but its applied on all (:chl).not on specific challan which is mention in (where clause). please advised.
|
|
|
|
|
Re: list item runtime [message #690184 is a reply to message #690183] |
Thu, 14 November 2024 07:12 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You can't do that, list items have to have the same set of values for every record in a given datablock. you want them to have different values.
Use an LOV instead.
|
|
|
|
Re: list item runtime [message #690186 is a reply to message #690185] |
Thu, 14 November 2024 07:50 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You've moved the list item generation code to post-query?
That won't work the way you think it does unless all the queried records have the same value of chl.
|
|
|