Solved

Oracle - Query to count NO value per row

Posted on 2013-01-12
2
757 Views
Last Modified: 2013-01-12
Hello all

I have this SQL where i would like to count the number of NO value per row.

It would add an extract column to the query called "ISSUE" and put the count of NO per row.

ex:
add column
Thanks again

Full code:
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') FREIGHT_PAY_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 LOCATION.SCNALIAS ASC;

Open in new window

0
Comment
Question by:Wilder1626
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38770325
SELECT id,
       scnalias,
       scnalias_set,
       freight_pay_match,
       vendor_rate_set,
         DECODE(scnalias_set, 'NO', 1, 0)
       + DECODE(freight_pay_match, 'NO', 1, 0)
       + DECODE(vendor_rate_set, 'NO', 1, 0)
           issues
  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'
               )
                   freight_pay_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 location.scnalias ASC;
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38770511
Ho again.

Many thanks, this is great.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question