Sub-query Problem

Hi,

I am not able to compile a query in windev which runs fine in SqlServer.

select temp.name from
(Select a.name , a.class from table1 as a, table2 as b where a.id=b.id) as temp, table3 as c
where temp.name = c.name

If such query can not be used in windev then what is the solution for this.


Regards,
Prolay

Prolay,
I'm using this kind of querys, against sql-server, without problems.
Could you post the error as well?
Which WD-version do you use?

Arie

Hi,

Double click the query in the query editor to open the properties window and flag 'without hfcorrection on test mode'. This should allow the query to work in test mode. In code, use hexecutequery(QryName, ConnectionName, hdefault+hquerywithoutcorrection). For ConnectionName you can use MyConnection..Caption if you have your connection defined as a global connection variable or singleton class member. E.g.:
//E.g. A - Global connection parameter
//Project initialisation
MyConnection is Connection

//E.g. B - Global singleton class member
//In project initialisation
MyApplication is objMyApplication
//Class definition
objMyApplication is Class
GLOBAL PUBLIC
MyConnection is Connection)

I have for example such a complex (subselect) query against a SQLServer using oledb which runs fine. The example is below. As you can see you can easily parse any parameter or variable query parts using the curly brackets.

SELECT
    F0411_MAIN.RPKCO AS MainCompany,
    F0411_MAIN.RPMCU AS MainBusinessUnit,
    F0006.MCDL01 AS MainBusinessUnitDesc,
    F0411_MAIN.RPAN8 AS MainSupplierNr,
    F0101.ABALPH AS MainSupplierName,
    F0411_MAIN.RPDOC AS MainDocNr,
    F0411_MAIN.RPDCT AS MainDocType,
    F0411_MAIN.RPVINV AS MainVendorDocNr,
    F0411_MAIN.RPDIVJ AS MainInvoiceDate,
    F0411_MAIN.RPDGJ AS MainGLDate,
    F0411_MAIN.RPDDJ AS MainDueDate,
    -- If it has been voided before the requested AS-IF date or the GL date is passed the requested AS-if date then result 0 else sum total open amount for voucher
    SUM(CASE WHEN VoidDate <= {pAsIfDateJulian} OR F0411_MAIN.RPDGJ > {pAsIfDateJulian} OR F0411_MAIN.RPDCTA = 'PE' THEN 0 ELSE ISNULL(VoidAmtGross*-1,F0411_MAIN.RPAAP)-ISNULL(TotalAdjAmtAfterAsOf,0)END) AS MainAmtOpen
FROM F0411 AS F0411_MAIN
LEFT JOIN
    (SELECT F0411_FLTR.RPKCO AS FltrCompany, 
            F0411_FLTR.RPMCU AS FltrBusinessUnit, 
            F0411_FLTR.RPAN8 AS FltrSupplierNr, 
            F0411_FLTR.RPDOC AS FltrDocNr, 
            F0411_FLTR.RPDCT AS FltrDocType, 
            F0411_FLTR.RPSFX AS FltrDocSuffix,
            F0411_FLTR.RPPST AS FltrDocPayStatus, 
            F0411_VOID.RPAG AS VoidAmtGross,
            F0411_VOID.RPACR AS VoidAmtGrossForeign,
            F0411_VOID.RPDGJ AS VoidDate
    FROM F0411 AS F0411_FLTR
    INNER JOIN F0411 AS F0411_VOID 
    ON     F0411_FLTR.RPDOC = F0411_VOID.RPDOC AND
        F0411_FLTR.RPKCO = F0411_VOID.RPKCO AND
        F0411_FLTR.RPDCT = F0411_VOID.RPDCT AND
        F0411_FLTR.RPSFX = F0411_VOID.RPSFX
    WHERE
        F0411_VOID.RPDCTA = 'PE' AND
        F0411_FLTR.RPKCO = {pKCOCompany} AND
        F0411_FLTR.RPMCU = {pMCUBusinessUnit} AND
        F0411_FLTR.RPAN8 = {pAN8SupplierNr} AND
        F0411_FLTR.RPPST IN ({pPSTPayStatusList})
    )
    AS F0411_SBVD
    ON    F0411_MAIN.RPDOC = F0411_SBVD.FltrDocNr AND
        F0411_MAIN.RPDCT = F0411_SBVD.FltrDocType AND
        F0411_MAIN.RPKCO = F0411_SBVD.FltrCompany AND
        F0411_MAIN.RPSFX = F0411_SBVD.FltrDocSuffix
