Hi,

I would like to list down the same lot number with the details. However, i have data that have extension after ".".
For example:
Lot Item
2131-2015.p123 Part A
2131-2015 Part B

My query is as below:

select
whltc100.clot, | Lot
whltc100.item, | Item
whltc100.orno, | Order
whltc100.frdt, | First Receipt Date
tisfc010.cwoc, | Work Center
tisfc010.mcno | Machine
from
tisfc010, | Production Order Operations
whltc100 | Lots
where
whltc100.clot >= whltc100.clot.f and
whltc100.clot <= whltc100.clot.t and
whltc100.item >= whltc100.item.f and
whltc100.item <= whltc100.item.t and
whltc100.orno = tisfc010.pdno


In report, i tried to use script as below:
LEFT(whltc100.clot,FIND(".",whltc100.clot&".")-1) = whltc100.clot

but not working.
Can members help me?
thank you in advance for your kind help.

Hi,

You need to use pos() function instead.

e.g.

posno = pos("abcdabcd", "bcd") | posno contains 2

Hence, you may try using -

whltc100.clot = (pos(whltc100.clot,".")+1,len(whltc100.clot)-pos(whltc100.clot,"."))

Hi Bhushan,

thank you for the reply.
i tried put the script as below in the report, but it not print at all.
In query, i'm requesting to print lot no 2131-2015.
so in report it should print out as below eventhough there is an extension after "."
2131-2015.p123
2131-2015

i hv insert screipt as below:

declaration:

long posno


before.program:
posno = pos(whltc100.clot,".")

detail.1:
before.layout:
if whltc100.clot (1;posno) = whltc100.clot then
lattr.print = true
else
lattr.print = false
endif

Oh, I thought, the lot number begins after the "."

In that case,

whltc100.clot = whltc100.clot(1;pos(whltc100.clot,".")-1)

But I didnt get the logic you are trying to use. It might always fail as both the fields you are comparing are same.

Can you please explain? I guess you might need an additional variable for comparison.

Hi,

in my database we have same lot no for with or without extension.
Lot number:
2131-2015.p123
2131-2015

so, i would like to download all lot number that have same number before "." for lot that hv extension.

my logic is that,
when system read lot with extension ".", it will read only figure in front before ".".
if system find same as lot in query then it will print out the lot with the details.

So you have whltc100.clot when entering the report script which is the selected lot? Put it in a variable like.lot = whltc100.clot(1;pos(whltc100.clot,".")-1) & ".*" and read the extra records with a select: where whltc100.clot like like.lot

Hi,

i hv amend the script as below but got an error "unexpected token 'lattr.print' (error 302).


declaration:

extern domain tcmcs.st20m lot

before.program:
lot = whltc100.clot(1;pos(whltc100.clot,".")-1) & ".*"

detail.1:
before.layout:

select lot
from whltc100
where whltc100.clot like lot
lattr.print = true
selectempty
lattr.print = false
endselect

Hi,

I think there is some problem with the code. The field selected is incorrect and selectdo is missing
select whltc100.lot
from whltc100
where whltc100.clot like lot
selectdo
lattr.print = true
selectempty
lattr.print = false
endselect

hi,

still got error: Unknown column 'lot' (error 302)

Oops, missed a colon.


select whltc100.lot
from whltc100
where whltc100.clot like {:lot}
selectdo
lattr.print = true
selectempty
lattr.print = false
endselect

Rather close...
Because what you need more (I suppose) is a loop to print the layout for all of the lots which meet the condition not only the last one (like this is what the code would do now)

So introduce an extra var containing the "current.lot" and bind it to the current lot
In the sql include the "and > current.lot
in the after.layout do layout againdetail.1:
before.layout:
select *, whltc100.lot:current.lot | current value within statement
from whltc100
where whltc100.clot like {:lot}
and whltc100.clot > :current.lot | previous value from outside statement
selectdo
lattr.print = true
selectempty
lattr.print = false
endselect
after.layout:
layout.again()

Hi,

i tried as per said, and still got error when execute the query.
The error as below:
Fatal Error: [MR_ARRY_ERR] Array bound error size -1 not in whltc100.clot
Fatal Error: Can not continue in rtisqltisfc001b0 (r.s.before.program) [last function: pos]

Hmmm, reading back you tell us that:2131-2015.p123 Part A
2131-2015 Part Bthese are the same lot.
We assumed in the rest of the story that you discriminate between lots by using the ".", but that is not the case in 2131-2015 Part B

From our position (not knowing your clot logic) assuming a standard separator for taking lots together (and using the "pos" command) would be one way to go. But in the above quoted you use "." (dot) in one case and " " in the other case.

