SQL & PL/SQL

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

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 table
Also, show the latest hiredate and the total salary of CLERKs in each department
Also, 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 sal
FROM      scott.emp
GROUP BY  GROUPING SETS ( (deptno, job, ename)
                        , (deptno, job)
                        )
HAVING    GROUPING (ename)  = 0
OR        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.

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

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.)

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;

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.

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;

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.

its Oracle 18c

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.

Also posted to https://stackoverflow.com/questions/63598820/apply-filter-in-oracle-view

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.

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.

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.

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.

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.