Home » Developer & Programmer » Forms » Queryable Non-Database Item
icon5.gif  Queryable Non-Database Item [message #205841] Mon, 27 November 2006 21:11 Go to next message
thurgiven
Messages: 3
Registered: October 2006
Junior Member
guys.. i think this seems to be a simple question.. i'm sorry.. please guys help me.. what will be the programatical approach for querying using a non database item???
Re: Queryable Non-Database Item [message #205843 is a reply to message #205841] Mon, 27 November 2006 21:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
G'day. I think we need more detail here please, I know that I certainly do.

Either cut and paste your requirement in this thread or write in your reply what you are being asked to achieve and from where the data is coming.

David
Re: Queryable Non-Database Item [message #205858 is a reply to message #205841] Mon, 27 November 2006 23:16 Go to previous messageGo to next message
thurgiven
Messages: 3
Registered: October 2006
Junior Member
i'm sorry for that little detail.. you see i have the plate_no as the non_database item.. if you queried the whole block.. the value for plate_no is populated by a function that have a long select statement.. my problem is what if the user wants to query the block using the plate_no? the function where the plate_no is populated is like this..
FUNCTION GET_PLATE_NO( ws_line_cd    IN NUMBER,
                       ws_subline_cd IN NUMBER,
                       ws_iss_cd     IN NUMBER,
                       ws_clm_yy     IN NUMBER,
                       ws_clm_seq_no IN NUMBER ) RETURN VARCHAR2 IS
  v_pol_seq_no     MOTCAR.POL_SEQ_NO%type;
  v_plate_no       MOTCAR.PLATE_NO%type;
  v_item_no        MOTCAR.ITEM_NO%type;
BEGIN
  begin
     select distinct pol_seq_no
       into v_pol_seq_no
       from claims a
       where 1=1
         and a.line_cd = ws_line_cd
         and a.subline_cd = ws_subline_cd
         and a.iss_cd = ws_iss_cd
         and a.clm_yy = ws_clm_yy
         and a.clm_seq_no = ws_clm_seq_no;
  exception
      when no_data_found then
          null;
      when too_many_rows then
          v_pol_seq_no := null;
  end;   

  begin
     select distinct item_no
       into v_item_no
       from clm_loss a, clm_hist b
       where a.line_cd = b.line_cd
         and a.subline_Cd = b.subline_cd
         and a.iss_cd = b.iss_cd
         and a.clm_yy = b.clm_yy
         and a.clm_seq_no = b.clm_seq_no
         and a.line_cd = ws_line_cd
         and a.subline_cd = ws_subline_cd
         and a.iss_cd = ws_iss_cd
         and a.clm_yy = ws_clm_yy
         and a.clm_seq_no = ws_clm_seq_no
         and b.delete_tag != 'Y';
  exception
      when no_data_found then
          null;
      when too_many_rows then
          v_item_no := null;
  end;  

  begin
      select distinct plate_no
        into v_plate_no
        from motcar c, polbasic d
        where c.line_cd = d.line_cd
          and c.subline_cd = d.subline_cd
          and c.iss_cd = d.iss_cd
          and c.pol_seq_no = d.pol_seq_no
          and d.eff_dt = (select max(eff_dt)
                            from polbasic a, motcar b
                            where a.line_cd = b.line_cd
                              and a.subline_cd = b.subline_cd
                              and a.iss_cd = b.iss_cd
                              and a.pol_seq_no = b.pol_seq_no
                              and a.endt_iss_cd = b.endt_iss_cd
                              and a.endt_yy = b.endt_yy
                              and a.endt_seq_no = b.endt_seq_no
                              and b.line_cd = c.line_cd
                              and b.subline_cd = c.subline_cd
                              and b.iss_cd = c.iss_cd
                              and b.pol_seq_no = c.pol_seq_no
                              and b.item_no = c.item_no
                              and b.plate_no is not null 
                              and nvl(a.pol_stat,0) != '5')
          and c.line_cd = ws_line_cd
          and c.subline_cd = ws_subline_cd
          and c.iss_cd = ws_iss_cd
          and c.pol_seq_no = v_pol_seq_no
          and c.item_no = v_item_no
          and c.plate_no is not null;
  exception 
       when no_data_found then
           null;
       when too_many_rows then
           v_plate_no := null;     
  end; 
RETURN(v_plate_no);
END;

Upd-Mod: Add code tags.

[Updated on: Tue, 28 November 2006 00:12] by Moderator

Report message to a moderator

Re: Queryable Non-Database Item [message #205873 is a reply to message #205858] Tue, 28 November 2006 00:21 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I suggest either adding a 'search by plate no' button which takes the user to a clone of your current form so that you have the same layout but this time basing your form's driver block on 'motcar' and then making all the other details non-database or reviewing the whole thing and basing the block on either a regular view or a materialised view.

David
Re: Queryable Non-Database Item [message #205875 is a reply to message #205858] Tue, 28 November 2006 00:23 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As an example, this is how I see your problem: There's a table with two columns: 'COL_1' and 'COL_2'. A non-database item is derived from those two columns; let it be a simple addition: non_database_item = col_1 + col_2. Output (on your form) looks like this:
COL_1   COL_2   NON-DATABASE-ITEM
-----   -----   -----------------
1       4       5
2       1       3
0       5       5
2       2       4


What you want is this: press <enter query> button, enter '5' into the non-database-item, press <execute query> and, as a result, you'd like to get pairs (1, 4) and (0, 5) from the table.

Right? If so, I'm not sure how to do that using a table as a block source. Perhaps you could do that if there's a possiblity to create a VIEW instead (which would have col_1, col_2 and calculated non-database-item - if possible), or even a STORED PROCEDURE (which would make that surely possible) which would return a PL/SQL table.
Re: Queryable Non-Database Item [message #206289 is a reply to message #205841] Wed, 29 November 2006 08:16 Go to previous messageGo to next message
satishachar
Messages: 17
Registered: July 2006
Location: Mumbai
Junior Member

use post-query
Re: Queryable Non-Database Item [message #206314 is a reply to message #206289] Wed, 29 November 2006 10:01 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you, please, explain use of the POST-QUERY trigger on this problem?
Previous Topic: How to Display Meu "ยต" Sign on Oracle Form as Label
Next Topic: how to get one side oval shaped push button in forms 10g
Goto Forum:
  


Current Time: Thu Dec 08 16:46:03 CST 2016

Total time taken to generate the page: 0.08821 seconds