Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Oracle - Query with wrong count decode

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

User generated image

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
        );

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>COUNT(DECODE(SCNALIAS_SET, 'NO', 1)) OVER ()

Try this (I prefer case over the oracle specific and older decode):
SUM(CASE when SCNALIAS_SET = 'NO' then 1 end)) OVER ()

Do this for all of them...
Avatar of Wilder1626

ASKER

Hi

I just updated the  query but know i have an error
ORA-00923: FROM keyword not found where expected on this part:
SUM(CASE when VENDOR_MAINTENANCE_MATCH = 'NO' then 1 end)) OVER () TOTAL_VENDORT_MAINT_NOT_SET,
row 8

Do you know why?


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,
        SUM(CASE when SCNALIAS_SET = 'NO' then 1 end)) OVER () TOTAL_SCNALIAS_NOT_SET,
        SUM(CASE when VENDOR_MAINTENANCE_MATCH = 'NO' then 1 end)) OVER () TOTAL_VENDORT_MAINT_NOT_SET,
        SUM(CASE when VENDOR_RATE_SET = 'NO' then 1 end)) 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
        );

Open in new window

>>Do you know why?

Looks like I might have had an extra ')'.
this doesn't directly address your syntax error; but to generate totals you may want to consider using ROLLUP or CUBE in your GROUP BY


minor note on above,  I would use COUNT to do a count, rather than SUM.

Yes, summing a bunch of 1-values will return the same result, but COUNT is self-documenting as to the intent
you are right, it was an extra ')'

Now like this:
 SUM(CASE when SCNALIAS_SET = 'NO' then 1 end) OVER () TOTAL_SCNALIAS_NOT_SET,
        SUM(CASE when VENDOR_MAINTENANCE_MATCH = 'NO' then 1 end) OVER () TOTAL_VENDORT_MAINT_NOT_SET,
        SUM(CASE when VENDOR_RATE_SET = 'NO' then 1 end) OVER () TOTAL_VENDOR_RATE_NOT_SET

Open in new window


But the weird this is that it still does not get the good count or SUM.

I have the same result as before.
can you provide sample data and expected results?

in text format, no screen shots please
>>But the weird this is that it still does not get the good count or SUM.

Can you provide sample data what shows a 'bad' count?

Do you possibly have a data issue where there are leading or trailing spaces around the 'NO'?

Using your original count:
COUNT(DECODE(trim(SCNALIAS_SET), 'NO', 1)) OVER () TOTAL_SCNALIAS_NOT_SET,
Sure, Here is an extract that i just did

If you see, i dont have any 'NO' in column C but the count in G2 = 3279. I should have ZERO.
Test-extract.xls
Has to be a data issue.

I think what we were asking for is base table raw data.

If I hacked it apart right, what does this return:

select SCNALIAS_SET, count(*) 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')
                )
)
group by SCNALIAS_SET;

Open in new window

HI

this return :

SCNALIAS_SET      COUNT(*)
NO                                   3,279
-                                    1,059
>>i dont have any 'NO' in column C but the count in G2 = 3279. I should have ZERO.

3,279 looks right to me:  NO  3,279

Check to see if 3,279 of them don't have spaces instead of nulls?

select nvl(LOCATION.SCNALIAS,'is null','not null'),
     nvl(trim(LOCATION.SCNALIAS),'has spaces','looks good'),
count(*)
FROM TMMGR.LOCATION
group by
nvl(LOCATION.SCNALIAS,'is null','not null'),
     nvl(trim(LOCATION.SCNALIAS),'has spaces','looks good'),
Do you count the '-' as no?

in the extract i have attached i dont see any 'NO' in column C. Only in D where i have 3 and column E = 297.

In column C is populated only if column 2 (SCNALIAS) as a value. If there is no value, the the column C will say NO.
I believe you will be better off by avoiding strings and using 1 (good) or 0 (bad) in the lower level operations. (Currently you are using several decodes to interpret the strings back to numbers which can be avoided by starting with numbers.)

Similarly you are using 2 "correlated subqueries" in the selection list of the inner most subquery which I believe will not be efficient and both can be substituted with joined subqueries.

