forms-mask based on stored procedure/select-statement 1

[hudo]

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. If start_date and end_date are NULL,
there should be no date-range restriction (no WHERE-clause)
for the SELECT-statement.

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

 

[sem]

In Forms you may specify QUERY_DATASOURCE_NAME dynamically. You may also change DEFAULT_WHERE block property. And at last, you may create a procedure returning REF_CURSOR. Your case is quite simple, thus I'd recommend you the first choice.

Regards, Dima

http://seminihin.narod.ru

 

[HimanB]

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

 

[sem]

Himanshu,
can you explain the need in stored procedure? The same may be done in ordinary base-table block by specifying parametrized DEFAULT_WHERE. And why do you catch (and not process!) VALUE_ERROR?

Regards, Dima

http://seminihin.narod.ru

 

[HimanB]

Sem,
Hudo has speicifically asked here how to base the Form on Stored Procedure for which I gave him the code accordingly.
Also this is a sample code which I wrote, it is upto him whether to Process any error raised or not.

If you need any other clarification then please let me know.

Regards
Himanshu

 

[sem]

I've just wondered why you catched VALUE_ERROR and not others. Did you expect it to occur? I can't see any reason to distinguish it.
I "carped" at your answer because IMO when somebody asks about "how to solve my problem using this specific technique" besides answering the question (or even instead of!) it is worth to be mentioned that this technique is inappropriate for this specific case and another one should be used.
I also don't like your solution because it may produce wrong execution plans (due to nvl), I had a lot of headache with similar code: openning different cursors depending on parameters provided seems to be far more efficient.

Regards, Dima

http://seminihin.narod.ru

 

[hudo]

Hello,

from sem:

"can you explain the need in stored procedure?"

"it is worth to be mentioned that this technique is inappropriate for this specific case and another one should be used."

Think of the case that some base-tables exist already and it is not allowed for the programmer/developer to create other tables/views which he could build upon the mentioned query and then base the form on these newly created tables/views.

Which other technique would be appropriate for this case ?

I read in the Forms 6i online help, that a query may also be based directly on a select-statement "using the FROM-clause query as datasource" for a data block.
I experimented a while but did not succeed. Maybe someone can help me, it would be fine if the data example in this thread would be used for demonstration.

 

[sem]

Hudo,
create block manually. Set block properties:

QUERY DATA SOURCE TYPE: FROM Clause Query
INSERT_ALLOWED: FALSE
UPDATE_ALLOWED: FALSE
DELETE_ALLOWED: FALSE


Add base table items that you need to display/process.
Create 2 non-base table items to get date range :dt_from and :dt_to of DATE data type. In PRE-QUERY trigger:

if :dt_from is not null then
	if :dt_to is null then
		set_block_property('block5', QUERY_DATA_SOURCE_NAME,
		'(SELECT col1, col2, SUM(DECODE(col5, 1, 1, 0)) ok, SUM(DECODE(col5, 1, 0, 1)) bad, SUM(DECODE(col5, 1, 1, 0))/count(*)*100 ok_percent, SUM(DECODE(col5, 1, 0, 1))/count(*)*100 bad_percent FROM col_table WHERE col4 >= to_date('''||:dt_from||''') GROUP BY COL1, COL2)');
	else
		set_block_property('block5', QUERY_DATA_SOURCE_NAME,
		'(SELECT col1, col2, SUM(DECODE(col5, 1, 1, 0)) ok, SUM(DECODE(col5, 1, 0, 1)) bad, SUM(DECODE(col5, 1, 1, 0))/count(*)*100 ok_percent, SUM(DECODE(col5, 1, 0, 1))/count(*)*100 bad_percent FROM col_table WHERE col4 between to_date('''||:dt_from||''') and to_date('''||:dt_to||''') GROUP BY COL1, COL2)');
	end if;	
else 
	if :dt_to is not null then
		set_block_property('block5', QUERY_DATA_SOURCE_NAME,
		'(SELECT col1, col2, SUM(DECODE(col5, 1, 1, 0)) ok, SUM(DECODE(col5, 1, 0, 1)) bad, SUM(DECODE(col5, 1, 1, 0))/count(*)*100 ok_percent, SUM(DECODE(col5, 1, 0, 1))/count(*)*100 bad_percent FROM col_table WHERE col4 <= to_date('''||:dt_to||''') GROUP BY COL1, COL2)');
	else
		set_block_property('block5', QUERY_DATA_SOURCE_NAME,
		'(SELECT col1, col2, SUM(DECODE(col5, 1, 1, 0)) ok, SUM(DECODE(col5, 1, 0, 1)) bad, SUM(DECODE(col5, 1, 1, 0))/count(*)*100 ok_percent, SUM(DECODE(col5, 1, 0, 1))/count(*)*100 bad_percent FROM col_table GROUP BY COL1, COL2)');
	end if;
end if;

Regards, Dima

http://seminihin.narod.ru