Avatar of Wilder1626
Wilder1626
Flag 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

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

Open in new window

Oracle DatabaseSQL

Avatar of undefined
Last Comment
Wilder1626

8/22/2022 - Mon
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...
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

slightwv (䄆 Netminder)

>>Do you know why?

Looks like I might have had an extra ')'.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Sean Stuber

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
Wilder1626

ASKER
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.
Sean Stuber

can you provide sample data and expected results?

in text format, no screen shots please
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>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,
Wilder1626

ASKER
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
slightwv (䄆 Netminder)

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Wilder1626

ASKER
HI

this return :

SCNALIAS_SET      COUNT(*)
NO                                   3,279
-                                    1,059
slightwv (䄆 Netminder)

>>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'),
Wilder1626

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Wilder1626

ASKER
Hello all

sorry for the delay, i was on vacation.

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