| remove duplicate value [message #555763] |
Mon, 28 May 2012 06:53  |
 |
kriti.akanksha
Messages: 28
Registered: March 2012
|
Junior Member |
|
|
how can i get distinct records through this coding, when i add
select distinct col1, col2, col3 from tablename where RECD_ON between :control.REC_FROM and :control.REC_TO;
in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.
declare
qry varchar2(5000);
n number;
alert number;
Begin
if :control.REC_FROM is not null and :control.REC_TO is not null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO ';
IF :control.v_form_status is not null then
qry :=qry|| ' and form_status=:control.v_form_status ';
END IF;
IF :control.HOUSE_TYPE is not null then
qry :=qry|| ' and qtr_type=:control.house_type ';
END IF;
IF :control.HOUSE_TYPE is not null and :control.v_form_status is not null then
qry :=qry|| ' and qtr_type=:control.house_type and form_status=:control.v_form_status ';
END IF;
IF :control.status is not null then
IF :control.status='edt' then
qry :=qry|| ' order by EDT ';
ELSIF :control.status='form_no' then
qry :=qry|| ' order by FORM_NO ';
ELSIF :control.status='name' then
qry :=qry|| ' order by NAME ';
ELSIF :control.status='recd_on' then
qry :=qry|| ' order by RECD_ON ';
END IF;
else
qry:=qry||' ORDER BY EDT,NAME,FORM_NO,RECD_ON ';
END IF;
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
execute_query;
else
message('Please fill Received from and Received to date(s).'); message('.');
raise form_trigger_failure;
end if;
END;
|
|
|
|
| Re: remove duplicate value [message #555772 is a reply to message #555763] |
Mon, 28 May 2012 12:36  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Such an unformatted code is difficult to read. Please, have a look here - won't take more than 10 seconds of your time, but will teach you how to properly format code and keep that formatting in your future messages.
As of your question: DISTINCT - as far as I know - can not cause what you claim it does (i.e. make the SELECT statement return no records). WHERE clause, on the other hand, can.
What are REC_FROM and REC_TO? What is their datatype? Be careful when dealing with dates - you need to provide a correct format mask. Do not rely on implicit datatype conversion.
If these are characters, be careful again. Character sorting is different than number sorting (so '2' comes after '15'), which means that if, for example REC_FROM = '2' and REC_TO = '15', WHERE clause evaluates to where RECD_ON between '2' and '15'
If none of above rings a bell, provide as many useful details as possible (such as table(s) description, form items' datatypes, their values when you run a form, and so forth).
|
|
|
|
| Re: remove duplicate value [message #555778 is a reply to message #555772] |
Mon, 28 May 2012 14:17 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Littlefoot wrote on Mon, 28 May 2012 18:36
As of your question: DISTINCT - as far as I know - can not cause what you claim it does (i.e. make the SELECT statement return no records).
It can if you try to put the distinct in the default_where property.
@kriti.akanksha - create a view that returns the data that you want then base the block on that.
You can't put distinct in the default_where because that property is just for the where clause and distinct is part of the select clause.
|
|
|
|
|
|
| Re: remove duplicate value [message #555783 is a reply to message #555780] |
Mon, 28 May 2012 17:38 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
He says he added it to the first query after the begin - I assume that means the line after the first if.
And the error could have been masked by an on-error trigger.
|
|
|
|
| Re: remove duplicate value [message #555786 is a reply to message #555783] |
Tue, 29 May 2012 00:08 |
|
kriti.akanksha
Messages: 28
Registered: March 2012
|
Junior Member |
|
|
i am trying to say that as i make my qry :=' select distinct......'as i wrote here, then it is not working.it is working properly when i wrote qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO ';
but through this code i am unable to fetch distinct rows what i really want.
i want to know how to possible to make default_where with distinct.
declare
qry varchar2(5000);
n number;
alert number;
Begin
if :control.REC_FROM is not null and :control.REC_TO is not null then
qry := 'select distinct edt, regno, name, desgn, form_no, form_status, recd_on from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO ';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
execute_query;
here control= data block name
ol_registration_vu:= view name
ol_registration= data block name
[Updated on: Tue, 29 May 2012 00:16] Report message to a moderator |
|
|
|
|
|
| Re: remove duplicate value [message #555802 is a reply to message #555790] |
Tue, 29 May 2012 01:38 |
|
kriti.akanksha
Messages: 28
Registered: March 2012
|
Junior Member |
|
|
i am already taking data from a view, which is already distinct by different columns combination. in my view data is distinct as:
entry_date form no applicant qtr_type
21-05-09 1156 abc 2
21-05-09 1156 abc 3
21-05-09 1156 abc 1
22-05-09 1237 xyz 5
21-05-09 1785 pqr 5
i am taking data in my form as
qry := ' RECD_ON between :control.REC_FROM and :control.REC_TO ';
IF :control.HOUSE_TYPE is not null then
qry :=qry|| ' and qtr_type=:control.house_type ';
End if;
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
execute_query;
i want when i don't select any qtr_type(it is optional) through listitem( as one person can apply maximum for 3 qtr_type, but cann't apply two times for the same qtr_type), it will show records distinct by form_no.
i am adding my form in attachment
[EDITED by LF: applied [pre] tags]
[Updated on: Tue, 29 May 2012 01:40] by Moderator Report message to a moderator |
|
|
|
| Re: remove duplicate value [message #555806 is a reply to message #555802] |
Tue, 29 May 2012 01:51 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
|
Maybe you should think of changing data block source type to a FROM clause or a stored procedure. Have a look at this example which will show you how to dynamically create a FROM clause as query data source.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
