Solved

Oracle - Query to count NO value per row

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

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 38
convert in derived column 7 27
SELECT query on two levels (detail and summary) 13 47
SQL Syntax 5 33
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now