Rbuilder Ignores Distinct Command

rbuser

Hello

The code below is from the end user reporter before my report is run.
We are using advantage database server, Rb Ent 7.02 , Delphi7.
if the value in INVITEMS.QUANTITY is 2 then we get normal repeated entries
in report.
If we paste this code into a AdsQuery and show results in DBGrid, then
records are NOT duplicated.
Thus producing different result and ignoring the DISTINCT command.

Is this a problem or I am oing something wrong
many thanks

Andy Dyble

SELECT DISTINCT INVHEAD.INV_NO, INVHEAD.INV_DATE,
INVHEAD.SALES_ACC, INVHEAD.TYPE,
INVHEAD.SALES_NAME,
INVHEAD.ADDRESS1,
INVHEAD.ADDRESS2,
INVHEAD.ADDRESS3,
INVHEAD.ADDRESS4,
INVHEAD.POSTCODE,
INVHEAD.TELEPHONE,
INVHEAD.VAT_NUMBER,
INVHEAD.DEL_NAME,
INVHEAD.DEL_ADD1,
INVHEAD.DEL_ADD2,
INVHEAD.DEL_ADD3,
INVHEAD.DEL_ADD4,
INVHEAD.DEL_PCODE,
INVHEAD.DUE_DATE,
INVHEAD.DEL_DATE,
INVHEAD.ORDERTAKEN,
INVHEAD.ORDER_NO,
INVHEAD.CUST_ORDER,
INVHEAD.NO_ITEMS,
INVHEAD.INV_PRINT,
INVHEAD.RELATE_TO,
INVHEAD.TOT_NETT,
INVHEAD.TOT_VAT,
INVHEAD.TOT_GROSS,
INVHEAD.NOTES_1, INVHEAD.NOTES_2,
INVHEAD.NOTES_3,
INVHEAD.CARR_NETT,
INVHEAD.CARR_TCODE,
INVHEAD.CARR_TRATE,
INVHEAD.CARR_TAMM,
INVHEAD.CARR_NOM,
INVHEAD.CARR_DEPT,
INVHEAD.CARR_GROSS,
INVHEAD.SETT_DAYS,
INVHEAD.SETT_DISC,
INVHEAD.EARLY_PAY,
INVHEAD.G_TAXCODE,
INVHEAD.G_TAXRATE,
INVHEAD.G_NOMINAL,
INVHEAD.G_DEPT, INVHEAD.G_DESC,
INVHEAD.ITEM_NETT,
INVHEAD.ITEM_TAX,
INVHEAD.ITEM_GROSS,
INVHEAD.PRINTED, INVHEAD.POSTED,
INVHEAD.PAY_VALUE,
INVHEAD.PAY_REF,
INVHEAD.PAY_BANK,
INVHEAD.PAY_AUTO,
INVHEAD.PAY_DATE,
INVHEAD.ANALYSIS,
INVHEAD.TOT_COST,
INVHEAD.TOT_MARGIN,
INVITEMS.S_CODE, INVITEMS.S_DESC,
INVITEMS.QUANTITY,
INVITEMS.UNIT_PRICE,
INVITEMS.GROSS_MARG,
INVITEMS.UNIT_COST,
INVITEMS.INV_NO AS INV_NO_2,
extract(year from inv_date)*12 + month(inv_date) AS
extract_year_from_inv_dat,
month(inv_date) AS month_inv_date
FROM INVHEAD INVHEAD
LEFT OUTER JOIN INVITEMS INVITEMS ON
(INVITEMS.INV_NO = INVHEAD.INV_NO)
WHERE ( INVHEAD.INV_DATE >= '2005-05-01' )
AND ( INVHEAD.INV_DATE <= '2099-05-31' )
ORDER BY 68

rbuser