Hi,

Following query for a report works and if keep the session open and run again does not give any o/p in fact do not enter the selectdo

where tdsls401._index1 inrange {:orno3.f} and {:orno3.t}
and tdsls401.ofbp between :cuno3.f and :cuno3.t
and tdsls401.invd between :ud.f and :ud.t
and tdsls400.crep between :crep3.f and :crep3.t
and tdsls401.ttyp between :ttyp3.f and :ttyp3.t
and tdsls401.invn between :invn3.f and :invn3.t
and tdsls401.orno = tdsls400.orno
and tdsls400.crep = tccom001.emno
and tdsls400.sotp <> "IPS"
and tdsls401.ofbp = tccom100.bpid
and tdsls401.invd > 0
order by tdsls401._index7
selectdo

brp.ready(brp_id)
endselect


Regards
Kedar

If you post the rest of your code as well, we may be able to help.

Any of the variable that you use for selection is getting reset.

i.e, Any of orno3.f,orno3.t,:cuno3.f,:cuno3.t,:ud.f,:ud.t,:crep3.f, :crep3.t,:ttyp3.f, :ttyp3.t,:invn3.f,:invn3.t, tdsls400.orno,tccom100.bpid

Put the script into debug and breakpoint just before entering of the query and just at that point check which of the above variable is blank.

Mostly, somewhere in the script itself it is getting reset.

Also make sure there is a brp.close also before second run.

Hi All

Mark the report is closed before 2nd run.

and Ajesh checked the variables - those are also not set to some wrong data

here is code

choice.cont.process:
before.choice:

determine.report.grp()
execute(print.data)

choice.print.data:
on.choice:
print.form.3.selection()


|**************************************

functions:

function print.form.3.selection()
{
long cnt
if report.open() then
comp.nr = get.compnr()
print.fully.paid.commissions()
close.report()
endif
}



function long report.open()
{
brp_id = brp.open(spool.report, "", 1)
if brp_id > 1 then
return(true)
endif

return(false)
}


function close.report()
{
long nb

brp.close(brp_id)
if (spool.report = "D") then
file.stat(g.tmp.file, nb)
if not nb then
mess("ttstps0014",1)
endif
endif
}

function print.fully.paid.commissions()
{
long rt, cnt
domain tfgld.date ud.f, ud.t
long d, m, y
cnt =0
rt = num.to.date(invd3.f , y , m, d)

ud.f = date.to.utc(y, m, d, 0, 0, 0)
rt = num.to.date(invd3.t , y , m, d)
ud.t = date.to.utc(y, m, d, 23, 59, 59)



select tdsls401.*, tccom100.seak, tccom001.nama,tdsls400.*
from tdsls400,
tdsls401,
tccom001,
tccom100

where tdsls401._index1 inrange {:orno3.f} and {:orno3.t}
and tdsls401.ofbp between :cuno3.f and :cuno3.t
and tdsls401.invd between :ud.f and :ud.t
and tdsls400.crep between :crep3.f and :crep3.t
and tdsls401.ttyp between :ttyp3.f and :ttyp3.t
and tdsls401.invn between :invn3.f and :invn3.t
and tdsls401.orno = tdsls400.orno
and tdsls400.crep = tccom001.emno
and tdsls400.sotp <> "IPS"
and tdsls401.ofbp = tccom100.bpid
and tdsls401.invd > 0
order by tdsls401._index7
selectdo

brp.ready(brp_id)
cnt = cnt + 1
endselect

if cnt = 0 then
message("No Data Found, Report not Printed")
endif
}

Hi,

You might try using db.set.to.default() for all the used tables -


db.set.to.default(ttdsls401)
db.set.to.default(ttdsls400)
db.set.to.default(ttccom100)
db.set.to.default(ttccom001)
select tdsls401.*, tccom100.seak, tccom001.nama,tdsls400.*
from tdsls400,
tdsls401,
tccom001,
tccom100

where tdsls401._index1 inrange {:orno3.f} and {:orno3.t}
and tdsls401.ofbp between :cuno3.f and :cuno3.t
and tdsls401.invd between :ud.f and :ud.t
and tdsls400.crep between :crep3.f and :crep3.t
and tdsls401.ttyp between :ttyp3.f and :ttyp3.t
and tdsls401.invn between :invn3.f and :invn3.t
and tdsls401.orno refers to tdsls400.orno
and tdsls400.crep refers to tccom001.emno
and tdsls400.sotp <> "IPS"
and tdsls401.ofbp refers to tccom100.bpid
and tdsls401.invd > 0
order by tdsls401._index7
selectdo

brp.ready(brp_id)
cnt = cnt + 1
endselect

Hi Bhushan

Thanks.

That I already tried, doesn't work.

Regards
Kedar

Just to get my understanding right:
- The 2nd time all variables preceded by : have the same value
- The 2nd time the select statement is reached
- The 2nd time the statements is the selectdo are not executed, so no record is found
Correct?
I would recommend to run bshell trace with SQL option set and/or dbslog. Then compare the data from the first and second execution.

Hi All

The issue with the invoice date input field invd3.f,
at first time if the full range then invd3.f = 0 and UTC conversion ud.f = -1
but 2nd or next run
invd3.f = 0 and UTC conversion ud.f = some value > 0

so please help to get every time the correct UTC conversion i.e. in full range 0 to till date
or with some date. i.e 29/04/2015 to 19/10/2015

Thanks
Kedar

The variables y, m, d keep the value from the successful conversion of invd3.t during the first run. 2 solutions:

1. Initialize y, m, d to 0 every time
2. Code something like:

if invd3.f = 0 then
ud.f = 0
else
rt = num.to.date(invd3.f , y , m, d)
ud.f = date.to.utc(y, m, d, 0, 0, 0)
endif

Thanks All

it's working now...

Regards
Kedar