Hello together,

i like to count all jobs in my baan environment (in all companies)
i have tryed it with an dynamic sql statement like this:


all.comp = ""
select ttaad100.comp:comp
from ttaad100
where ttaad100._compnr = 000
selectdo
all.comp = all.comp & "," & str$(comp)
endselect

string.scan(all.comp, "%s,%s", alt, all.comp2)


to get all copanies which exits.


sql = sql.parse(
"select count(ttaad500.cjob):anzahl_jobs " &
"FROM ttaad500 " &
"WHERE ttaad500._compnr IN " & "(" & all.comp2 & ")",PARSE.ANSI)
sql.exec(sql)
sql.close(sql)

But it doesn't works.
Has anybody an idea, how can i get the job number of all companies.

thanks

hi,olliwest
fyi

sql = sql.parse(
"select count(ttaad500.cjob):1 " &
"FROM ttaad500 " &
"WHERE ttaad500._compnr IN " & "(" & all.comp2 & ")")

sql.select.bind(sql, 1, anzahl_jobs)

sql.exec(sql)
while ( true )
on case ( sql.fetch(sql) )
case eendfile:
break
case 0:
anzahl_jobs =anzahl_jobs
continue
default: | error
endcase
break
endwhile

sql.close(sql)

I would prefer to do this as follows:

- generate a new session with any dummy main table (say Areas) to begin with. This gives the insfrastructure (session/form/report) to make further modifications.
- modify the script (and form and report) as needed. [Especially, in the report, remember to add the field ttaad100.comp, so it is possible to identify which Company the Job 'belongs' to].
- specifically, the script would be like follows and replaces the automatically generated select (which would be for the dummy table):


select ttaad100.*
from ttaad100
where ttaad100._compnr = 000
and ttaad100.comp inrange :ncmp.f and :ncmp.t
selectdo
select ttaad500.*
from ttaad500
where ttaad500._index1 inrange {:cjob.f}
and {:cjob.t}
and ttaad500._compnr = :ttaad100.comp
order by ttaad500._index1
selectdo
rprt_send()
endselect
endselect


- thereafter, it is possible to produce any counts by making suitable modifications to the report layouts.

Aggregate function and compnr dont go together . Try splitting the same and accumulate separately . It should work .

hello together,

i think i have another problem.
There are companies which doesn't have any jobs, so it returns an error like
"unknown column ttaad._compnr"
how can i absorb this error?

thanks