LEFT JOIN
    (
    SELECT     F0411_FLTR.RPKCO AS SubRPKCO,
            F0411_FLTR.RPMCU AS SubRPMCU,
            F0411_FLTR.RPAN8 AS SubRPAN8,
            F0411_FLTR.RPDOC AS SubRPDOC,
            F0411_FLTR.RPDCT AS SubRPDCT,
            F0411_FLTR.RPSFX AS SubRPSFX,
            F0411_FLTR.RPSFXE AS SubRPSFXE,
            SUM(F0414.RNPAAP+F0414.RNADSA) AS TotalAdjAmtAfterAsOf
    FROM F0411 AS F0411_FLTR
    INNER JOIN F0414
    INNER JOIN F0413
    ON    F0414.RNPYID = F0413.RMPYID
    ON     F0411_FLTR.RPKCO = F0414.RNKCO AND
        F0411_FLTR.RPDOC = F0414.RNDOC AND
        F0411_FLTR.RPDCT = F0414.RNDCT AND
        F0411_FLTR.RPSFX = F0414.RNSFX AND
        F0411_FLTR.RPSFXE = F0414.RNSFXE
    WHERE
        ((F0414.RNDCTM = 'PO' AND
        F0413.RMVDGJ > {pAsIfDateJulian}) OR 
        F0413.RMDMTJ > {pAsIfDateJulian}) AND
        F0414.RNDCTM <> 'PG' AND
        F0411_FLTR.RPKCO = {pKCOCompany} AND
        F0411_FLTR.RPMCU = {pMCUBusinessUnit} AND
        F0411_FLTR.RPAN8 = {pAN8SupplierNr} AND
        F0411_FLTR.RPPST IN ({pPSTPayStatusList})
    GROUP BY F0411_FLTR.RPKCO, F0411_FLTR.RPMCU, F0411_FLTR.RPAN8, F0411_FLTR.RPDOC, F0411_FLTR.RPDCT, F0411_FLTR.RPSFX, F0411_FLTR.RPSFXE
    )
    AS SUBSELECT 
    ON    F0411_MAIN.RPKCO = SubRPKCO AND
        F0411_MAIN.RPDOC = SubRPDOC AND
        F0411_MAIN.RPDCT = SubRPDCT AND
        F0411_MAIN.RPSFX = SubRPSFX AND
        F0411_MAIN.RPSFXE = SubRPSFXE
INNER JOIN F0006
ON F0411_MAIN.RPMCU = F0006.MCMCU
INNER JOIN F0101
ON F0411_MAIN.RPAN8 = F0101.ABAN8
GROUP BY F0411_MAIN.RPKCO, F0411_MAIN.RPMCU, F0006.MCDL01, F0411_MAIN.RPAN8, F0101.ABALPH, F0411_MAIN.RPDOC, F0411_MAIN.RPDCT ,F0411_MAIN.RPVINV ,F0411_MAIN.RPDIVJ ,F0411_MAIN.RPDGJ ,F0411_MAIN.RPDDJ
HAVING SUM(CASE WHEN VoidDate <= {pAsIfDateJulian} OR F0411_MAIN.RPDGJ > {pAsIfDateJulian} OR F0411_MAIN.RPDCTA = 'PE' THEN 0 ELSE ISNULL(VoidAmtGross*-1,F0411_MAIN.RPAAP)-ISNULL(TotalAdjAmtAfterAsOf,0)END) <> 0
ORDER BY F0411_MAIN.RPKCO ASC, F0411_MAIN.RPAN8 ASC, F0411_MAIN.RPDCT ASC, F0411_MAIN.RPDDJ ASC

Cheers,

Hi,

Thanks all for your response. And the Query can be referenced for all complex queries. Thanks again!!

Regards,
Prolay