Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hi Tresesco,
Thanks for your reply
'AS INVOICEACCOUNT' isn't part of the problem. the query works fine.
Regards,
Mike
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
Mr Janne you're a lifesaver!!
Thanks, works!
Kind regards,
Mike