To help achieve self-documenting as to the intent I have also taken the liberty of changing "TOTAL_" to "COUNT_", my view is that "TOTAL" indicates a SUM() operation.

Once these changes are made, substituting strings from the 1 or 0 values can be undertaken for the final output. So, taking these suggestions together I propose this:
SELECT ID
    , SCNALIAS
    , case when SCNALIAS_SET             = 1 then '-' else 'NO' end as SCNALIAS_SET_STR
    , case when VENDOR_MAINTENANCE_MATCH = 1 then '-' else 'NO' end as VENDOR_MAINTENANCE_MATCH_STR
    , case when VENDOR_RATE_SET          = 1 then '-' else 'NO' end as VENDOR_RATE_SET_STR
    , ISSUES
    , COUNT_SCNALIAS_NOT_SET
    , COUNT_VENDORT_MAINT_NOT_SET
    , COUNT_VENDOR_RATE_NOT_SET
FROM (
    SELECT ID
        , SCNALIAS
        , SCNALIAS_SET
        , VENDOR_MAINTENANCE_MATCH
        , VENDOR_RATE_SET
        /* use of decode removed, with 1 good and 0 bad, 3 - (good + good + good) = 0*/
        , 3 - (SCNALIAS_SET + VENDOR_MAINTENANCE_MATCH + VENDOR_RATE_SET) ISSUES
        , count(SCNALIAS_COUNT) over(order by 3-(SCNALIAS_SET + VENDOR_MAINTENANCE_MATCH + VENDOR_RATE_SET)) COUNT_SCNALIAS_NOT_SET
        , count(VMM_COUNT)      over(order by 3-(SCNALIAS_SET + VENDOR_MAINTENANCE_MATCH + VENDOR_RATE_SET)) COUNT_VENDORT_MAINT_NOT_SET
        , count(VRS_COUNT)      over(order by 3-(SCNALIAS_SET + VENDOR_MAINTENANCE_MATCH + VENDOR_RATE_SET)) COUNT_VENDOR_RATE_NOT_SET
    FROM (
        SELECT  /* use of distinct: substitute group by */
              LOCATION.ID
            , LOCATION.SCNALIAS
            , CASE 
                WHEN LOCATION.SCNALIAS IS NOT NULL
                    THEN 1
                ELSE 0
                END SCNALIAS_SET
            , NVL(VMM.IS_SET, 0) VENDOR_MAINTENANCE_MATCH /* use of subquery in selection list subsituted */
            , NVL(VRS.IS_SET, 0) VENDOR_RATE_SET          /* use of subquery in selection list subsituted */
        FROM TMMGR.LOCATION
        LEFT JOIN (
                    SELECT FP_VENDOR_INFO.VENDOR_NBR, 1 as IS_SET /* correleated subquery in selection list substitute */
                    FROM TMMGR.FP_VENDOR_INFO
                    GROUP BY
                       FP_VENDOR_INFO.VENDOR_NBR
                  ) as VMM ON LOCATION.SCNALIAS = VMM.VENDOR_NBR
        LEFT JOIN (
                    SELECT LRN.FROM_LOW_KEY_VALUE, 1 as IS_SET    /* correleated subquery in selection list substituted */
                    FROM TL_RATE TLR                              /* use of old join style, ansi preferred, no change made */
                        , 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 TLR.EXPIRATION > TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
                    GROUP BY
                        LRN.FROM_LOW_KEY_VALUE
                  ) as VRS ON LOCATION.ID = VRS.FROM_LOW_KEY_VALUE
        WHERE (
                SUBSTR(LOCATION.ID, 6, 1) BETWEEN 'A' AND ('Z')
                AND SUBSTR(LOCATION.ID, 3, 1) BETWEEN '0' AND ('9')
                )
        GROUP BY
              LOCATION.ID
            , LOCATION.SCNALIAS
            , VMM.IS_SET
            , VRS.IS_SET
        ) 
    )
WHERE ISSUES = 2 /* believe this is the equivalent to previous NOT IN (0,3)*/
ORDER BY ISSUES DESC /* not sure this is logical if only selecting 2*/
;

Open in new window

[edits: needed change for ISSUES calculation- sorry]
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello all

sorry for the delay, i was on vacation.

Thanks again for your help, i was able to now have the good counts.