You can use the pos on a "." if it fails use the " " ... etcetera
In your case if pos(whltc100.clot, ".") <> 0 then
|(use previous proposed code)
else
if pos(whltc100.clot, ".") <> 0 then
|(use previous code but replace "." by " "
else
if pos(whltc100.clot, "*" <> 0 then
|etcetera
endif
endif
endifAnother way is to use this construction:like.lot = whltc100.clot(1;9) & ".*"which uses the first 9 characters of the lot, regardless the lot setup.

i tried another flow whereby system will print all whinr110.clot that look a like whltc100.clot (user insert data).


declaration:

extern domain tcclot whltc100.clot

detail.1:
before.layout:

select whinr110.*
from whinr110
where whinr110.clot like whltc100.clot
selectdo
lattr.print = true
selectempty
lattr.print = false
endselect



but i got an error: Error SQL: SQLState 42I03: Unknown column 'whltc100.clot' (error 302)

i tried another flow whereby system will print all whinr110.clot that look a like whltc100.clot (user insert data).


declaration:

extern domain tcclot whltc100.clot

detail.1:
before.layout:

select whinr110.*
from whinr110
where whinr110.clot like whltc100.clot
selectdo
lattr.print = true
selectempty
lattr.print = false
endselect



but i got an error: Error SQL: SQLState 42I03: Unknown column 'whltc100.clot' (error 302)

Use:

where whinr110.clot like :whltc100.clot

Regards

Why dont you simply assign 2131-2015 to a local variable print.clot and use print.clot in the selection query.

Something like this


print.clot = "2131-2015"
select
whltc100.clot, | Lot
whltc100.item, | Item
whltc100.orno, | Order
whltc100.frdt, | First Receipt Date
tisfc010.cwoc, | Work Center
tisfc010.mcno | Machine
from
tisfc010, | Production Order Operations
whltc100 | Lots
where
whltc100.clot >= whltc100.clot.f and
whltc100.clot <= whltc100.clot.t and
whltc100.item >= whltc100.item.f and
whltc100.item <= whltc100.item.t and
whltc100.orno = tisfc010.pdno
and whltc100.clot like print.clot

Hi Ajesh,

i want to allow my user to insert any lot they wanted to print.

i tried another method whereby, i used whltc100.clot in query and print any lot the equal or alike from whinr110.clot.
but still not working. it still print out only with same lot number.



select
tcibd001.item, | Item
whinr110.clot, | Lot
tisfc010.cwoc, | Work Center
whltc100.orno | Order
from
tcibd001, | Items - General
tisfc010, | Production Order Operations
whinr110, | Inventory Transactions by Item and Warehouse
whltc100 | Lots
where
tcibd001.item >= tcibd001.item.f and
tcibd001.item <= tcibd001.item.t and
tcibd001.cpcl >= tcibd001.cpcl.f and
tcibd001.cpcl <= tcibd001.cpcl.t and
whltc100.clot >= whltc100.clot.f and
whltc100.clot <= whltc100.clot.t and
tcibd001.item = whinr110.item and
tcibd001.item = whltc100.item and
whinr110.koor = tckoor.act.sfc and
whinr110.kost = tckost.receipt and
whltc100.orno = tisfc010.pdno and
whinr110.clot like whltc100.clot

Hi,

Always use refers to clause when you are just referring a table for a single record. In this case, you can try the following -

select
tcibd001.item, | Item
whinr110.clot, | Lot
tisfc010.cwoc, | Work Center
whinr110.orno | Order
from
tcibd001, | Items - General
tisfc010, | Production Order Operations
whinr110, | Inventory Transactions by Item and Warehouse
whltc100 | Lots
where
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
tcibd001.cpcl >= tcibd001.cpcl.f and
tcibd001.cpcl <= tcibd001.cpcl.t and
whinr110.clot >= whinr110.clot.f and
whinr110.clot <= whinr110.clot.t and
whinr110.item refers to tcibd001 and
whinr110.koor = tckoor.act.sfc and
whinr110.kost = tckost.receipt and
whinr110.clot refers to whltc100 and
whinr110.orno refers to tisfc010.pdno


Also, I am not sure which order number you want to print. But, if you need to print production order number, the above is the correct way. Else, just replace the whinr110.orno with whltc100.orno

Also, if you further need to improve the performance for this query, replace the fields selection with indices as follows -

select
tcibd001.item, | Item
whinr110.clot, | Lot
tisfc010.cwoc, | Work Center
whinr110.orno | Order
from
tcibd001, | Items - General
tisfc010, | Production Order Operations
whinr110, | Inventory Transactions by Item and Warehouse
whltc100 | Lots
where
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
tcibd001.cpcl >= tcibd001.cpcl.f and
tcibd001.cpcl <= tcibd001.cpcl.t and
whinr110.clot >= whinr110.clot.f and
whinr110.clot <= whinr110.clot.t and
whinr110._index1 refers to tcibd001 and
whinr110.koor = tckoor.act.sfc and
whinr110.kost = tckost.receipt and
whinr110.clot refers to whltc100 and
whinr110.orno refers to tisfc010.pdno

Works fine for me. Hope it works for you as well.

Hi Bhushan,

if i refer direct to whltc100, system will print out only the same lot number as per user insert in the query.
however, i would like system to print all lot number that have almost the same as per user insert.

for example:
user insert: 1216-2.P1234

Then, system will print out:
2100-1216-0
2101-1216-3

so, based on previous suggestion, i have to use "Like" but seriously, i tried many times, but i still getting error.