Solved

Oracle - Query where clause issue

Posted on 2013-01-14
9
517 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 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
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 shows how to recover a database from a user managed backup

830 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