Oracle Forms 6i ( fill data block from procedure..)

[vpv]

try follow (fill data block structure from procedure):

declare
S VARCHAR2(50);
I NUMBER;
CURSOR DF (dt_s1 DATE,dt_s2 DATE) IS
SELECT ipcrmain, dpcrsdt, dpcrfdt, ipcracc,
b.ccknname, c.cognsname,ccusname
FROM igg.pcr a, igg.ckn b, igg.ogn c,igg.cus d,igg.ncr
WHERE a.ipcrnum = b.icknnum
AND a.ipcrogn = c.iognnum
AND a.ipcrcus = d.icusnum
AND a.dpcrsdt >= dt_s1
AND a.dpcrsdt <= dt_s2
union
SELECT ipcrmain, dpcrsdt, dpcrfdt, ipcracc,
b.ccknname, 'Clients',ccusname
FROM igg.pcr a, igg.ckn b, igg.cus d
WHERE a.ipcrnum = b.icknnum
AND a.ipcrogn is null
AND a.ipcrcus = d.icusnum
AND a.dpcrsdt >= dt_s1
AND a.dpcrsdt <= dt_s2;

MC DF%ROWTYPE;
begin
OPEN DFd1,:d2);
I:=1;
loop
fetch df into :main_t(i).ipcrmain, :main_t(i).ipcrsdt,:main_t(i).ipcrfdt,
:main_t(i).ipcracc,:main_t(i).ccknname,:main_t(i).cognsname,:main_t(i).ccusname;
exit when df%notfound;
i:=i+1;
end loop;
*************************
main_t - data_block.
receive error: bad bind variable ':main_t'
Why?
i want show result of query on the forms....
may be exist another ways?
thanks..

 

[sem]

Is main_t a block name? Even if so, there's no such thing as :main_t(i). To assign value to the field within specific record you should navigate to that record. In your case I suppose you try to fill some non basetable block, so the code may be
...
loop
fetch df into :main_t.ipcrmain, :main_t.ipcrsdt,:main_t.ipcrfdt,
:main_t.ipcracc,:main_t.ccknname,:main_t.cognsname,:main_t.ccusname;
exit when df%notfound;
next_record;
end loop;
...

BTW, you may build your block on that query and manage without coding at all.

Regards, Dima

 

[vpv]

thank, Dima.
How i can built data block without this programming?
I try, but there's nothing get...
I'd like to find out simply way in order to redirect SQL query in form grid...

regards, Pavel

 

[sem]

Set Query Data Source Type block property to [/b]FROM clause query[/b], enter cursor query (without parameter-specific filetr) into Query Data Source Name.

SELECT ipcrmain, dpcrsdt, dpcrfdt, ipcracc,
b.ccknname, c.cognsname,ccusname
FROM igg.pcr a, igg.ckn b, igg.ogn c,igg.cus d,igg.ncr
WHERE a.ipcrnum = b.icknnum
AND a.ipcrogn = c.iognnum
AND a.ipcrcus = d.icusnum
SELECT ipcrmain, dpcrsdt, dpcrfdt, ipcracc,
b.ccknname, c.cognsname,ccusname
FROM igg.pcr a, igg.ckn b, igg.ogn c,igg.cus d,igg.ncr
WHERE a.ipcrnum = b.icknnum
AND a.ipcrogn = c.iognnum
AND a.ipcrcus = d.icusnum
union
SELECT ipcrmain, dpcrsdt, dpcrfdt, ipcracc,
b.ccknname, 'Clients',ccusname
FROM igg.pcr a, igg.ckn b, igg.cus d
WHERE a.ipcrnum = b.icknnum
AND a.ipcrogn is null
AND a.ipcrcus = d.icusnum
union
SELECT ipcrmain, dpcrsdt, dpcrfdt, ipcracc,
b.ccknname, 'Clients',ccusname
FROM igg.pcr a, igg.ckn b, igg.cus d
WHERE a.ipcrnum = b.icknnum
AND a.ipcrogn is null
AND a.ipcrcus = d.icusnum


Set WHERE CLAUSE block property to a.dpcrsdt >= :cnt_block.dt_s1 AND a.dpcrsdt <= :cnt_block.dt_s2

cnt_block is some single-record control block. If both dates are originated from some existing items, you may reference them. Or you may use global variables. It's up to you.

Regards, Dima

 

[vpv]

Super...
Dima great thanks...
You save a long time for me...
by the way, i was born in Ukraine too (Hmelnitsky city).

 

[sem]

Nice city with delicious cuisine My mother is from Kamenets-Podilsky. The world is so small!

Regards, Dima