I have a table containing a project number / purchase order number, and I have a project number in the purchase order detail lines.

select tdpur041.* and wtpur099.*, tdpur040.*
from tdpur041, tdpur040, wtpur099
where tdpur041.dim4 inrange :proj.f and :proj.t
or wtpur099.porj inrange :proj.f and :proj.t
and wtpur099.orno = tdpur040.orno
and tdpur040.orno inrange :orno.f and :orno.t
and tdpur041.orno refers to tdpur040

This does not work using the "or"
Either statment may be true, or both may be true.
Any suggestions.

Hi,

Put the following part of the query in brackets.

where (tdpur041.dim4 inrange :roj.f and :roj.t
or wtpur099.porj inrange :roj.f and :roj.t)

When posting code do not forget to turn smiles off otherwise you get a lot of :) in the code. Just a friendly reminder. Saves on editing time.

Thanks

Mark

This is what I have. I need both results to display all orders having a value that are in the range of proj.f and proj.t. Currently it is displaying the orders that are only related to the wtpur099 table.

functions:

function read.main.table()
{

select tdpur040.*, tsuno.nama:suno.nama, tccom000.*, tcmcs042.*,
tfacp302.*, tcmcs013.*, tcmcs002.*, tcmcs003.*, tccom001.*,
tcmcs011.*, tcmcs080.*, tcmcs034.*, tcmcs010.*, tcmcs046.*,
tpspr.nama:pspr.nama, tpstx.nama:pstx.nama, tcmcs041.*,
tfacp001.*, tcmcs031.*, tcmcs045.*, tdpur300.*, trtoc600.*,tdpur041.*, wtpur099.*
from tdpur040, tccom020 tsuno, tccom000, tcmcs042, tfacp302,
tcmcs013, tcmcs002, tcmcs003, tccom001, tcmcs011, tcmcs080,
tcmcs034, tcmcs010, tcmcs046, tccom020 tpspr, tccom020 tpstx,
tcmcs041, tfacp001, tcmcs031, tcmcs045, tdpur300, trtoc600, tdpur041, wtpur099
where (tdpur040.orno inrange :orno.f and :orno.t
or wtpur099.ponumber inrange :orno.f and :orno.t)
and (tdpur041.dim4 inrange :proj.f and :proj.t or
wtpur099.project inrange :proj.f and :proj.t)
and tdpur040.suno refers to tsuno
and tdpur040.comp refers to tccom000
and tdpur040.cotp refers to tcmcs042
and tdpur040.ragr refers to tfacp302
and tdpur040.cpay refers to tcmcs013
and tdpur040.ccur refers to tcmcs002
and tdpur040.cwar refers to tcmcs003
and tdpur040.ccon refers to tccom001
and tdpur040.ccrs refers to tcmcs011
and tdpur040.cfrw refers to tcmcs080
and tdpur040.cplp refers to tcmcs034
and tdpur040.ccty refers to tcmcs010
and tdpur040.clan refers to tcmcs046
and tdpur040.pspr refers to tpspr
and tdpur040.pstx refers to tpstx
and tdpur040.cdec refers to tcmcs041
and tdpur040.cfsg refers to tfacp001
and tdpur040.cbrn refers to tcmcs031
and tdpur040.creg refers to tcmcs045
and tdpur040.prno refers to tdpur300
and tdpur040.ctrj refers to trtoc600
and tdpur041.orno refers to tdpur040
and wtpur099.ponumber = tdpur041.orno **When I leave this statment out, it displays every purchase order in the system ***
order by tdpur040._index1
selectdo
if tdpur040.ccur <> "CAD"
then tdpur041.amta = tdpur041.amta * tdpur040.ratp
endif
rprt_send()
endselect
}

This is what I have. I need both results to display all orders having a value that are in the range of proj.f and proj.t. Currently it is displaying the orders that are only related to the wtpur099 table.

I'm not sure if I understand you well. But of course the line 'and wtpur099.ponumber = tdpur041.orno' causes you to get only those records from tdpur041 that exist also in wtpur099.

Please explain clearly what you want to achieve, and what the problems are.


Regards,

En.

I needed to get data from purchase table where tdpur041.dim4 is in a range of a value entered on the form (proj.f and proj,t). Then I also need to get data from purchase table based on the values in my other table wtpur099, which contains a purchase order number and project number(proj.f and proj.t) which is also on the form as a range

Oh ok, I think now I get it. You want to select 2 ranges from tdpur041. 1) all records from tdpur041 where tdpur041.dim4 inrange :proj.f and :proj.t and 2) all records also from tdpur041 where for which there is a record available in wtpur099. Is this correct?

Then you should try using the EXISTS condition, something like this:
where ((tdpur040.orno inrange :orno.f and :orno.t or wtpur099.ponumber inrange :orno.f and :orno.t)
or (EXISTS (select wtpur099.*
from wtpur099
where wtpur099.project inrange :proj.f and :proj.t)))


You will have to complete the syntax according to your needs, but for the basic idea, I hope this helps...

Good luck!

En.

Thanks for your help

Did you manage witht he code I suggested? Does everything work now as desired?