Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle - Query to count NO value per row

Posted on 2013-01-12
2
Medium Priority
?
780 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

610 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