a table that is writen but getting unfound....

[#1]

i have this table :

Custs:

//טבלת לקוחות

LOAD

CUSTNAME AS CUST_CUSTNAME,

CUST AS CUST_CUST,

ADDRESS AS CUST_ADDRESS,

CURRENCY AS CUST_CURRENCY,

CUSTDES AS CUST_CUSTDES

FROM [lib://CLINTON_SAMPLE/customers.qvd]

(qvd);

and i am trying to run this :

Inv_Sales_temp: //טבלת מכירות כללית

LOAD

Date (IVDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

IVNUM AS INV_INVNUM,

DOC AS INV_DOC,

IV,

QPRICE AS INV_QPRICE,

TOTPRICE AS INV_TOTPRICE,

"CURRENCY" AS INV_CURRENCY,

CUST AS INV_CUST,

DEBIT,

VAT  AS INV_VAT,

UDATE AS INV_UDATE,

PRINTED AS INV_PRINTED,

FINAL AS INV_FINAL,

ORD AS INV_ORD ,

DISCOUNT,

DISPRICE AS INV_DISCOUNT,

PAYDATE AS INV_PAYDATE,

FNCTRANS AS INV_FNCTRANS,

BOOKNUM AS INV_BOOKNUM,

AGENT AS INV_AGENT,

TYPE AS INV_TYPE,

CHECKING AS INV_CHECKING,

IMPFILE AS INV_IMPFILE,

STORNOFLAG AS INV_STORNOFLAG,

BRANCH AS INV_BRANCH

FROM [lib://CLINTON_SAMPLE/Invoices.qvd]

(qvd);

//פרטי ת.יומן  

Left join

LOAD

FNCTRANS AS INV_FNCTRANS,

FNCNUM,FINAL as FNC_FINAL,

CURDATE AS FNC_CURDATE,

FNCDATE,

EXCHANGE3 AS FNC_EXCHANGE3,

CURRENCY3 AS FNC_CURRENCY3,

IVNUM AS FNC_IVNUM,

EXCHANGE2 AS FNC_EXCHANGE2

FROM [lib://CLINTON_SAMPLE/Fnctrans.qvd]

(qvd)

Where FINAL ='Y';

left Join (Inv_Sales_temp)

LOAD

CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES

Resident Custs;

but i am getting this :

Table not found: left Join (Inv_Sales_temp) LOAD CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES Resident Custs

i dont realiy understand why.... since i have wrote down custs...

[#2]

hey Sangram

first of all thank you for your kind help

i am getting the following error.

i would try to compare it to different thing i have done....

[reddy-s]

Hi Jhonathan,

I think you missed out to attach the error or script I guess.

[swuehl · Accepted solution]

jhonatan ben ami wrote:

still getting this :

Table not found: left Join (Inv_Sales_temp) LOAD CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES Resident Custs

Have you really added the Exit script; statement beofre the JOIN?

Then the script should not proceed to the failing statement.

[#5]

hey Swel what do you mean by exit comment :

again i have this table:

CUSTOMERS:

LOAD

CUSTNAME AS CUST_CUSTNAME,

CUST AS CUST_CUST,

CUSTDES AS CUST_CUSTDES,

CTYPE AS CUST_CTYPE,

TERRITORY AS CUST_TERRITORY,

CUSTSTAT AS CUST_CUSTAT

// AGENT AS CUST_AGENTNAME  

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

FROM [lib://Clinton key/customers.qvd]

(qvd)

WHERE CUSTSTAT <> -5;

then i am trying to do this :

//נתונים נוספים לשורת חשבונית

Left Join

LOAD IV,

KLINE AS IV_KLINE,

COSTC2 AS IVA_COSTSC2,

COSTC3 AS IVA_COSTC3,

COSTC4 AS IVA_COSTC4,

COSTC5 AS IVA_COSTC5,

TOTPRICE AS IVA_TOTPRICE,

IVTAX AS IVA_IVTAX,

EXTCOST AS IVA_EXTCOST,

EXTCOST2 AS IVA_EXTCOST2,

BONUSFLAG AS IVA_BONUSFLAG

FROM [lib://CLINTON_SAMPLE/InvoiceItemsa.qvd]

(qvd);

// נתונים מכרטיס לקוח

left Join (Inv_Sales_temp)

LOAD

CUST_CUST,

CUST_CUSTNAME AS INV_CUSTNAME,

CUST_CUSTDES AS INV_CUSTDES

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

// CUST_AGENTNAME

RESIDENT

CUSTOMERS;

and i keep getting

[reddy-s]

Hi Jhonatan,

Do you have more script above the Customers table. Because I can't see a table named Inv_Sales_temp and this could be the cause of the error.

[#7]

Inv_Sales_temp: //טבלת מכירות כללית

LOAD

Date (IVDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

IVNUM AS INV_INVNUM,

DOC AS INV_DOC,

IV,

QPRICE AS INV_QPRICE,

TOTPRICE AS INV_TOTPRICE,

"CURRENCY" AS INV_CURRENCY,

CUST AS INV_CUST,

DEBIT,

VAT  AS INV_VAT,

UDATE AS INV_UDATE, // "T$USER",

PRINTED AS INV_PRINTED ,

FINAL AS INV_FINAL,

ORD AS INV_ORD ,

DISCOUNT,

DISPRICE AS INV_DISCOUNT,

PAYDATE AS INV_PAYDATE,

FNCTRANS AS INV_FNCTRANS,

BOOKNUM AS INV_BOOKNUM,

AGENT AS INV_AGENT,

TYPE AS INV_TYPE,

CHECKING AS INV_CHECKING,

IMPFILE AS INV_IMPFILE,

STORNOFLAG AS INV_STORNOFLAG,

BRANCH AS INV_BRANCH

FROM [lib://CLINTON_SAMPLE/Invoices.qvd]

(qvd);

//פרטי ת.יומן   

Left join

LOAD

FNCTRANS AS INV_FNCTRANS,

FNCNUM,

FINAL as FNC_FINAL,

CURDATE AS FNC_CURDATE,

FNCDATE,

EXCHANGE3 AS FNC_EXCHANGE3,

CURRENCY3 AS FNC_CURRENCY3,

IVNUM AS FNC_IVNUM,

EXCHANGE2 AS FNC_EXCHANGE2

FROM [lib://CLINTON_SAMPLE/Fnctrans.qvd]

(qvd)

Where FINAL ='Y'

;

Left Join (Inv_Sales_temp)

LOAD

IV,

PART AS IV_PART,

PRICE AS IV_PRICE,

QPRICE AS IV_QPRICE,

QUANT/1000 AS IV_QUANT,

"CURRENCY" AS IV_CURRENCY,

LINE AS IV_LINE,

ORDI AS IV_ORDI,

UDATE AS IV_UDATE,

KLINE AS IV_KLINE,

TQUANT/1000 AS IV_TQUANT,

TUNIT AS TUNIT,

TRANS AS IV_TRANS,

TOTPERCENT AS IV_TOTPERCENT,

(IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,

TYPE AS IV_TYPE,

//PRSOURCE,

//STORNOFLAG,

COSTC AS IV_COSTC,

CREDITCOST AS IV_CREDITCOST,

//ACCOUNT,

BRANCH AS IV_BRANCH,

IVDATE AS IV_IVDATE,

ICURRENCY AS IV_ICURRENCY,

IEXCHANGE AS IV_IEXCHANGE,

//COUNTRY,

CREDITFLAG AS IV_CREDITFLAG,

AGENT AS IV_AGENT,

COMMISSION AS IV_COMMISSION,

DUTYCOST AS IV_DUTYCOST     

FROM [lib://CLINTON_SAMPLE/InvoiceItems.qvd]

(qvd);

//נתונים נוספים לשורת חשבונית

Left Join

LOAD IV,

KLINE AS IV_KLINE,

COSTC2 AS IVA_COSTSC2,

COSTC3 AS IVA_COSTC3,

COSTC4 AS IVA_COSTC4,

COSTC5 AS IVA_COSTC5,

TOTPRICE AS IVA_TOTPRICE,

IVTAX AS IVA_IVTAX,

EXTCOST AS IVA_EXTCOST,

EXTCOST2 AS IVA_EXTCOST2,

BONUSFLAG AS IVA_BONUSFLAG

FROM [lib://CLINTON_SAMPLE/InvoiceItemsa.qvd]

(qvd);

// נתונים מכרטיס לקוח

left Join (Inv_Sales_temp)

LOAD

CUST_CUST,

CUST_CUSTNAME AS INV_CUSTNAME,

CUST_CUSTDES AS INV_CUSTDES

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

// CUST_AGENTNAME

RESIDENT

CUSTOMERS;

//נתונים מכרטיס הלקוח

/*

left Join (Inv_Sales_temp)

LOAD

CUST_CUST AS INV_CUST,

CUST_CUSTNAME AS INV_CUSTNAME,

CUST_CUSTDES AS INV_CUSTDES

Resident Custs;*/

//נתונים מכרטיס הפריט משורשרים לטבלת המכירות

left Join (Inv_Sales_temp)

LOAD

PARTNAME AS IV_PARTNAME,

PART_PART AS IV_PART,

PART_PRICE AS IV_PART_PRICE,

PART_SECONDPRICE AS IV_PART_SECONDPRICE,

PART_LASTPRICE AS IV_PARTLASTPRICE,

PART_LASTCURRENCY AS IV_PART_LASTCURRENCY, 

PART_COSTQUANT,

PART_COST as IV_PART_COST,

PART_SECONDCOST

// PART_MGCAT as IV_PART_MGCAT,

// PART_MGDEP as IV_PART_MGDEP

Resident Parts;

//נתונים משורות התנועה המקושרת לחשבונית- עלות וכדומה

left Join (Inv_Sales_temp)

LOAD

(IF(ISNULL(TRANS)=-1,0, TRANS)) AS IV_TRANS,

DOC as TRANS_DOC,

PART AS TRANS_PART,

QUANT/1000 AS TRANS_QUANT,

ORDI AS TRANS_ORDI,

CUST AS TRANS_CUST,

PRICE AS TRANS_PRICE,

IV,

COST AS TRANS_COST,

SECONDCOST AS TRANS_SECONDCOST,

BRANCH AS TRANS_BRANCH,

CURRENCY AS TRANS_CURRENCY,

COGFLAG AS TRANS_COSTFLAG,

CURDATE AS TRANS_CURDATE

FROM [lib://CLINTON_SAMPLE/Trans.qvd]

(qvd);

//שער מטבע לתאריך החשבונית ולמטבע השורה

Left JOIN (INV_SALES_TEMP)

LOAD  

CUR_CURRENCY AS INV_CURRENCY,

CUR_EXCHANGE

Resident Currencies;

LEFT JOIN (INV_SALES_TEMP)

LOAD

Date (CURREG_CURDATE/60/24+32143,'DD/MM/YYYY')  AS INV_IVDATE,

CURREG_CURRENCY  AS INV_CURRENCY,

CURREG_EXCHANGE

Resident Curreg;

left JOIN (INV_SALES_TEMP)

LOAD 

CUR_EXCHANGE AS CUR_EXCHANGE2

Resident Currencies Where CUR_CURRENCY = -2;

LEFT JOIN (INV_SALES_TEMP)

LOAD

Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

CURREG_EXCHANGE AS CURREG_EXCHANGE2

Resident Curreg Where CURREG_CURRENCY = -2;

// POSSIBLE EURO COIN

Left JOIN (INV_SALES_TEMP)

LOAD

CUR_EXCHANGE AS CUR_EXCHANGE3

Resident Currencies

Where CUR_CURRENCY =$(vForeignCurrency);

// שיניתי כי לא עבד בפעם הראשונה לא זהה את השדה

// POSSIBLE EURO COIN

left JOIN (INV_SALES_TEMP)

LOAD

Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY')AS INV_IVDATE ,

CURREG_EXCHANGE AS CURREG_EXCHANGE3

Resident Curreg

Where CURREG_CURRENCY =$(vForeignCurrency);

// LINE 183 IN THE SALES CHART//

LEFT JOIN(INV_SALES_TEMP)

LOAD

ACC_COSTFLAG,

DATE((NUM(MAX(ACC_COSTDATE)))/60/24+32143,'DD/MM/YYYY') AS MAXACC_COSTDATE

RESIDENT ACCDATES

WHERE ACC_COSTDATE<>0

GROUP BY ACC_COSTFLAG;

DROP TABLE ACCDATES;

[#8]

this is the first part:

CUSTOMERS:

LOAD

CUSTNAME AS CUST_CUSTNAME,

CUST AS CUST_CUST,

CUSTDES AS CUST_CUSTDES,

CTYPE AS CUST_CTYPE,

TERRITORY AS CUST_TERRITORY,

CUSTSTAT AS CUST_CUSTAT

// AGENT AS CUST_AGENTNAME  

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

FROM [lib://Clinton key/customers.qvd]

(qvd)

WHERE CUSTSTAT <> -5;

Part_Balance: // מלאי לפריט לפי מחסנים

Load

PART AS BALANCE_PART,

BALANCE AS BALANCE_BALANCE,

WARHS AS BALANCE_WARHS

FROM [lib://CLINTON_SAMPLE/Balance.qvd]

(qvd);

LEFT JOIN (Part_Balance)

LOAD

WARHS as BALANCE_WARHS,

WARHSDES AS BALANCE_WARHSDES,

WARHSNAME AS BALANCE_WARHSNAME,

TYPE AS WARHS_TYPE

FROM [lib://CLINTON_SAMPLE/Warhs.qvd]

(qvd);

Parts: //טבלת פריטים

LOAD

PARTNAME,

PART AS PART_PART,

TYPE AS PART_TYPE,

PARTDES,

UNIT AS PART_UNIT,

STATUS AS PART_STATUS,

PRICE AS PART_PRICE,

SECONDPRICE AS PART_SECONDPRICE ,

// MGSP_CATEGORY as PART_MGCAT,

// MGSP_DEPARTMENT AS PART_MGDEP,

LASTPRICE AS PART_LASTPRICE,

LASTCURRENCY AS PART_LASTCURRENCY,

COST AS PART_COST,

FAMILY AS PART_FAMILY,

SECONDCOST AS PART_SECONDCOST,

COSTQUANT AS PART_COSTQUANT

FROM [lib://CLINTON_SAMPLE/parts.qvd]

(qvd);

LEFT JOIN (Parts)

LOAD

BALANCE_PART AS PART_PART,

sum( BALANCE_BALANCE) AS PART_BALANCE

Resident Part_Balance

where WARHS_TYPE <> 'W'

Group by BALANCE_PART;

PartFam: // משפחות מוצר

LOAD

FAMILY AS FAMILY_FAMILY,

FAMILYNAME,

FAMILYDES,

FAMILYTYPE,

TYPE AS FAMILY_TYPE

FROM [lib://CLINTON_SAMPLE/Family.qvd]

(qvd);

Branches: //סניפים

LOAD

BRANCH AS BRANCH_BRANCH ,

BRANCHNAME,

BRANCHDES

FROM [lib://CLINTON_SAMPLE/Branches.qvd]

(qvd);

Currencies: // מטבעות ושערי חליפין

LOAD

NAME,

CODE AS CUR_CODE,

CURRENCY AS CUR_CURRENCY,

EXCHANGE AS CUR_EXCHANGE,

EXCHDATE AS CUR_EXDATE

FROM [lib://CLINTON_SAMPLE/Currency.qvd]

(qvd);

Curreg: //שערי חליפין

LOAD

CURRENCY AS CUREG_CURRENCY,

EXCHANGE AS CURREG_EXCHANGE,

CURDATE AS CURREG_CURDATE

FROM [lib://CLINTON_SAMPLE/CurReg.qvd]

(qvd);

ACCDATES:

LOAD

CURDATE AS ACC_CURDATE,

COSTFLAG AS ACC_COSTFLAG,

COSTDATE AS ACC_COSTDATE

FROM [lib://CLINTON_SAMPLE/Accdates.qvd]

(qvd);

this is the second part:

Inv_Sales_temp: //טבלת מכירות כללית

LOAD

Date (IVDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

IVNUM AS INV_INVNUM,

DOC AS INV_DOC,

IV,

QPRICE AS INV_QPRICE,

TOTPRICE AS INV_TOTPRICE,

"CURRENCY" AS INV_CURRENCY,

CUST AS INV_CUST,

DEBIT,

VAT  AS INV_VAT,

UDATE AS INV_UDATE, // "T$USER",

PRINTED AS INV_PRINTED ,

FINAL AS INV_FINAL,

ORD AS INV_ORD ,

DISCOUNT,

DISPRICE AS INV_DISCOUNT,

PAYDATE AS INV_PAYDATE,

FNCTRANS AS INV_FNCTRANS,

BOOKNUM AS INV_BOOKNUM,

AGENT AS INV_AGENT,

TYPE AS INV_TYPE,

CHECKING AS INV_CHECKING,

IMPFILE AS INV_IMPFILE,

STORNOFLAG AS INV_STORNOFLAG,

BRANCH AS INV_BRANCH

FROM [lib://CLINTON_SAMPLE/Invoices.qvd]

(qvd);

//פרטי ת.יומן   

Left join

LOAD

FNCTRANS AS INV_FNCTRANS,

FNCNUM,

FINAL as FNC_FINAL,

CURDATE AS FNC_CURDATE,

FNCDATE,

EXCHANGE3 AS FNC_EXCHANGE3,

CURRENCY3 AS FNC_CURRENCY3,

IVNUM AS FNC_IVNUM,

EXCHANGE2 AS FNC_EXCHANGE2

FROM [lib://CLINTON_SAMPLE/Fnctrans.qvd]

(qvd)

Where FINAL ='Y'

;

Left Join (Inv_Sales_temp)

LOAD

IV,

PART AS IV_PART,

PRICE AS IV_PRICE,

QPRICE AS IV_QPRICE,

QUANT/1000 AS IV_QUANT,

"CURRENCY" AS IV_CURRENCY,

LINE AS IV_LINE,

ORDI AS IV_ORDI,

UDATE AS IV_UDATE,

KLINE AS IV_KLINE,

TQUANT/1000 AS IV_TQUANT,

TUNIT AS TUNIT,

TRANS AS IV_TRANS,

TOTPERCENT AS IV_TOTPERCENT,

(IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,

TYPE AS IV_TYPE,

//PRSOURCE,

//STORNOFLAG,

COSTC AS IV_COSTC,

CREDITCOST AS IV_CREDITCOST,

//ACCOUNT,

BRANCH AS IV_BRANCH,

IVDATE AS IV_IVDATE,

ICURRENCY AS IV_ICURRENCY,

IEXCHANGE AS IV_IEXCHANGE,

//COUNTRY,

CREDITFLAG AS IV_CREDITFLAG,

AGENT AS IV_AGENT,

COMMISSION AS IV_COMMISSION,

DUTYCOST AS IV_DUTYCOST     

FROM [lib://CLINTON_SAMPLE/InvoiceItems.qvd]

(qvd);

//נתונים נוספים לשורת חשבונית

Left Join

LOAD IV,

KLINE AS IV_KLINE,

COSTC2 AS IVA_COSTSC2,

COSTC3 AS IVA_COSTC3,

COSTC4 AS IVA_COSTC4,

COSTC5 AS IVA_COSTC5,

TOTPRICE AS IVA_TOTPRICE,

IVTAX AS IVA_IVTAX,

EXTCOST AS IVA_EXTCOST,

EXTCOST2 AS IVA_EXTCOST2,

BONUSFLAG AS IVA_BONUSFLAG

FROM [lib://CLINTON_SAMPLE/InvoiceItemsa.qvd]

(qvd);

// נתונים מכרטיס לקוח

left Join (Inv_Sales_temp)

LOAD

CUST_CUST,

CUST_CUSTNAME AS INV_CUSTNAME,

CUST_CUSTDES AS INV_CUSTDES

// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

// CUST_AGENTNAME

RESIDENT

CUSTOMERS;

//נתונים מכרטיס הלקוח

/*

left Join (Inv_Sales_temp)

LOAD

CUST_CUST AS INV_CUST,

CUST_CUSTNAME AS INV_CUSTNAME,

CUST_CUSTDES AS INV_CUSTDES

Resident Custs;*/

//נתונים מכרטיס הפריט משורשרים לטבלת המכירות

left Join (Inv_Sales_temp)

LOAD

PARTNAME AS IV_PARTNAME,

PART_PART AS IV_PART,

PART_PRICE AS IV_PART_PRICE,

PART_SECONDPRICE AS IV_PART_SECONDPRICE,

PART_LASTPRICE AS IV_PARTLASTPRICE,

PART_LASTCURRENCY AS IV_PART_LASTCURRENCY, 

PART_COSTQUANT,

PART_COST as IV_PART_COST,

PART_SECONDCOST

// PART_MGCAT as IV_PART_MGCAT,

// PART_MGDEP as IV_PART_MGDEP

Resident Parts;

//נתונים משורות התנועה המקושרת לחשבונית- עלות וכדומה

left Join (Inv_Sales_temp)

LOAD

(IF(ISNULL(TRANS)=-1,0, TRANS)) AS IV_TRANS,

DOC as TRANS_DOC,

PART AS TRANS_PART,

QUANT/1000 AS TRANS_QUANT,

ORDI AS TRANS_ORDI,

CUST AS TRANS_CUST,

PRICE AS TRANS_PRICE,

IV,

COST AS TRANS_COST,

SECONDCOST AS TRANS_SECONDCOST,

BRANCH AS TRANS_BRANCH,

CURRENCY AS TRANS_CURRENCY,

COGFLAG AS TRANS_COSTFLAG,

CURDATE AS TRANS_CURDATE

FROM [lib://CLINTON_SAMPLE/Trans.qvd]

(qvd);

//שער מטבע לתאריך החשבונית ולמטבע השורה

Left JOIN (INV_SALES_TEMP)

LOAD  

CUR_CURRENCY AS INV_CURRENCY,

CUR_EXCHANGE

Resident Currencies;

LEFT JOIN (INV_SALES_TEMP)

LOAD

Date (CURREG_CURDATE/60/24+32143,'DD/MM/YYYY')  AS INV_IVDATE,

CURREG_CURRENCY  AS INV_CURRENCY,

CURREG_EXCHANGE

Resident Curreg;

left JOIN (INV_SALES_TEMP)

LOAD 

CUR_EXCHANGE AS CUR_EXCHANGE2

Resident Currencies Where CUR_CURRENCY = -2;

LEFT JOIN (INV_SALES_TEMP)

LOAD

Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,

CURREG_EXCHANGE AS CURREG_EXCHANGE2

Resident Curreg Where CURREG_CURRENCY = -2;

// POSSIBLE EURO COIN

Left JOIN (INV_SALES_TEMP)

LOAD

CUR_EXCHANGE AS CUR_EXCHANGE3

Resident Currencies

Where CUR_CURRENCY =$(vForeignCurrency);

// שיניתי כי לא עבד בפעם הראשונה לא זהה את השדה

// POSSIBLE EURO COIN

left JOIN (INV_SALES_TEMP)

LOAD

Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY')AS INV_IVDATE ,

CURREG_EXCHANGE AS CURREG_EXCHANGE3

Resident Curreg

Where CURREG_CURRENCY =$(vForeignCurrency);

// LINE 183 IN THE SALES CHART//

LEFT JOIN(INV_SALES_TEMP)

LOAD

ACC_COSTFLAG,

DATE((NUM(MAX(ACC_COSTDATE)))/60/24+32143,'DD/MM/YYYY') AS MAXACC_COSTDATE

RESIDENT ACCDATES

WHERE ACC_COSTDATE<>0

GROUP BY ACC_COSTFLAG;

DROP TABLE ACCDATES;