Solved

Oracle - Query where clause issue

Posted on 2013-01-14
9
513 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 76

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
 
LVL 76

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 11

Author Closing Comment

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

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 73

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 73

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

746 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