Hi,

error : "type incompatibility: [string] and [number].
select tdkpm010.*, tdkpm015.*, tdkpm014.*
from tdkpm010, tdkpm015, tdkpm014
where tdkpm010.orno = :tdkpm008.orno
and tdkpm010.item = tdkpm015.item
and tdkpm015.odat <= :odat.t
and tdkpm014.size = tdkpm010.item(8;4)
order by tdkpm010._index1
how to solve this problem

tdkpm014.size = tdkpm010.item(8;4)
size is number

tdkpm010.item(8;4) is string

how to convert?

Hi,

error : "type incompatibility: [string] and [number].

Hello :

select tdkpm010.*, tdkpm015.*, tdkpm014.*
from tdkpm010, tdkpm015, tdkpm014
where tdkpm010.orno = :tdkpm008.orno
and tdkpm010.item = tdkpm015.item
and tdkpm015.odat <= :odat.t
and tdkpm014.size = tdkpm010.item(8;4)
order by tdkpm010._index1
how to solve this problem

tdkpm014.size = lval(tdkpm010.item(8;4))


if size is long
take val if size is double

tdkpm010.item(8;4) is string


Regards

Hi,

error : "type incompatibility: [string] and [number].

Hello :

select tdkpm010.*, tdkpm015.*, tdkpm014.*
from tdkpm010, tdkpm015, tdkpm014
where tdkpm010.orno = :tdkpm008.orno
and tdkpm010.item = tdkpm015.item
and tdkpm015.odat <= :odat.t
and tdkpm014.size = tdkpm010.item(8;4)
order by tdkpm010._index1
how to solve this problem

tdkpm014.size = lval(tdkpm010.item(8;4))

if size is long
take val if size is double

tdkpm010.item(8;4) is string


Regards

Hi thanks for the support.

Hi thanks for the support.
Can you pls help me in this?


tdkpm010.size - integer
tdkpm008.item(8;4) - string

The code :

long i
extern domain tdstr6 size
functions:

function read.main.table()
{

select tdkpm008.*, tdkpm007.*, tdkpm014.*
from tdkpm008, tdkpm007, tdkpm014
where tdkpm008.odat between :odat.f and :odat.t
and tdkpm008.cuno between :cuno.f and :cuno.t
and tdkpm008.eono between :eono.f and :eono.t
and tdkpm008.orno between :orno.f and :orno.t
and (tdkpm007.clsd = :sts.f or tdkpm007.clsd = :sts.t)
and tdkpm008.orno refers to tdkpm007
and tdkpm014.orno = tdkpm008.orno
and tdkpm014.prod = tdkpm008.kpmc
order by tdkpm008.cuno
selectdo
i = tdkpm014.size
if i < 1000 then
size = "0"&str$(tdkpm014.size)
else
size = str$(tdkpm014.size)
endif
get.del.qty()
rprt_send()
endselect
}



function get.del.qty()
{
select tdkpm010.*, tdkpm015.*
from tdkpm010, tdkpm015
where tdkpm010.orno = :tdkpm008.orno
and tdkpm010.pono = :tdkpm008.pono
and tdkpm010.kpmc = :tdkpm008.kpmc
and tdkpm010.orn1 = tdkpm015.orno
and tdkpm010.item = tdkpm015.item
and tdkpm010.orno = :tdkpm014.orno
and tdkpm010.kpmc = :tdkpm014.prod
and tdkpm010.item(8;4) = :size selectdo
rprt_send()
endselect
}



It display duplicate data. that means repeating the data again and again. pls guide me. thanks

for example : output

orno ----------- lot no ----------- quantity ---------- weight
12 ------------ 80-c ----------- 25 ----------- 289.0000
12 ------------- 80-c ----------- 25 ----------- 289.0000

output suppose to be the following one :

orno ----------- lot no ----------- quantity ---------- weight
12 ------------ 80-c ----------- 25 ----------- 289.0000

can u pls guide me?
thanks

Regards
Anusha

Why using the rprt_send() command twice and send the data again to the report?
Remove the rprt_send() in function get.del.qty()

Regards,
Juergen

oh ya. thank you :).

Hi,

extern domain tcqsl1 tmp.qty
extern domain tcqsl1 tmp.wgt

extern domain tcqsl1 tot.tmp.qty
extern domain tcqsl1 tot.tmp.wgt

functions:

