No data returned from newly created QVD?

[#1]

Hi All,

I'm a newbee to QV, hope you can help me.

I have the following problem, I´m trying to create an incremental load and I´m using two helper variables v_MaxIV  and v_MinIV to store invoicenumbers. I have a problem when the QVD is new, that is when I store it and 3 seconds later want to query it.

Chances are my strategy is totally wrong thought..

IF isnull(filetime('DebInvJour.qvd')) then
//file doesnt exists --> FETCH ODBC
CALL FetchXALODBCDebInvJourFromDate
CALL StoreDebInvJour
CALL QueryInvoiceNumbers

END IF

FETCHXALODBCDebInvJourFromDate fetches all records:

SUB FetchXALODBCDebInvJourFromDate
DebInvJour:
QUALIFY *;
ODBC CONNECT TO XAL (XUserId is ZReBCZVMBDZeGXRNZLMB, XPassword is WCEHEZVMBDZeGXRNZbBB);
SQL SELECT DATASET,
TRIM(INVOICEACCOUNT) AS INVOICEACCOUNT,
TRIM(ORDERACCOUNT) AS ORDERACCOUNT,
SALESBALANCE,
TRIM(INVOICENUMBER) AS INVOICENUMBER,
INVOICEDATE
FROM "XAL_SUPERVISOR".DEBINVJOUR WHERE
INVOICEDATE >= TO_DATE('01/10/2013','DD/MM/YYYY') AND DATASET = 'FIK';
END SUB

StoreDebInvJour nicely stores the QVD

SUB StoreDebInvJour
//Als we geen script errors hebben slaan we de data op naar DebInvJour
If ScriptErrorCount = 0 then
STORE DebInvJour INTO [DebInvJour.qvd] (QVD);
SLEEP 1000;
End If
END Sub

But the QueryInvoiceNumbers returns a null for MaxInvNumber and MInInvNumber???

SUB QueryInvoiceNumbers
/Fetch last invoicenumber from QVD
DebInvJour:
load
max(DebInvJour.INVOICENUMBER) as MaxInvNumber,
min(DebInvJour.INVOICENUMBER) as MinInvNumber
FROM
[DebInvJour.qvd]
(qvd)
WHERE DebInvJour.DATASET = 'FIK';
let v_MaxIV = peek('MaxInvNumber', -1);
let v_MinIV = peek('MinInvNumber', -1);
;
END SUB

If I run the QueryInvoiceNumber method when the QVD already exists it returns min and max as expected??

What am I doing wrong here?

Thanks in advance!

Mike

[tresesco]

A quick look says that the following part of your code is wrong.

SQL SELECT DATASET,

TRIM(INVOICEACCOUNT) AS INVOICEACCOUNT,

TRIM(ORDERACCOUNT) AS ORDERACCOUNT,

You can't use QV functions/features under SQL query part.

[#3]

Hi Tresesco,

Thanks for your reply

'AS INVOICEACCOUNT' isn't part of the problem. the query works fine.

Regards,

Mike

[mr_janne · Accepted solution]

Hi, maybe because you don't disable the Qualify parameter? It still is QUALIFY * at the end of script? You should unqualify it at some point of the script.

//Maybe something like this:

SUB QueryInvoiceNumbers
/Fetch last invoicenumber from QVD

unqualify *;
DebInvJourMinMax:
load
max(DebInvJour.INVOICENUMBER) as MaxInvNumber,
min(DebInvJour.INVOICENUMBER) as MinInvNumber
FROM
[DebInvJour.qvd]
(qvd)
WHERE DebInvJour.DATASET = 'FIK';
let v_MaxIV = Peek(''MaxInvNumber'',0,'DebInvJourMinMax');
let v_MinIV = Peek(''MinInvNumber'',0,'DebInvJourMinMax');
END SUB

[#5]

Mr Janne you're a lifesaver!!

Thanks, works!

Kind regards,

Mike