Question Manage output for dynamic query/browse when using OUTER-JOIN

Hi everyon,

While making improvement in my understanding of dynamic query I encounter a new unknown;
In fact in my dynamic Query bellow I use an outer-join on two tables : VAFDAI & VFGDAI:
Code:
CREATE QUERY dispQuery.
    
dispQuery:SET-BUFFERS(BUFFER FGSERV:HANDLE,BUFFER SACDPE:HANDLE,BUFFER SDTFOE:HANDLE,BUFFER VAFDAI:HANDLE,BUFFER VFGDAI:HANDLE) .
dispQuery:QUERY-PREPARE("FOR EACH FGSERV WHERE FGSERV.fgsign = 'user' NO-LOCK," +
                            "EACH SACDPE WHERE SACDPE.stecod = '" + c-stecod + "' " +
                            "            AND   SACDPE.svccod = FGSERV.fgserv" +
                            "            AND   SACDPE.dpeutidem MATCHES '" + fiUtidem + "' " +
                 (IF fiDatdeb <> ? THEN "AND   SACDPE.dpedatdem >= " + STRING(fiDatdeb) + " " ELSE "") +
                 (IF fiDatfin <> ? THEN "AND   SACDPE.dpedatdem <= " + STRING(fiDatfin) + " " ELSE "") +
                            "            AND   SACDPE.foucodcde MATCHES '" + fiFoucod + "' NO-LOCK," +
                            "EACH SDTFOE WHERE SDTFOE.foucod = SACDPE.foucodcde NO-LOCK," +
                            "EACH VAFDAI OUTER-JOIN WHERE VAFDAI.danum = SACDPE.acddemnum NO-LOCK," +
                            "EACH VFGDAI OUTER-JOIN WHERE VFGDAI.dainum = VAFDAI.dainum NO-LOCK" )
So when the VAFDAI as no value to match with the SACDPE.acddemnum field I obtain a defautl value in the column "?" .
I would like to be able to prompt a blank like "" if possible.

I add the column like this:
Code:
DEFINE VARIABLE myCol AS HANDLE      NO-UNDO.

ASSIGN
myBr = BROWSE-5:HANDLE IN FRAME F-Main
myBr:QUERY = dispQuery
myCol = myBr:ADD-LIKE-COLUMN("FGSERV.fgserv":U)
myCol:LABEL = "Section"
myCol:WIDTH-PIXELS = func-WidthColumn(myCol) + 30
myCol = myBr:ADD-LIKE-COLUMN("SACDPE.acddemnum":U)
myCol:LABEL = "Num DA"
myCol:WIDTH-PIXELS = func-WidthColumn(myCol) + 30
myCol = myBr:ADD-LIKE-COLUMN("SACDPE.dpedatdem")
myCol:LABEL = "Date DA":U
myCol:WIDTH-PIXELS = func-WidthColumn(myCol) + 30
myCol = myBr:ADD-LIKE-COLUMN("SACDPE.dpeutidem")
myCol:LABEL = "Demandeur":U
myCol:WIDTH-PIXELS = func-WidthColumn(myCol) + 30
myCol = myBr:ADD-LIKE-COLUMN("VAFDAI.dainum")
myCol:LABEL = "DAI"
myCol:WIDTH-PIXELS = func-WidthColumn(myCol) + 30
myCol = myBr:ADD-LIKE-COLUMN("VFGDAI.dailib1")
myCol:LABEL = "Lib. DAI"
myCol:WIDTH-PIXELS = func-WidthColumn(myCol) + 30 .

Also I will maybe want to add some column calculate from a function .
Is there a way to do it ?

Or will I be obliged to used static query & browse ?

Best Regards,

- BonyIsProgress -
 

Osborne

Active Member
Also I will maybe want to add some column calculate from a function .
Is there a way to do it ?
To add a calculated column you do something like this:
Code:
hCalcCol = myBr:ADD-CALC-COLUMN("CHAR",       /* Data type */
                                "X(15)",      /* Format */
                                "",           /* Initial value */
                                "Calc Column"). /* column label */
Then as per Stefan's solution, assign the value in the row display trigger:
Code:
IF VALID-HANDLE(hCalcCol)THEN
   hCalcCol:SCREEN-VALUE = func-CalcColValue(hField2:BUFFER-VALUE,hField1:BUFFER-VALUE).
 
Hi,

Thank you both @Stefan & @Osborne for your help.
It work perfectly. The only issue I still have is that each time i update on of the "fi" variable by a value-changed trigger the browse blink. So not so good at the eye .

Best Regards,

- BobyIsProgress -
 
Top