Home » Developer & Programmer » Forms » forms-mask based on stored procedure ?
| forms-mask based on stored procedure ? [message #85212] |
Wed, 09 June 2004 12:07  |
hudo
Messages: 165
Registered: May 2004
|
Senior Member |
|
|
Hello,
can I base a forms-mask directly on a SELECT-Statement respectively on a stored procedure ?
The following scenario:
CREATE TABLE COL_TABLE
(COL1 VARCHAR2(10),
COL2 VARCHAR2(10),
COL3 VARCHAR2(10),
COL4 DATE,
COL5 NUMBER(4));
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA1','1',TO_DATE('01.02.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA1','1',TO_DATE('01.02.2003','dd.mm.yyyy'),60);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA1','1',TO_DATE('01.05.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA1','2',TO_DATE('01.05.2003','dd.mm.yyyy'),50);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA1','2',TO_DATE('01.05.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA2','1',TO_DATE('01.02.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA2','1',TO_DATE('01.05.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA2','3',TO_DATE('01.02.2003','dd.mm.yyyy'),40);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA3','2',TO_DATE('01.07.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA3','2',TO_DATE('01.02.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('AAA','STA3','2',TO_DATE('01.05.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('BBB','STA1','1',TO_DATE('01.07.2003','dd.mm.yyyy'),11);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('BBB','STA1','2',TO_DATE('01.07.2003','dd.mm.yyyy'),5);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('BBB','STA1','2',TO_DATE('01.07.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('BBB','STA1','2',TO_DATE('01.07.2003','dd.mm.yyyy'),12);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('BBB','STA3','2',TO_DATE('01.02.2003','dd.mm.yyyy'),1);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('BBB','STA3','2',TO_DATE('01.02.2003','dd.mm.yyyy'),5);
INSERT INTO COL_TABLE(COL1,COL2,COL3,COL4,COL5) VALUES('BBB','STA3','2',TO_DATE('01.02.2003','dd.mm.yyyy'),1);
I'd like to perform a mask displaying data based on the following SELECT-statement:
SELECT col1, col2, ok, bad,
ok/total*100 ok_percent, bad/total*100 bad_percent
FROM
(SELECT col1, col2,
SUM(DECODE(col5, 1, 1, 0)) ok,
SUM(DECODE(col5, 1, 0, 1)) bad,
count(*) total
FROM col_table
WHERE col4 BETWEEN TO_DATE('01.02.2003','dd.mm.yyyy') AND TO_DATE('01.07.2003','dd.mm.yyyy')
GROUP BY col1, col2);
The WHERE-clause should be implemented as two forms-text-items for example start_date and end_date.
My first thought was, to create a view based on the SELECT-statement (without the WHERE-clause), because I do not know how to build the view "dynamically" with the date-ranges.
So I think building the mask based directly on the SELECT-statement would be fine.
Maybe there are other possibilities two solve this problem ??
Please help
|
|
|
|
| Re: forms-mask based on stored procedure ? [message #85217 is a reply to message #85212] |
Wed, 09 June 2004 22:38  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Make two Control feilds Start_Date & End_Date on your Form.
Now create a Package as follows:
CREATE OR REPLACE PACKAGE COL_DETAILS IS
/***
** This stored procedure returns a ref cursor as a block datasource.
***/
TYPE COLREC is RECORD(COL1 COL_TABLE.COL1%TYPE,
COL2 COL_TABLE.COL2%TYPE,
OK COL_TABLE.COL5%TYPE,
BAD COL_TABLE.COL5%TYPE
);
TYPE COLCUR is REF CURSOR RETURN COLREC;
/***
** Define all IN/OUT parameters
***/
PROCEDURE COLquery(Resultset IN OUT COLCUR,
Start_Date DATE,
Start_Date DATE);
End;
CREATE OR REPLACE PACKAGE BODY COL_DETAILS
(Resultset IN OUT COLCUR,
Start_Date DATE,
Start_Date DATE) IS
Begin
OPEN Resultset
FOR SELECT col1, col2, ok, bad
FROM
(SELECT col1, col2,
SUM(DECODE(col5, 1, 1, 0)) ok,
SUM(DECODE(col5, 1, 0, 1)) bad,
FROM col_table
WHERE col4 BETWEEN
NVL(START_DATE,COL4)
AND NVL(END_DATE,COL4)
GROUP BY col1, col2);
EXCEPTION
WHEN VALUE_ERROR THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
END;
/
As far As Bad% & OK% columns are concerened you can caluclate simly in Post-query of your form.
But if you still want them to be in Package then modify the COLREC accordingly.
HTH
Regards
Himanshu
|
|
|
|
| Re: forms-mask based on stored procedure ? [message #85260 is a reply to message #85217] |
Fri, 11 June 2004 07:09 |
hudo
Messages: 165
Registered: May 2004
|
Senior Member |
|
|
Hello,
what do you mean by control fields ?? I created two text items START_DATE and END_DATE. But I wonder how the connection between the text-item values and the values (Start_date and End_date) in the procedure works.
If you refer to a form-element it has a colon as prefix
e.g. :MY_SEARCH_BLOCK.START_DATE
Do I have to set some "special" properties in the Property Palette of START_DATE and END_DATE ?
Do I need the help of some triggers ??
As far As Bad% & OK% are concerned, there's no problem:
-----
TYPE COLREC is RECORD(COL1 COL_TABLE.COL1%TYPE,
COL2 COL_TABLE.COL2%TYPE,
OK COL_TABLE.COL5%TYPE,
BAD COL_TABLE.COL5%TYPE,
PROZ_OK COL_TABLE.COL5%TYPE,
PROZ_BAD COL_TABLE.COL5%TYPE
);
-----
and excerpt of PROCEDURE COLquery (Resultset IN OUT COLCUR, Start_Date DATE, End_Date DATE)
-----
OPEN Resultset
FOR SELECT col1, col2, ok, bad, ok/TOTAL*100 , bad/TOTAL*100
FROM (SELECT col1, col2,
SUM(DECODE(col5, 1, 1, 0)) ok,
SUM(DECODE(col5, 1, 0, 1)) bad,
count(*) TOTAL
FROM col_table
WHERE col4 BETWEEN
NVL(START_DATE,COL4)
AND NVL(END_DATE,COL4)
GROUP BY col1, col2);
------
Thanks for your help
|
|
|
|
| Re: forms-mask based on stored procedure ? [message #85261 is a reply to message #85217] |
Fri, 11 June 2004 07:39 |
hudo
Messages: 165
Registered: May 2004
|
Senior Member |
|
|
Hello,
I think I got it now, after running the DataBlockWizard and setting (at the Query-Procedure)
the Value :MY_SEARCH_BLOCK.START_DATE for the ArgumentName START_DATE adequate :MY_SEARCH_BLOCK.END_DATE for the ArgumentName END_DATE
it works as it should.
Sorry for the heady answer.
This query is now based on a stored procedure. Is it possible to base the query directly (just) on the SELECT-statement ??
This was just a simple example. If the table COL_TABLE would not be a origin table but a view from an origin table created with the mentioned SELECT-statement, I could get the "corresponding forms-result" as discussed in this thread.
But if I can base the query directly (just) on the SELECT-statement , there is no need to create/store the view on the database-server.
Something similar is mentioned in the forms 6i online help "USing the FROM clause as a block datasource".
I experimented with the FROM-clause query but did not succeed.
Please help
|
|
|
|
Goto Forum:
Current Time: Tue May 07 14:31:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
