Hello to everybody, i have a question, i have this query:
DEFINE INPUT PARAMETER ipdtDataComm AS DATE NO-UNDO.
DEFINE INPUT PARAMETER iConvNum AS INTEGER NO-UNDO.
DEFINE INPUT PARAMETER ipdtDataDal AS DATE NO-UNDO.
DEFINE INPUT PARAMETER ipdtDataAl AS DATE NO-UNDO.
DEFINE OUTPUT PARAMETER TABLE FOR ttEspDati.
DEFINE VARIABLE iCounter AS INTEGER NO-UNDO.
Main:
DO:
PUBLISH "messaggio" (PROGRAM-NAME(1), 0, "ipdtDataComm = " + STRING(ipdtDataComm)).
PUBLISH "messaggio" (PROGRAM-NAME(1), 0, "iConvNum = " + STRING(iConvNum)).
PUBLISH "messaggio" (PROGRAM-NAME(1), 0, "ipdtDataDal = " + STRING(ipdtDataDal)).
PUBLISH "messaggio" (PROGRAM-NAME(1), 0, "ipdtDataAl = " + STRING(ipdtDataAl)).
DEFINE QUERY qQuery FOR
AvvLav0F,
AnCli060F,
AnCli040F,
AnCli00F,
AnaAz00F,
AnaAz20F,
AnaOp00F,
TbGestPro SCROLLING.
OPEN QUERY qQUERY FOR EACH sociale.AvvLav0F WHERE /* AvvLav0F.ConvN = iConvNum
AND */ AvvLav0F.DtComm = ipdtDataComm NO-LOCK,
EACH sociale.AnCli060F OF sociale.AvvLav0F NO-LOCK,
EACH sociale.AnCli040F OF sociale.AnCli060F NO-LOCK,
EACH sociale.AnCli00F OF sociale.AnCli060F NO-LOCK,
EACH sociale.AnaAz00F OF sociale.AvvLav0F NO-LOCK,
EACH sociale.AnaAz20F OF sociale.AvvLav0F OUTER-JOIN NO-LOCK,
EACH sociale.AnaOp00F OF sociale.AvvLav0F OUTER-JOIN NO-LOCK,
EACH sociale.TbGestPro OF sociale.AvvLav0F OUTER-JOIN NO-LOCK
BY AvvLav0F.Anno
BY AvvLav0F.ConvN INDEXED-REPOSITION.
REPEAT:
GET NEXT qQuery. /*
IF NOT AVAILABLE (AnaAz00F) THEN LEAVE.
*/
IF QUERY-OFF-END ('qQUERY') THEN LEAVE.
CREATE ttEspDati.
ASSIGN ttEspDati.CodCli = AvvLav0F.CodCli.
iCounter = iCounter + 1.
END.
PUBLISH "messaggio" (PROGRAM-NAME(1), 0, "iCounter = " + STRING(iCounter)).
END.
END PROCEDURE.
but i need to change Query on table AvvLav0F, because i have to insert a variable numbers of parameters for query, for example one time i have to search using ConvN, and the other one DtComm, together or none. How can i do this? I read on documentation, and i read Dynamic Query can do this, but i never used them. Can i do this changing this Query? If i have to use Dynamic Query, do i need buffer?
Best regards.
You could use a CASE statement with different open query statements depending on value(s), but this will result in larger procedure
Yes, i agree, because i have 4 parameter and i will have 16 cases, do you think there is not other solution? Only Dynamic Query?
hahaha I am now stuck trying to use different methods to resolve this. Would have been nice if you could use a Variable in where clause :-D
Yes, i know, but can i use a variable in a where clause?
You can build a dynamic query...
Ciao,
You can do it using dynamic query as you've already read in the documentation.
There is an example in online help for CREATE QUERY statement .
In your case it would be something along these lines:
/* you input parameters go in here... */
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE myQueryPhrase AS CHARACTER NO-UNDO.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(BUFFER AvvLav0F:HANDLE, BUFFER AnCli060F:HANDLE, BUFFER AnCli040F:HANDLE /* Add other buffers here ...*/ ).
/* build your query phrase as an character string */
myQueryPhrase = " WHERE AvvLav0F.ConvN = iConvNum AND AvvLav0F.DtComm = ipdtDataComm " .
myQueryPhrase = myQueryPhrase + "EACH sociale.AnCli060F OF sociale.AvvLav0F" . /* Add whatever else you need */
hQuery:QUERY-PREPARE("FOR EACH AvvLav0F " + MyQueryPhrase) .
hQuery:QUERY-OPEN.
/* Do the rest of the code here */
NB: You can use ADD-BUFFER( ) method to add one buffer to the object, without affecting the other buffers. Maximum number of buffers is 18.
To minimize code changes:
It's also possible to dynamically (re-)open an existing static query. Doing that means you only need to replace the OPEN QUERY statements with QUERY-PREPARE() and QUERY-OPEN() method calls, ie.
...
DEFINE QUERY qQuery FOR
AvvLav0F,
AnCli060F,
AnCli040F,
AnCli00F,
AnaAz00F,
AnaAz20F,
AnaOp00F,
TbGestPro SCROLLING.
/* build myQueryPhrase string here as needed */
myQueryPhrase = "FOR EACH sociale.AvvLav0F WHERE AvvLav0F.DtComm = " + quoter(ipdtDataComm),
myQueryPhrase = myQueryPhrase + "EACH sociale.AnCli060F OF sociale.AvvLav0F" . /* Add whatever else you need */
QUERY qQuery:QUERY-PREPARE(MyQueryPhrase) .
QUERY qQuery:QUERY-OPEN().
REPEAT:
GET NEXT qQuery. /*
IF NOT AVAILABLE (AnaAz00F) THEN LEAVE.
...
I've seen a few other techniques, but if you need to change which fields you're selecting on this tends to be the better approach. It's the easiest to maintain (WHERE clause per buffer needs to be maintained in only one point in the code) and usually provides best performance because the AVM can usually pick the best indexes at runtime.
unfortunately does not seem like you can use a variable, will have to look at doing everything with Dynamic Queries.
The supporting docs are not too bad and it is not so hard so give it a bash :-)
Thank you to everybody :) i wrote a solution, it is a mix of Frank and Sasha answers :-) i think i Dynamic Query is a very flexible and helpful instrument, and i will study your answer, to use Dynamic Queries more often.
Have i to close post now?