Hi,

I am finding difficulty for getting right output.
When I Take tdsfl003 as main table I get different output and when I Take tdrpl100 as main table I get different output. I want the output from both the table inorder to finalise the report .

My Script is:

functions:
function read.main.table()
{
select tdrpl100.*
from tdrpl100
where tdrpl100.rwar = :rwar
and tdrpl100.dwar = :dwar
and tdrpl100.rddt between :odat.f and :odat.t
order by tdrpl100.orno,tdrpl100.pono,tdrpl100.item
selectdo
select tdsfl003.*
from tdsfl003
where tdsfl003.orno in (select tdrpl100.orno from tdrpl100)
and tdsfl003.pono = :tdrpl100.pono
and tdsfl003.item = :tdrpl100.item
and tdsfl003.trtp = "MREQ"
selectdo
tdsfl003.orno = tdrpl100.orno
tdsfl003.pono = tdrpl100.pono
tdsfl003.item = tdrpl100.item
selecteos
tdsfl003.orno = tdrpl100.orno
tdsfl003.pono = tdrpl100.pono
tdsfl003.item = tdrpl100.item
selectempty
tdsfl003.orno = tdrpl100.orno
tdsfl003.pono = tdrpl100.pono
tdsfl003.item = tdrpl100.item
endselect
select tiitm001.*
from tiitm001
where tiitm001.item = :tdrpl100.item
selectdo
endselect

rprt_send()

endselect
}

Not sure what you are trying to accomplish. With the code you posted the nested query on tdsfl003 looks kind of useless - you always do the same three commands no matter how records are found or not found. What exactly are you trying to report?

This piece puzzles me:

where tdsfl003.orno in (select tdrpl100.orno from tdrpl100)
and tdsfl003.pono = :tdrpl100.pono
and tdsfl003.item = :tdrpl100.item
and tdsfl003.trtp = "MREQ"

Are you just trying to find any order in tdsfl003 which also has an order in tdrpl100? So the outer loop could finr tdrpl100.orno of 123 - then the nested query could then find order 1 since it has the same item, position and trtp = "MREQ". Of course it could find multiple orders - like 1, 2 and 3. Then order 3 tdsfl003 will be used for the report.

Can you write out exactly what you are trying - someone else might be able to make a recommendation.

HI,

I'm trying to get the data from both the table(if tdrpl100.orno = tdsfl003.orno
and tdrpl100.orno <> tdsfl003.orno)

I think the best way to get what you want is to select at first all records of the first table where also records exist in the second table. After that process all records of the first table where no record exists in the second table.

Hi,

How can I do that...(pls explain with script)

HI,

I'm trying to get the data from both the table(if tdrpl100.orno = tdsfl003.orno
and tdrpl100.orno <> tdsfl003.orno)

Your script did that.

select tdrpl100.*
from tdrpl100
where tdrpl100.rwar = :rwar
and tdrpl100.dwar = :dwar
and tdrpl100.rddt between :odat.f and :odat.t
order by tdrpl100.orno,tdrpl100.pono,tdrpl100.item
selectdo

The above gets all the records in tdrpl100 based off the input fields. Then this query:

select tdsfl003.*
from tdsfl003
where tdsfl003.orno = :tdrpl100.orno
as set with 1 rows
selectdo
| Here you know the tdrpl100 order is in tdsfl003
selectempty
| Here you know the tdrpl100 order is not in tdsfl003
| So init report fields here
endselect

Now if you also need to know which orders are in tdsfl003 and NOT in tdrpl100 then you do a second pass reversing the nested queries. Granted I do not know the tables or the indexes so I do not know what all needs to be in the join. Unless I am misunderstanding what you want this is a simple check.

HI,
Take the data from tdrpl100 based on order date and check the status should be delivered and then check that order into tdsfl003 table if RPL order no is available then print it otherwise leave it blank.

Join between two table is RPL order no and warehouse and date.

This is what I should do.....
My script is

functions:
function read.main.table()
{
select tdrpl100.*
from tdrpl100
where tdrpl100.rwar = :rwar
and tdrpl100.dwar = :dwar
and tdrpl100.odat between :odat.f and :odat.t
and tdrpl100.srpl > 1
order by tdrpl100.orno
selectdo
select tdsfl003.*
from tdsfl003
where tdsfl003.rwar = :tdrpl100.rwar
and tdsfl003.dwar = :tdrpl100.dwar
and tdsfl003.orno = :tdrpl100.orno
and tdsfl003.trtp = "MREQ"
|as set with 1 rows
selectdo
tdsfl003.orno = 0
selectempty
tdsfl003.orno = tdrpl100.orno

endselect
select tiitm001.*
from tiitm001
where tiitm001.item = :tdrpl100.item
selectdo
endselect
|if tdrpl100.orno = tdsfl003.orno then
|else
rprt_send()
|endif
endselect
}

Tdsfl003 table
dwar -Dest.W/h
preq -Pos req.no
ppos-Pos position no
seqn-Seqn no
orno- Rpl order no
pono-Rpl Posn. no
srnb-Rpl Seqn. no
item- Item Code
quan- Quantity
rwar- Repl. W/h
trtp- TransactionType
date-Date
time- Time
succ- Succesful
rmrk-Remark

Is the above script right according to expln. Please rectify if it is wrong bcos i'm facing same problem as I have explained bfore.Any other idea is appreciated