Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle - Query where clause issue

Posted on 2013-01-14
9
Medium Priority
?
523 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

670 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