Solved

Oracle - Query to count NO value per row

Posted on 2013-01-12
2
762 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 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