Forum Stats

  • 3,741,218 Users
  • 2,248,393 Discussions
  • 7,861,681 Comments

Discussions

Apply filter in Oracle View

user12251389
user12251389 Member Posts: 306 Blue Ribbon
edited Aug 26, 2020 1:14PM in SQL & PL/SQL

I have below view where i just wanted to apply small filter such that for example i want to ignore or remove all rows for each IDENTIFIER from the View where member_descr = 'O' and member_ratio > = -5.

I am not sure if i want to create new Union condition or apply filter where i am calculating value for member_ratio value for member_descr = 'O'

Currently in the fiddle we can see the member_ratio value is 15.32778 for member_descr = 'O' which is ok for Identifier I0000RTERER3 after calculation. But after calculation for member_descr = 'O'  if the member_ratio >= -5 then i want to ignore or remove all the rows for this identifier from view.

Tagged:
user12251389

Best Answer

Answers

  • user12251389
    user12251389 Member Posts: 306 Blue Ribbon
    edited Aug 26, 2020 9:24AM

    I have added fiddle example complete ...can you please check now..

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,727 Red Diamond
    edited Aug 26, 2020 9:45AM

    Hi,

    user12251389 wrote:Thanks i will try to create test example but can you just provide an idea where i can apply with the GROUP BY ?

    It looks like you want to do something similar to this:

    Show the deptno, job, ename, hiredate and sal columns for all rows in the scott.emp tableAlso, show the latest hiredate and the total salary of CLERKs in each departmentAlso, show the latest hiredate and 75% of the total salary for all 'MANAGERs in each department.

    producing output like this:

      DEPTNO JOB       ENAME      HIREDATE         SAL-------- --------- ---------- ----------- --------      10 MANAGER   CLARK      09-Jun-1981     2450      10 PRESIDENT KING       17-Nov-1981     5000      10 CLERK     MILLER     23-Jan-1982     1300      10 CLERK                23-Jan-1982     1300      10 MANAGER              09-Jun-1981   1837.5      20 CLERK     ADAMS      23-May-1987     1100      20 ANALYST   FORD       03-Dec-1981     3000      20 MANAGER   JONES      02-Apr-1981     2975      20 ANALYST   SCOTT      19-Apr-1987     3000      20 CLERK     SMITH      17-Dec-1980      800      20 CLERK                23-May-1987     1900      20 MANAGER              02-Apr-1981  2231.25      30 SALESMAN  ALLEN      20-Feb-1981     1600      30 MANAGER   BLAKE      01-May-1981     2850      30 CLERK     JAMES      03-Dec-1981      950      30 SALESMAN  MARTIN     28-Sep-1981     1250      30 SALESMAN  TURNER     08-Sep-1981     1500      30 SALESMAN  WARD       22-Feb-1981     1250      30 CLERK                03-Dec-1981      950      30 MANAGER              01-May-1981   2137.5

    You can do that without UNION like this:

    SELECT    deptno,         job,         ename,         MAX (hiredate)   AS hiredate,         SUM (sal) * CASE                          WHEN  GROUPING (ename)  = 1                          AND   job               = 'MANAGER'                          THEN  .75                          ELSE  1                      END  AS salFROM      scott.empGROUP BY  GROUPING SETS ( (deptno, job, ename)                        , (deptno, job)                        )HAVING    GROUPING (ename)  = 0OR        job               IN ('CLERK', 'MANAGER')ORDER BY  deptno, ename, job;

    If there is no CLERK or no MANAGER in a department, then the query above will not produce a "totals" row for that depatment and job.  If you need a row (with NULL for the date and 0 for the salary) then the solution is a little more complicated, but you still don't need a UNION.

  • user12251389
    user12251389 Member Posts: 306 Blue Ribbon
    edited Aug 26, 2020 9:47AM

    HI Frank this is bit confusing sorry...i have added the complete fiddle example now about what i want to achieve...

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,727 Red Diamond
    edited Aug 26, 2020 9:59AM

    Hi,

    user12251389 wrote:HI Frank this is bit confusing sorry...i have added the complete fiddle example now about what i want to achieve...

    Please don't make changes to existing messages, especially after people have replied to them; it make the thread even more confusing, and it makes missing your changes very easy.  Post all corrections and additions in a new reply.

    What are the exact results you want from the given sample data?  (Since you'll have to calculate this manually, you might want to use much less sample data.  I'll bet you could give a good example of what you need with only 10 rows of sample data, and fewer columns as well.)

  • user12251389
    user12251389 Member Posts: 306 Blue Ribbon
    edited Aug 26, 2020 10:13AM

    Sorry for confusion...i want to keep the logic and everything as it is in my View...Just i want to apply additional condition such that after calculation for member_descr = 'O'  if the member_ratio >= -5 then i want to ignore or remove all the rows for this identifier from view. Currently the result is fine with what data i have provided ...

    I want the result like this :

    Select * from IS_ID where MEMBER_DESCR = 'O' and member_ratio > -5;

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Aug 26, 2020 10:10AM

    Post CREATE table statement of your table is_test (relevant columns only) and some sample data as INSERT statement, post your desired result you want to obtain from your sample data.

  • user12251389
    user12251389 Member Posts: 306 Blue Ribbon
    edited Aug 26, 2020 10:14AM

    i already fiddle with example and i want the result something like below:

    Select * from IS_ID where MEMBER_DESCR = 'O' and member_ratio > -5;

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Aug 26, 2020 10:42AM

    You STILL have not answered @Frank Kulash's questions (which is part of the FAQ guidelines on --> ), question #5....WHAT is your database version???

    5) Database Version and IDE Version
    Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.If you're not sure what it is you can do the following:select * from v$version;

    in an SQL*Plus session and paste the results.

  • user12251389
    user12251389 Member Posts: 306 Blue Ribbon
    edited Aug 26, 2020 10:55AM

    its Oracle 18c

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,727 Red Diamond
    edited Aug 26, 2020 11:01AM

    Hi,

    user12251389 wrote:i already fiddle with example and i want the result something like below:Select * from IS_ID where MEMBER_DESCR = 'O' and member_ratio > -5; 

    Once again, post the exact results you want from the given data, that is, post exactly what

    SELECT  *FROM    is_id;

    should produce with the given sample data.  It's great if you want to describe the results, like you did above, but describe the results in addition to (not instead of) actually posting them.

    Not all Oracle version are the same.  In fact, all Oracle versions are different.  The best way to do something in Oracle version N might not work in version N-1, and it might be a very complicated, inefficient way to do it in version N+1.  If you want a good solution that works in your version, then say what your version is.

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Aug 26, 2020 12:37PM
    user12251389 wrote:I have below view where i just wanted to apply small filter such that for example i want to ignore or remove all rows for each IDENTIFIER from the View where member_descr = 'O' and member_ratio > = -5.I am not sure if i want to create new Union condition or apply filter where i am calculating value for member_ratio value for member_descr = 'O'Currently in the fiddle we can see the member_ratio value is 15.32778 for member_descr = 'O' which is ok for Identifier I0000RTERER3 after calculation. But after calculation for member_descr = 'O' if the member_ratio >= -5 then i want to ignore or remove all the rows for this identifier from view.

    Respected Moderators (I am aware of few names, but don't want to ping) ,

    I think this is the 4/5th times the user has changed his original post, despite of the fact he has given advise of not to do so in Reply 5. He has removed the "fiddle link" which he has actually added afterwards. I an adding screenshots because I don't know the history is preserved by the forum software or not, and he can delete.

    pastedImage_1.png

    He has removed his view definition also from original post (Proof in Reply 1). I think this thread should be locked if not ban this user from the forum.

    I can guess, why he has deleted the "proofs" that can lead to the question that he is doing the office work with help of forums. He got the "work around" in other forum.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Aug 26, 2020 12:45PM
    user12251389 wrote:its Oracle 18c

    Thanks but try to follow the guidelines and post the FULL version....they are some differences in DB versions where some functions don't exist.

    always post FULL 4 digit numbers as described in the guidelines.

  • EdStevens
    EdStevens Member Posts: 28,239 Gold Crown
    edited Aug 26, 2020 1:04PM

    Since he also posted the same question on SO, I'm guessing the thinks this form works the same way.  If you are not familiar with SO, the only way to post formatted code is in either the original question or in a proposed 'answer'.  Comments/replies are limited to unformulated sentences.  Therefor, their common practice is the opposite of ours ... any code updates are made in the original post.

    I prefer the the way things work here.

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Aug 26, 2020 1:14PM

    Sorry, I wasn't aware of this, may be I have over reacted.

    But there should be a track, now a new person will not understand what has happened, even if somebody like me who has downloaded the DDL and DMLs from the link (also created the view from here) and posts something, the new person will not be able to related and not be able to fine tune the SQL.

Sign In or Register to comment.