My requirement is to fetch records from table form both archiving company and live company and write it to a file.

But i have encountered strange problem , if i execute the extraction session straight away after log on ... its extraction the data but after that session does not extract the data at all ... ( after log on i am trying to execute the session 2nd or 3rd time)

i have debugged it ... and found that that the query is not fetching the data set

can anyone let me know what might be the issue ?

select tfacp200.*
from tfacp200
where tfacp200._index4 = {:year.f}
and (tfacp200.prod >= :prod.f and
tfacp200.prod <= :prod.t)
and tfacp200._compnr = :live.comp
UNION
select tfacp200.*
from tfacp200
where tfacp200._index4 = {:year.f}
and (tfacp200.prod >= :prod.f and
tfacp200.prod <= :prod.t)
and tfacp200._compnr = :arch.comp
selectdo



I know i can go with individual company .... but if UNION works out that would be gr8

Simplify, there is no need to do the UNION of two select, one is enough:


select tfacp200.*
from tfacp200
where tfacp200._index4 = {:year.f}
and tfacp200.prod between :prod.f and :prod.t
and (tfacp200._compnr = :live.comp or tfacp200._compnr = :arch.comp)

Zardoz the code sample that you mention has an Error

pgbcor22000( 201): Error SQL: SQLState 42I30: Compnr specification in OR condition (error 302)

i think one cannot use and -- or with compnr

What I would do is create a function where I just passed the company number as a parameter - let say just print the records

function print.records()
{
get.records(prodcompany)
get.records(archcompany)
}

function get.records(domain tccomp some.compnr)
{
select tfacp200.*
from tfacp200
where tfacp200._index4 = {:year.f}
and (tfacp200.prod >= :prod.f and
tfacp200.prod <= :prod.t)
and tfacp200._compnr = :live.comp
selectdo
|do stuff
rprt_send()
endselect
}

Hi Mark,

that's my last resort ..;) anyway .. i changed it to individual company

but i wanted to know what might be the issue...

one more observation was ... the session is extracting if switch company using webtop --> tools --> change company.

I would also prefer the solution by Mark but it should also work with the UNION query. I only use the UNION ALL.

Just did a small test in our LN test system with a very simple session (print warehouses) for two companies.

Changed Code:
select tfacp200.*
from tfacp200
where tfacp200._index4 = {:year.f}
and (tfacp200.prod >= :prod.f and
tfacp200.prod <= :prod.t)
and tfacp200._compnr = :live.comp
UNION ALL
select tfacp200.*
from tfacp200
where tfacp200._index4 = {:year.f}
and (tfacp200.prod >= :prod.f and
tfacp200.prod <= :prod.t)
and tfacp200._compnr = :arch.comp
selectdo

regards,
Juergen

Is the union functionality even part of Baan 4c4? Never tried it myself, but will see if I can find time in the coming week. Just realized the only place I can test it is in production(only place we have two companies) - and I cannot develop there.

Mark, I guess, its not available in IV.