Solved

Oracle - Query where clause issue

Posted on 2013-01-14
9
519 Views
Last Modified: 2013-01-15
Hello all

i have this query that work great but i would like to filter only is the ISSUES column = 2.

How can i do that?

thanks for your help.



SELECT ID,
       SCNALIAS,
       SCNALIAS_SET,
       FREIGHT_PAY_MATCH,
       VENDOR_RATE_SET,
       ISSUES,
       DECODE(ROWNUM, 1, TOTAL_SCNALIAS_NOT_SET) TOTAL_SCNALIAS_NOT_SET,
       DECODE(ROWNUM, 1, TOTAL_FREIGHT_PAY_NOT_SET) TOTAL_FREIGHT_PAY_NOT_SET,
       DECODE(ROWNUM, 1, TOTAL_VENDOR_RATE_NOT_SET) TOTAL_VENDOR_RATE_NOT_SET
  FROM (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,
               COUNT(DECODE(SCNALIAS_SET, 'NO', 1)) OVER () TOTAL_SCNALIAS_NOT_SET,
               COUNT(DECODE(FREIGHT_PAY_MATCH, 'NO', 1)) OVER () TOTAL_FREIGHT_PAY_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'
                       )
                           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 ISSUES DESC); 

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
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38776541
Maybe I'm missing something does this work:

...
                    AND SUBSTR(LOCATION.ID, 3, 1) BETWEEN '0' AND ('9')))
        WHERE issues=2
        ORDER BY ISSUES DESC);
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38776542
Hi,

I tried that already but i was getting a ORA-00904: "ISSUES": invalid identifier
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38776560
i know

like this it work

                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=2;
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38776572
>>I tried that already but i was getting a ORA-00904: "ISSUES": invalid identifier

It all has to do with where you can use column aliases and where you can't.

If you can order by it, you should be able to use it in the where.

For example, take a quick look at:
select * from (select dummy bob from dual) where bob='X' order by bob;

If the aliases are off, use the decode in place of the alias name:

...
                    AND SUBSTR(LOCATION.ID, 3, 1) BETWEEN '0' AND ('9')))
        WHERE DECODE(SCNALIAS_SET, 'NO', 1, 0)
               + DECODE(FREIGHT_PAY_MATCH, 'NO', 1, 0)
               + DECODE(VENDOR_RATE_SET, 'NO', 1, 0)=2
        ORDER BY ISSUES DESC);
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38776644
Thanks again for all your help
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38776710
No problem.

In the future:  Going from memory, ORDER BY is the pickiest about column aliases.  If you can order by it, you should be able to use it in a where clause.  Not sure why it caused an error for you.  Triple check your closing parans ad where you had the where.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38777129
SELECT ID,
       SCNALIAS,
       SCNALIAS_SET,
       FREIGHT_PAY_MATCH,
       VENDOR_RATE_SET,
       ISSUES,
       DECODE(ROWNUM, 1, TOTAL_SCNALIAS_NOT_SET) TOTAL_SCNALIAS_NOT_SET,
       DECODE(ROWNUM, 1, TOTAL_FREIGHT_PAY_NOT_SET) TOTAL_FREIGHT_PAY_NOT_SET,
       DECODE(ROWNUM, 1, TOTAL_VENDOR_RATE_NOT_SET) TOTAL_VENDOR_RATE_NOT_SET
  FROM (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,
               COUNT(DECODE(SCNALIAS_SET, 'NO', 1)) OVER () TOTAL_SCNALIAS_NOT_SET,
               COUNT(DECODE(FREIGHT_PAY_MATCH, 'NO', 1)) OVER () TOTAL_FREIGHT_PAY_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'
                       )
                           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')))
        )
        WHERE issues = 2;


The ORDER BY isn't needed anymore because you've filtered to a single issues value, so it's not useful as a sorting criteria
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38777844
Thank you so much again for your help.

This really help
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38778151
>>> If you can order by it, you should be able to use it in the where.

this is not true - this question is the counter example


order by resolves last, after results have been determined - by necessity, you can't sort data that hasn't been retrieved yet.  That's why aliased columns are legal in an order by - the data is there and has the given name.

where clauses resolve first, before results have been determined - by necessity, you can't get accurate results without applying the where conditions. That's why aliased columns are not legal in an order by - the data is still in it's "raw" form in the underlying tables/views.



>>>> select * from (select dummy bob from dual) where bob='X' order by bob;

In this example, the () change scope. Like math, the () define order of scope - but unlike math, not necessarily order of operations though.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

739 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