troubleshooting Question

Oracle - Query with wrong count decode

Avatar of Wilder1626
Wilder1626Flag for Canada asked on
Oracle DatabaseSQL
16 Comments2 Solutions605 ViewsLast Modified:
Hello all

Have have this query bellow that count 3 columns separatly the number of time the word 'NO' show.

The thing is that the count does not give me the good count.

I need to count the number of NO in column:
SCNALIAS_SET
VENDOR_MAINTENANCE_MATCH
VENDOR_RATE_SET

Do you see something wrong in the query?

Thanks again for your help

query result

Query:
SELECT ID,
    SCNALIAS,
    SCNALIAS_SET,
   VENDOR_MAINTENANCE_MATCH,
    VENDOR_RATE_SET,
    ISSUES,
    DECODE(ROWNUM, 1, TOTAL_SCNALIAS_NOT_SET) TOTAL_SCNALIAS_NOT_SET,
    DECODE(ROWNUM, 1, TOTAL_VENDORT_MAINT_NOT_SET) TOTAL_VENDORT_MAINT_NOT_SET,
    DECODE(ROWNUM, 1, TOTAL_VENDOR_RATE_NOT_SET) TOTAL_VENDOR_RATE_NOT_SET
FROM (
    SELECT ID,
        SCNALIAS,
        SCNALIAS_SET,
        VENDOR_MAINTENANCE_MATCH,
        VENDOR_RATE_SET,
        DECODE(SCNALIAS_SET, 'NO', 1, 0) + DECODE(VENDOR_MAINTENANCE_MATCH, 'NO', 1, 0) + DECODE(VENDOR_RATE_SET, 'NO', 1, 0) ISSUES,
        COUNT(DECODE(SCNALIAS_SET, 'NO', 1)) OVER () TOTAL_SCNALIAS_NOT_SET,
        COUNT(DECODE(VENDOR_MAINTENANCE_MATCH, 'NO', 1)) OVER () TOTAL_VENDORT_MAINT_NOT_SET,
        COUNT(DECODE(VENDOR_RATE_SET, 'NO', 1)) OVER () TOTAL_VENDOR_RATE_NOT_SET
    FROM (
        SELECT DISTINCT LOCATION.ID,
            LOCATION.SCNALIAS,
            CASE 
                WHEN LOCATION.SCNALIAS IS NOT NULL
                    THEN '-'
                ELSE 'NO'
                END SCNALIAS_SET,
            NVL((
                    SELECT '-'
                    FROM TMMGR.FP_VENDOR_INFO
                    WHERE LOCATION.SCNALIAS = FP_VENDOR_INFO.VENDOR_NBR
                        AND ROWNUM = 1
                    ), 'NO') VENDOR_MAINTENANCE_MATCH,
            NVL((
                    SELECT '-'
                    FROM TL_RATE TLR,
                        TL_RATE_DETAIL TRD,
                        LANE_RATE_NETWORK LRN
                    WHERE TLR.CARRIER_ID = TRD.CARRIER_ID(+)
                        AND TLR.LANE_ID = TRD.LANE_ID(+)
                        AND TLR.TARIFF_CLASS_ID = TRD.TARIFF_CLASS_ID(+)
                        AND TLR.EFFECTIVE = TRD.EFFECTIVE(+)
                        AND TLR.LANE_ID = LRN.ID
                        AND TLR.CARRIER_ID = 'CUSTOMER'
                        AND LRN.FROM_KEY_NUM = 6
                        AND LRN.FROM_LOW_KEY_VALUE(+) = LOCATION.ID
                        AND TLR.EXPIRATION > TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
                        AND ROWNUM = 1
                    ), 'NO') VENDOR_RATE_SET
        FROM TMMGR.LOCATION
        WHERE (
                SUBSTR(LOCATION.ID, 6, 1) BETWEEN 'A'
                    AND ('Z')
                AND SUBSTR(LOCATION.ID, 3, 1) BETWEEN '0'
                    AND ('9')
                )
        )
    ORDER BY ISSUES DESC
    )
WHERE ISSUES NOT IN (
        0,
        3
        );
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros