Solved

Oracle - Query where clause issue

Posted on 2013-01-14
9
521 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 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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 …
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

632 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