Request only numerical values ​​of the earliest possible date

Question

I need to execute a request from oracle 11 db. In the query below, I get all the latest TAG_VALUE, TAG_DESC, INSERTION_DATE and PROJECT_ID from my database.

 SELECT * FROM (SELECT t.tag_value, t.tag_desc, u.update_as_of AS INSERTION_DATE, p.proj_id AS PROJECT_ID, Row_number() over( PARTITION BY p.proj_id ORDER BY u.update_as_of DESC) RN FROM project p join update u ON p.project_id = u.project_id join tag t ON t.tag_id = u.tag_id WHERE t.tag_desc LIKE 'Equity%') WHERE rn = 1; 

However, I came to the conclusion that the answer to my request (without sorting by date) may look like this:

 +----------------------------------------------+ | TAG_VALUE TAG_DESC INSERTION_DATE PROJECT_ID | +----------------------------------------------+ | null Equity 14-DEC-14 1 | | 0 Equity 14-DEC-14 1 | | 312 Equity 14-DEC-14 1 | | 23343 Equity 17-DEC-11 5 | | 1263 Equity 16-DEC-11 5 | | null Equity 22-JÄN-14 2 | | null Equity 11-JÄN-14 2 | | null Equity 25-SEPT-13 2 | | 0 Equity 20-SEPT-13 2 | | 1234 Equity 19-SEPT-13 2 | | 13415 Equity 18-SEPT-13 2 | | 99999 Equity 16-OCT-10 9 | +----------------------------------------------+ 

My Result Set should look like this:

 +----------------------------------------------+ | TAG_VALUE TAG_DESC INSERTION_DATE PROJECT_ID | +----------------------------------------------+ | 312 Equity 14-DEC-14 1 | | 23343 Equity 17-DEC-11 5 | | 1234 Equity 19-SEPT-13 2 | | 99999 Equity 16-OCT-10 9 | +----------------------------------------------+ 

There are two cases that mainly focus on the same problem:

• As you can see, there are two cases where project_id = 1 the insertion date is always the same. However, in my request above, I am still getting null back due to ordering . How can I return number 312 without getting null or 0 ?
• If projectID = 2 there are different insertion dates, and earlier dates have TAG_VALUE null elements. However, I would like to have a tagValue of | 1234 Equity 19-SEPT-13 2 | | 1234 Equity 19-SEPT-13 2 | because this is the last meaning?

How can I, in principle, ignore all null values ​​as well as 0 values ​​and accept only a numeric value that is greater than 0 with the earliest date?

I really appreciate your answer!

Answer 1

Given that your grouping occurs in PROJECT_ID for INSERTION_DATE DESC and positive TAG_VALUE, I changed the analytic functions to achieve the result. This may not be a reliable solution, but it will certainly help you.

Data setting:

 CREATE TABLE Table1 ("TAG_VALUE" varchar2(5), "TAG_DESC" varchar2(6), "INSERTION_DATE" varchar2(10), "PROJECT_ID" int) ; INSERT ALL INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES (NULL, 'Equity', '14-DEC-14', 1) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('0', 'Equity', '14-DEC-14', 1) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('312', 'Equity', '14-DEC-14', 1) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('23343', 'Equity', '17-DEC-11', 5) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('1263', 'Equity', '16-DEC-11', 5) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES (NULL, 'Equity', '22-JÄN-14', 2) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES (NULL, 'Equity', '11-JÄN-14', 2) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES (NULL, 'Equity', '25-SEPT-13', 2) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('0', 'Equity', '20-SEPT-13', 2) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('1234', 'Equity', '19-SEPT-13', 2) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('13415', 'Equity', '18-SEPT-13', 2) INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID") VALUES ('99999', 'Equity', '16-OCT-10', 9) SELECT * FROM dual ; 

Query:

 SELECT tag_value, tag_desc, insertion_date, project_id FROM (SELECT tag_value, tag_desc, insertion_date, project_id, Last_value(Decode(tag_value, 0, NULL, tag_value) ignore nulls) over ( PARTITION BY project_id ORDER BY insertion_date ROWS BETWEEN unbounded preceding AND unbounded following ) new_tag_value FROM table1) WHERE tag_value = new_tag_value; 

Result:

 TAG_VALUE TAG_DESC INSERTION_DATE PROJECT_ID 312 Equity 14-DEC-14 1 1234 Equity 19-SEPT-13 2 23343 Equity 17-DEC-11 5 99999 Equity 16-OCT-10 9 

Here is the fiddle

Answer 2

Your question: "How, I can basically ignore all NULL values, as well as 0 values"

The simple answer is: Removing these entries in the WHERE clause.

I am using AND t.tag_value > 0 here. You can replace it with AND t.tag_value <> 0 AND t.tag_value IS NOT NULL if you want to allow negative values.

 SELECT * FROM ( SELECT t.tag_value, t.tag_desc, u.update_as_of AS INSERTION_DATE, p.proj_id AS PROJECT_ID, ROW_NUMBER() OVER(PARTITION BY p.proj_id ORDER BY u.update_as_of DESC) RN FROM updated u JOIN project p ON p.project_id = u.project_id JOIN tag t ON t.tag_id = u.tag_id WHERE t.tag_desc LIKE 'Equity%' AND t.tag_value > 0 ) WHERE RN = 1; 

Answer 3

You can select min (Insertion_Date) and the project ID in the internal query from the table and filter it using Tag_Value <> Null. Then you are in the outer query, the inner join of the table with this inner query on project_id and insertion_date.

Answer 4

Use case 1:

If I understand your use cases correctly, you can do this by prioritizing nonzero nonzero tag_value -s in a sentence for ordering analytic functions as follows:

 ROW_NUMBER() OVER ( PARTITION BY p.proj_id ORDER BY CASE WHEN t.tag_value > 0 THEN 0 ELSE 1 END ASC, u.update_as_of DESC ) RN 

This, of course, will give you null -s or zeros in your output each time there are no other tag_value -s identified by p.proj_id in your data section.

---

Use case 2:

If you want to completely get rid of zeros and null -s, you will have to change the where clause of your (internal) request:

 WHERE t.tag_desc LIKE 'Equity%' AND t.tag_value > 0