customfields_ne.sql

SELECT  
    "chs_coresearch"."listingkey",
    "chs_coresearch"."modificationtimestamp",
    "chs_coresearch"."sparkmodificationtimestamp" 
FROM "chs_coresearch"  
WHERE 
(
    "chs_coresearch"."listingkey" in 
    (
        select distinct "chs_coresearch"."listingkey" from chs_coresearch 
        LEFT OUTER JOIN "chs_customfieldsearch" chs_customfieldsearch_1
        ON "chs_customfieldsearch_1"."listingkey"="chs_coresearch"."listingkey" AND "chs_customfieldsearch_1"."groupname" = 'Status Change Info' AND "chs_customfieldsearch_1"."fieldname" = 'Sold Only'
        WHERE 
        (
            ("chs_coresearch"."privatesearchable" = true) 
            AND "chs_coresearch"."standardstatus" IN ('Active','Active Under Contract','Canceled','Closed','Coming Soon','Expired','Pending','Withdrawn') 
            AND "chs_coresearch"."approvalstatus" = true
        ) 
        AND 
        (
            "chs_coresearch"."listingid" in ('16004362','15013222') 
            AND 
            (
                upper("chs_customfieldsearch_1"."charactervalue") <> 'YES'
                OR 
                "chs_customfieldsearch_1"."charactervalue" IS NULL
            )
        )
    )
) 
ORDER BY "chs_coresearch"."modificationtimestamp" DESC, "chs_coresearch"."listingkey" LIMIT 40 OFFSET 0

therealtest.sql

SELECT  
    "chs_coresearch"."listingkey",
    "chs_coresearch"."modificationtimestamp",
    "chs_coresearch"."sparkmodificationtimestamp" 
FROM "chs_coresearch"  
WHERE 
(
    "chs_coresearch"."listingkey" in 
    (
        select distinct "chs_coresearch"."listingkey" from chs_coresearch 
        LEFT OUTER JOIN "chs_customfieldsearch" chs_customfieldsearch_1
        ON "chs_customfieldsearch_1"."listingkey"="chs_coresearch"."listingkey" AND "chs_customfieldsearch_1"."groupname" = 'Status Change Info' AND "chs_customfieldsearch_1"."fieldname" = 'Sold Only'
        WHERE 
        (
            ("chs_coresearch"."privatesearchable" = true) 
            AND "chs_coresearch"."standardstatus" IN ('Active','Active Under Contract','Canceled','Closed','Coming Soon','Expired','Pending','Withdrawn') 
            AND "chs_coresearch"."approvalstatus" = true
        ) 
        AND 
        (
            (
                upper("chs_customfieldsearch_1"."charactervalue") <> 'YES'
                OR 
                "chs_customfieldsearch_1"."charactervalue" IS NULL
            )
        )
    )                                                                                                   
) 
ORDER BY "chs_coresearch"."modificationtimestamp" DESC, "chs_coresearch"."listingkey" LIMIT 40 OFFSET 0;


SELECT  
    "chs_coresearch"."listingkey",
    "chs_coresearch"."modificationtimestamp",
    "chs_coresearch"."sparkmodificationtimestamp" 
FROM "chs_coresearch"  
WHERE 
(
    "chs_coresearch"."listingkey" in 
    (
        select distinct "chs_coresearch"."listingkey" from chs_coresearch 
        LEFT OUTER JOIN "chs_customfieldsearch" chs_customfieldsearch_1
        ON "chs_customfieldsearch_1"."listingkey"="chs_coresearch"."listingkey" AND "chs_customfieldsearch_1"."groupname" = 'Status Change Info' AND "chs_customfieldsearch_1"."fieldname" = 'Sold Only'
        WHERE 
        (
            ("chs_coresearch"."privatesearchable" = true) 
            AND "chs_coresearch"."standardstatus" IN ('Active','Active Under Contract','Canceled','Closed','Coming Soon','Expired','Pending','Withdrawn') 
            AND "chs_coresearch"."approvalstatus" = true
        ) 
        AND 
        (
            (
                upper("chs_customfieldsearch_1"."charactervalue") = 'YES'
            )
        )
    )                                                                                                   
) 
ORDER BY "chs_coresearch"."modificationtimestamp" DESC, "chs_coresearch"."listingkey" LIMIT 40 OFFSET 0;


SELECT  
    "chs_coresearch"."listingkey",
    "chs_coresearch"."modificationtimestamp",
    "chs_coresearch"."sparkmodificationtimestamp" 
FROM "chs_coresearch"  
WHERE 
(
    "chs_coresearch"."listingkey" in 
    (
        select distinct "chs_coresearch"."listingkey" from chs_coresearch 
        LEFT OUTER JOIN "chs_customfieldsearch" chs_customfieldsearch_1 
        ON "chs_customfieldsearch_1"."listingkey"="chs_coresearch"."listingkey" 
        WHERE 
        (
            ("chs_coresearch"."privatesearchable" = true) 
            AND "chs_coresearch"."standardstatus" IN ('Active','Active Under Contract','Canceled','Closed','Coming Soon','Expired','Pending','Withdrawn') 
            AND "chs_coresearch"."approvalstatus" = true
        ) 
        AND 
        (
            (
                (
                    "chs_customfieldsearch_1"."groupname" = 'Status Change Info' 
                    AND "chs_customfieldsearch_1"."fieldname" = 'Sold Only' 
                    AND upper("chs_customfieldsearch_1"."charactervalue") = 'YES'
                ) 
            )
        )
    )
) 
ORDER BY "chs_coresearch"."modificationtimestamp" DESC, "chs_coresearch"."listingkey" LIMIT 40 OFFSET 0