Home » Developer & Programmer » Forms » How toexecute data by passing new values to a query??
icon14.gif  How toexecute data by passing new values to a query?? [message #142783] Mon, 17 October 2005 12:55 Go to next message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
Hello Gentlemen,

I am facing a problem regarding forms and a block based on SQL query..
please help me out..

i have a query whose short form is this (to make my question simple)
select t.*, 
           case when col2 in (1, 17) 
          then rank() over (order by case when col2 in (1, 17) then 0 else null end, id)
       else 0
           end rank 
      from t 
     order by id
/
 
        ID       COL2       RANK
         1          1           1
         2         17          2
         3          1           3
         4          1           4
         5         11          0
         6          1           5



i have made a VIEW of this query and then made a form based on the block of this VIEW. The VIEW has columns

SQL> desc my_view
 Name                               Null?    Type
 ID                                                 NUMBER
 COL2                                            NUMBER
 RANK                                            NUMBER


now the problem is that on my form which is a 12 record block, i want to create two numeric non-db text items and one button..

here i want to make
non-db item_1 = the '1' part of the "when col2 IN (1, 17)" in the above query
i.e. when col2 IN (non-db item_1, 17)

and

non-db item_2 = the '17' part of the "when col2 IN (1, 17)" in the above query
i.e. when col2 IN (1, non-db item_17)


in these two text items i want when the user to enter the two numbers of his choice and then press the button the block should execute query with the new values which the CASE statement inside the VIEW will look for and the respective data is displayed in the form

for example, if the user types 11 in item_1 and 1 in item_2 then the query of the VIEW should be changed like this before executing..

select t.*, 
           case when col2 in (11, 1) 
          then rank() over (order by case when col2 in (11, 1) then 0 else null end, id)
       else 0
           end rank 
      from t 
     order by id
/


I hope this will clear my problem
i am unable to figure out how can i achieve this in my form..
would be grateful is some one sorts this out for me..

regards
ramis.


Re: How toexecute data by passing new values to a query?? [message #142830 is a reply to message #142783] Mon, 17 October 2005 23:23 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Interesting ...
CREATE TABLE TEMP2
(
  ID    NUMBER                                  NOT NULL,
  COL2  NUMBER
)
LOGGING 
NOCACHE
NOPARALLEL;

CREATE UNIQUE INDEX PK_TEMP2 ON TEMP2
(ID)
LOGGING
NOPARALLEL;

ALTER TABLE TEMP2 ADD (
  CONSTRAINT PK_TEMP2 PRIMARY KEY (ID));

INSERT INTO TEMP2 ( ID, COL2 ) VALUES (1, 1); 
INSERT INTO TEMP2 ( ID, COL2 ) VALUES (2, 17); 
INSERT INTO TEMP2 ( ID, COL2 ) VALUES (3, 1); 
INSERT INTO TEMP2 ( ID, COL2 ) VALUES (4, 1); 
INSERT INTO TEMP2 ( ID, COL2 ) VALUES (5, 11); 
INSERT INTO TEMP2 ( ID, COL2 ) VALUES (6, 1); 
COMMIT;

I ran
select   temp2.*,
         case
            when col2 in (1, 11) then rank () over (order by case
                                           when col2 in (1, 11) then 0
                                           else null
                                        end,
                                        id)
            else 0
         end rank
    from temp2 
order by 1;
and got the following output
        ID       COL2       RANK
---------- ---------- ----------
         1          1          1
         3          1          2
         4          1          3
         5         11          4
         6          1          5
         2         17          0


I had not expected the '2' line to come out last.

David
Re: How toexecute data by passing new values to a query?? [message #142845 is a reply to message #142830] Tue, 18 October 2005 00:17 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have a look at this form. It uses set_block_property QUERY_DATA_SOURCE_NAME to populate a query using the control field 'col'.

The main block has a pre-query as follows
declare
  the_string varchar2(2000);
begin
  message('pre-query start');pause;
  the_string := '(select temp2.*, case when col2 in (1,'
                || :ctrl.col
                || ') then rank () over (order by case when col2 in (1,'
                || :ctrl.col
                || ') then 0 else null end, id) else 0 end rank from temp2)';
  set_block_property('block2',QUERY_DATA_SOURCE_NAME,the_string);
  message('pre-query end');pause;
end;


I have attached a sample form - check the various block attributes. The default QUERY_DATA_SOURCE_NAME is irrelevant.

You have to manually go to the main block to execute the query but you can fix that when you add your own 'query' button. You will also have to add the second control field (in which you currently have the value '1'). Defining the fields as type 'number' will save you having to have numerous 'quotes' in your 'select' statement.

David
  • Attachment: djm_t4.fmb
    (Size: 44.00KB, Downloaded 1084 times)

[Updated on: Tue, 18 October 2005 00:19]

Report message to a moderator

Re: How toexecute data by passing new values to a query?? [message #142972 is a reply to message #142783] Tue, 18 October 2005 09:19 Go to previous messageGo to next message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
djmartin,
thankyou so much.
it was an excellet example and worked perfectly well,
thanks for ur time and help
regards,
ramis.
Re: How toexecute data by passing new values to a query?? [message #259460 is a reply to message #142830] Wed, 15 August 2007 12:03 Go to previous messageGo to next message
paragdhonde
Messages: 1
Registered: August 2007
Location: INDIA
Junior Member
Hi Martin

I have seen ur solution for this.
But for my query its not working. Please suggest.

select repno,
dvi_utl.get_ctec('RPTR', repno)
count(distinct effortno)
from effort
where exists (
select 1
from event
where eventdate >= '01-APR-07'
and eventtype = 3 /* Call */
and event.effortno = effort.effortno
)
and callsdone > 0 /* Removed ENDED IS NULL due to AM telling me! */
and not exists (
select 1
from event
where event.effortno = effort.effortno
and trunc(eventdate) < '01-APR-07'
and eventtype+0 = 3
)
and repno in (
select rptr
from staff
where (scsgroup = 3 or rptr = 67)
)
group by repno, dvi_utl.get_ctec('RPTR', repno);

Thanks & Regards

Parag
Re: How toexecute data by passing new values to a query?? [message #260752 is a reply to message #259460] Mon, 20 August 2007 19:22 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
My public name is 'David'. It is at the bottom of each of my entries.

Please format your code and post it within 'code' tags.

I suggest that you run your query in PL/SQL (or TOAD SQL) until you get the results that you expect and then put the code into your form. Be careful when using something like "eventdate >= '01-APR-07'". I suggest using "eventdate >= to_date('01-APR-07','DD-MON-YY')" as there may be different date formats between your foreground environment and the database environment.

David
Previous Topic: get value
Next Topic: Can I close the IE window,when I exit my 9i application
Goto Forum:
  


Current Time: Wed Dec 07 04:29:33 CST 2016

Total time taken to generate the page: 0.17655 seconds