function read.main.table()
{

select tdkpm008.*, tdkpm007.*, tdkpm014.*
from tdkpm008, tdkpm007, tdkpm014
where tdkpm008.odat between :odat.f and :odat.t
and tdkpm008.cuno between :cuno.f and :cuno.t
and tdkpm008.eono between :eono.f and :eono.t
and tdkpm008.orno between :orno.f and :orno.t
and (tdkpm007.clsd = :sts.f or tdkpm007.clsd = :sts.t)
and tdkpm008.orno refers to tdkpm007
and tdkpm014.orno = tdkpm008.orno
and tdkpm014.prod = tdkpm008.kpmc
order by tdkpm008.cuno
selectdo

i = tdkpm014.size
if i < 1000 then
size = "0"&str$(tdkpm014.size)
else
size = str$(tdkpm014.size)
endif

get.del.qty()

tmp.qty = 0
tmp.wgt = 0

tot.tmp.qty = tot.tmp.qty + tmp.qty
tot.tmp.wgt = tot.tmp.wgt + tmp.wgt

endselect
}

function get.del.qty()
{
select tdkpm010.*, tdkpm015.*
from tdkpm010, tdkpm015
where tdkpm010.orno = :tdkpm008.orno
and tdkpm010.pono = :tdkpm008.pono
and tdkpm010.kpmc = :tdkpm008.kpmc
and tdkpm010.orn1 = tdkpm015.orno
and tdkpm010.item = tdkpm015.item
and tdkpm010.orno = :tdkpm014.orno
and tdkpm010.kpmc = :tdkpm014.prod
and tdkpm010.item(8;4) = :size
selectdo
tmp.qty = tmp.qty + tdkpm015.totq
tmp.wgt = tmp.wgt + tdkpm015.totw
rprt_send()
endselect
}


The output total for tmp.qty and tmp.wgt is correct.
But the output total for tot.tmp.qty and tot.tmp.wgt is not correct.

I dont know wat is the bugs in program script. can you pls help me?

Thanks
Regards
Anusha

Please check your script.

