 How toexecute data by passing new values to a query?? [message #142783] |
Mon, 17 October 2005 12:55  |
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  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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 ranselect 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; 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 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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 followsdeclare
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 2090 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 #259460 is a reply to message #142830] |
Wed, 15 August 2007 12:03 |
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 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
