Please HELP with converting SQL query to 4GL

[phoenix_me]

I have SQL Query which I need to conver to 4GL. I am strugling with two issues. First, the query behave in a strange way, second, conversion.

The Query

select RD.ponum, V.VendorNum, AH.invoicedate, AH.invoiceamt, AH.entryperson
    from pub.rcvhead RH 
    inner join pub.rcvdtl RD on RD.company = 'EN' and RD.Vendornum =  RH.Vendornum and RD.purpoint = RH.purpoint and RD.packslip = RH.packslip
    inner join pub.vendor V on V.company = 'EN' and V.Vendornum = RD.Vendornum
    left outer join pub.apinvhed AH on AH.company = 'EN' and AH.vendornum = RD.vendornum and AH.invoicenum = RD.invoicenum    
    where AH.company = 'EN' and AH.InvoiceDate > '2010-08-01' and AH.InvoiceDate < '2010-08-15'

What is strange for me in this query it's a WHERE condition for LEFT OUTER JOIN table. Moreover the WHERE for AH.InvoiceDate seems to work as FILTER at the end of query execution.

Regarding conversion I was trying something like that:

FOR EACH RcvHed,
EACH RcvDtl WHERE RcvDtl.company = 'EN' and RcvDtl.Vendornum =  RcvHed.Vendornum and RcvDtl.purpoint = RcvHed.purpoint and  RcvDtl.packslip = RcvHed.packslip,
EACH Vendor WHERE Vendor.company = 'EN' and Vendor.Vendornum = RcvDtl.Vendornum,
EACH apinvhed OUTER-JOIN WHERE apinvhed.company = 'EN' and  apinvhed.vendornum = RcvDtl.vendornum and apinvhed.invoicenum =  RcvDtl.invoicenum and apinvhed.InvoiceDate > '2010-08-01' and  apinvhed.InvoiceDate < '2010-08-15

but such Query in 4GL gives me of course the same result as SQL query but without the 'Date' filter.

 

[MaximMonin]

...
EACH apinvhed OUTER-JOIN WHERE apinvhed.company = 'EN' and apinvhed.vendornum = RcvDtl.vendornum and apinvhed.invoicenum = RcvDtl.invoicenum and apinvhed.InvoiceDate > DATE(08,01,2010) and apinvhed.InvoiceDate < Date(08,15,2010)

 

[phoenix_me]

...
EACH apinvhed OUTER-JOIN WHERE apinvhed.company = 'EN' and apinvhed.vendornum = RcvDtl.vendornum and apinvhed.invoicenum = RcvDtl.invoicenum and apinvhed.InvoiceDate > DATE(08,01,2010) and apinvhed.InvoiceDate < Date(08,15,2010)

Sorry, maybe I was not enough precisely. I know that I need to use DATE to compare the same data types. And of course I did it. But as far as it's an 4GL OUTER-JOIN it doesn't change the number of rows in result, while SQL query (in given example) is reducing the number of rows in result.

 

[MaximMonin]

FOR EACH RcvHed,
EACH RcvDtl WHERE RcvDtl.company = 'EN' and RcvDtl.Vendornum = RcvHed.Vendornum and RcvDtl.purpoint = RcvHed.purpoint and RcvDtl.packslip = RcvHed.packslip,
EACH Vendor WHERE Vendor.company = 'EN' and Vendor.Vendornum = RcvDtl.Vendornum,
EACH apinvhed WHERE apinvhed.company = 'EN' and apinvhed.vendornum = RcvDtl.vendornum and apinvhed.invoicenum = RcvDtl.invoicenum:

if available apinvhed and not (apinvhed.InvoiceDate > DATE(08,01,2010) and apinvhed.InvoiceDate < Date(08,15,2010)) then next.
.....
end.

But if it is open query statement then
you have to use 2 queries.
if date isnot entered then use

FOR EACH RcvHed,
EACH RcvDtl WHERE RcvDtl.company = 'EN' and RcvDtl.Vendornum = RcvHed.Vendornum and RcvDtl.purpoint = RcvHed.purpoint and RcvDtl.packslip = RcvHed.packslip,
EACH Vendor WHERE Vendor.company = 'EN' and Vendor.Vendornum = RcvDtl.Vendornum,
EACH apinvhed OUTER-JOIN WHERE apinvhed.company = 'EN' and apinvhed.vendornum = RcvDtl.vendornum and apinvhed.invoicenum = RcvDtl.invoicenum:

if date entered then use inner-join

FOR EACH RcvHed,
EACH RcvDtl WHERE RcvDtl.company = 'EN' and RcvDtl.Vendornum = RcvHed.Vendornum and RcvDtl.purpoint = RcvHed.purpoint and RcvDtl.packslip = RcvHed.packslip,
EACH Vendor WHERE Vendor.company = 'EN' and Vendor.Vendornum = RcvDtl.Vendornum,
EACH apinvhed WHERE apinvhed.company = 'EN' and apinvhed.vendornum = RcvDtl.vendornum and apinvhed.invoicenum = RcvDtl.invoicenum and apinvhed.InvoiceDate > DATE(08,01,2010) and apinvhed.InvoiceDate < Date(08,15,2010)