Before calculating the totals tot.tmp.qty and tot.tmp.wgt you set the values for tmp.qty and tmp.wgt to Zero. This dosen`t work.

Regards,
Juergen

Hi, Thank you very much

i could not get the grand total(tot.tmp.qty) and (tot.tmp.wgt) . is this because of program script bugs or report script? pls help me.

Program script code :

declaration:


table ttdkpm007
table ttdkpm008
table ttccom010
table ttdkpm010
table ttdkpm015
table ttdkpm014

extern domain tcdate odat.f | Sales order date
extern domain tcdate odat.t

extern domain tccuno cuno.f | Sales order date
extern domain tccuno cuno.t

extern domain tcorno orno.f | Sales order date
extern domain tcorno orno.t

extern domain tcmcs.st20 eono.f | Sales order date
extern domain tcmcs.st20 eono.t

extern domain tcyesno status

extern domain tcyesno sts.f

extern domain tcyesno sts.t

extern domain tcqiv1 tmp.qty
extern domain tcqiv1 tmp.wgt

extern domain tcqiv1 osqty
extern domain tcqsl1 delqty
extern domain tcqiv1 kpmc.osqty
extern domain tcqiv1 quan

long i
extern domain tdstr6 size



|****************************** form section **********************************

form.1:
init.form:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:


execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then

if status = tcyesno.yes then
sts.f = tcyesno.no
sts.t = tcyesno.no
endif
if status = tcyesno.no then
sts.f = tcyesno.no
sts.t = tcyesno.yes
endif

read.main.table()

rprt_close()

else
choice.again()
endif


|****************************** field section *********************************

field.odat.f:
when.field.changes:
odat.t = odat.f

field.cuno.f:
when.field.changes:
cuno.t = cuno.f

field.orno.f:
when.field.changes:
orno.t = orno.f

field.eono.f:
when.field.changes:
eono.t = eono.f


|****************************** function section ******************************

functions:

function read.main.table()
{

select tdkpm008.*, tdkpm007.*, tdkpm014.*
from tdkpm008, tdkpm007, tdkpm014
where tdkpm008.odat between :odat.f and :odat.t
and tdkpm008.cuno between :cuno.f and :cuno.t
and tdkpm008.eono between :eono.f and :eono.t
and tdkpm008.orno between :orno.f and :orno.t
and (tdkpm007.clsd = :sts.f or tdkpm007.clsd = :sts.t)
and tdkpm008.orno refers to tdkpm007
and tdkpm014.orno = tdkpm008.orno
and tdkpm014.prod = tdkpm008.kpmc
order by tdkpm008.cuno
selectdo
read.sub.table1()



i = tdkpm014.size
if i < 1000 then

size = "0"&str$(tdkpm014.size)

else

size = str$(tdkpm014.size)

endif
quan = tdkpm014.oqua + tdkpm014.dqua
get.del.qty()

osqty = (quan - tmp.wgt)

tmp.qty = 0
tmp.wgt = 0
endselect

}

function read.sub.table1()
{

select tccom010.*
from tccom010
where tccom010.cuno = :tdkpm008.cuno
selectdo

endselect
}


function get.del.qty()
{

select tdkpm010.*, tdkpm015.*
from tdkpm010, tdkpm015
where tdkpm010.orno = :tdkpm008.orno
and tdkpm010.pono = :tdkpm008.pono
and tdkpm010.kpmc = :tdkpm008.kpmc
and tdkpm010.orn1 = tdkpm015.orno
and tdkpm010.item = tdkpm015.item
and tdkpm010.orno = :tdkpm014.orno
and tdkpm010.kpmc = :tdkpm014.prod
and tdkpm010.item(8;4) = :size
selectdo

tmp.qty = tmp.qty + tdkpm015.totq
tmp.wgt = tmp.wgt + tdkpm015.totw
rprt_send()


endselect

}



Report script code :

|******************************************************************************
|* tdkpm045911000 VRC B40C c2 MPM
|* Print Sales Invoice by Terms
|* anusha
|* 11-02-10
|******************************************************************************
declaration:


table ttdkpm015
table ttdkpm010



extern domain tcqiv1 tot.tmp.qty
extern domain tcqiv1 tot.tmp.wgt


after.report.1:

before.layout:

select tdkpm015.*, tdkpm010.*
from tdkpm015, tdkpm010
where tdkpm015.orno = tdkpm010.orn1
and tdkpm015.item = tdkpm010.item
and tdkpm010.kpmc = :tdkpm008.kpmc
and tdkpm015.serc <> 0
selectdo
tot.tmp.qty = tot.tmp.qty + tdkpm015.totq
tot.tmp.wgt = tot.tmp.wgt + tdkpm015.totw
endselect

after.layout:
tot.tmp.qty = 0
tot.tmp.wgt = 0



The output grand total i am getting tot.tmp.qty = 0 and tot.tmp.wgt = 0. can pls help me?
thanks

Regards
Anusha

First in the report script you can eliminate the after.layout for the after.report section. Since you are doing this at the end of the report it will not really matter. If I was writing the code it would look like this:

after.report.1:
before.layout:
tot.tmp.qty = 0
tot.tmp.wgt = 0
select tdkpm015.*, tdkpm010.*
from tdkpm015, tdkpm010
where tdkpm015.orno = tdkpm010.orn1
and tdkpm015.item = tdkpm010.item
and tdkpm010.kpmc = :tdkpm008.kpmc
and tdkpm015.serc <> 0
selectdo
tot.tmp.qty = tot.tmp.qty + tdkpm015.totq
tot.tmp.wgt = tot.tmp.wgt + tdkpm015.totw
endselect

Since this is where tot.tmp.qty and tot.tmp.wgt are set - this is where the problem looks to be. First is tdkpm008.kpmc one of the report input fields? And will this field really be the same for all records on the report?

Got it. Thank u :)

In "Maintain Report Fields" screen, i enter
"(tdilc101.item(16;1)="N" or "2" or "3") and subs=subs.8" in where(expr) condition.

when i try to combine "Maintain report screen" it display error like : "Illegal type combination : "boolean or string"

can you please give me the solution in this?

Thanks

Regards
ANUSHA

Try tdilc101(16;1) in ("N","2", "3") and subs=subs.8. Don't know if the subs piece might cause a problem since I do not know how they are defined. Never really tried this myself - at least not in where clause. I assume this is a field you are totaling or something.

Hi,

can pls help me in this:

In Report script i using the following code:

declaration:
table ttdkpm045 | Delivery

detail.1:
before.layout:


select sum(tdkpm045.qstk), sum(tdkpm045.qstr), tdkpm045.orno, tdkpm045.pono, tdkpm045.clot(2;1)
from tdkpm045
where tdkpm045.orno = :tdsls045.orno
and tdkpm045.pono = :tdsls045.pono
group by tdkpm045.orno, tdkpm045.pono, tdkpm045.clot(2;1)
selectdo

endselect
after.layout:
-----------------------------------------

tdkpm045.clot(2;1) is string (16 characters). but for this report i want to use only tdkpm045.clot(2;1) "1 character". i want to group by this 1 character.

can anybody help me on this. thanks in advance. :-)

Easiest thing to do is to change the session script which calls this report to pull this one character and send it to the report. There are a couple of other reporting options on this board. The one I posted involves reading the input file and sorting it yourself - plus I there is another that I think looked easier. I can't remember who posted that one. Let me do some looking.

Had it booked marked by http://www.baanboard.com/baanboard/showthread.php?threadid=9090 - thread by gunther. He has posted a couple of other times on this topic. The solution I posted was based on the after.receive.data - you can search for it.

hi,

table1 : tdkpm014
fields : salesReq,
prod

table2: tdkpm010
fields : salesReq,
salesOrder,
prod

table3: tdkpm015
fields : salesOrder,
lotid

tdkpm014 has size : 700, 900, 1000
But tdkpm015 has item.
tdkpm015.item(8;4) = tdkpm014.size
kn421800700xxxxn, kn421900800xxxxn, kn421800900xxxxn, kn421801000xxxxn.



i want to write a query to display size 800 from tdkpm015 which is not exist in tdkpm014.

I tried this query in report script:

detail.2:
before.layout:

SELECT tdkpm010.*, tdkpm015.*
FROM tdkpm010, tdkpm015
WHERE tdkpm010.salesOrder = tdkpm015.salesOrder
AND NOT EXISTS (SELECT * FROM tdkpm014
WHERE tdkpm014.salesReq = tdkpm010.salesReq
AND tdkpm014.prod = tdkpm010.prod)
selectdo

endselect

this method is not working.
Can pls help me on this?

Thanks

Regards
Anusha

My personal preference is to always use separate and nested queries to make it easier for other developers who follow me and support the session. While maybe not the best programming technique it works for me.

hi,
can u pls help me on this?

report script:

table ttdkpm014
table ttdkpm010

long i
extern domain tdstr6 tdkpm014.size

before.tdkpm010.item.1:
before.layout:

after.layout:

select tdkpm014.*
from tdkpm014
where tdkpm014.orno = :tdkpm010.orno
and tdkpm014.prod = :tdkpm010.kpmc
and tdkpm014.size = :tdkpm010.item(8;4)
selectdo

if i < 1000 then

tdkpm014.size = "0"&str$(tdkpm014.size)

else

tdkpm014.size = str$(tdkpm014.size)

endif


endselect


error :
substring for non-string column not allowed.

notes:
tdkpm010.item(8;4) is string
tdkpm014.size is long.
how to fix this ? pls help me.

hi,
can u pls help me on this?

report script:

table ttdkpm014
table ttdkpm010

long i
extern domain tdstr6 tdkpm014.size

before.tdkpm010.item.1:
before.layout:

after.layout:

select tdkpm014.*
from tdkpm014
where tdkpm014.orno = :tdkpm010.orno
and tdkpm014.prod = :tdkpm010.kpmc
and tdkpm014.size = :tdkpm010.item(8;4)
selectdo

if i < 1000 then

tdkpm014.size = "0"&str$(tdkpm014.size)

else

tdkpm014.size = str$(tdkpm014.size)

endif


endselect


error :
substring for non-string column not allowed.

notes:
tdkpm010.item(8;4) is string
tdkpm014.size is long.
how to fix this ? pls help me.

Hello,
if tdkpm014.size is a long, use :

if i < 1000 then

tdkpm014.size = lval("0"&str$(tdkpm014.size))

else

tdkpm014.size = tdkpm014.size

endif

Regards

thank you. got it the solution :)

Hello,

select tdsls400.orno, tdsls400.sotp, tdsls400.odat, tdsls400.ofbp,
tdsls400.cfrw, tdsls400.cpls, tdsls400.cbrn,tdsls400.creg
from tdsls400
where tdsls400._index1 inrange {:orno.f, :sotp.f, :ofbp.f} and {:orno.t, :sotp.t, :ofbp.t}
and tdsls400.orno refers to tdsls400
and tdsls400.sotp refers to tdsls094
and tdsls400.orno >= 01012010
and (tdsls400.bkyn = 1)
order by tdsls400._index1
selectdo
rprt_send()
endselect
}

I got error message type incompatibility: [String] and [Number] in line and (tdsls400.bkyn = 1) after compile the program script. Any one can please tell me how to fix this error. Thank you

Try tcyesno.yes instead of 1. Of course I was thinking 1 should work.

is the query correct...tdsls400._index1 has only orno as index and the other thing i observe is tdsls400.orno >= 01012010 i think thats should be in quotes...