All geek questions in one place

Is there a way to nullify a WHERE clause in SQL?

This is my basic request.

select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a

and he produces the following conclusion

 Projects WIP days outstanding History - AURANGABAD - NASIK 0 History - PUNE - MUMBAI 0 History - NASIK - MUMBAI 89.92 History - SASAGRAM - MUMBAI 0 History - SASAGRAM - MUMBAI 1386.52 History - AURANGABAD - MUMBAI 83.25

Now I need to display all the lines except the 4th line. The reason I use the case statement in the first place is because if I did (condition billing_fy! = 0 - prevent the error caused by dividing by 0)

 select projects, round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) as "WIP days outstanding" from table1 where billing_fy!=0 group by projects;

I would get

 Projects WIP days outstanding History - SASAGRAM - MUMBAI 1386.52 History - NASIK - MUMBAI 89.92 History - AURANGABAD - MUMBAI 83.25

but I need to show the other 2 places.

 History - AURANGABAD - NASIK 0 History - PUNE - MUMBAI 0

This query only displays a row that I do not want.

 select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a where (projects='History - SASAGRAM - MUMBAI' AND billing_fy=0);

and gives the result as expected

 Projects WIP days outstanding History - SASAGRAM - MUMBAI 0

Now my question . Is there a way in SQL to cancel a WHERE clause? As in C ++, I just would have to use the not operator before the sentence to nullify it. Because basically, I want to display all EXCEPT lines of the line above.

At the moment, I have solved the problem of displaying all lines except the line that I do not want using the following code

 select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a where projects not in ('History - SASAGRAM - MUMBAI') and billing_fy!=0 union all select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a where projects not in ('History - SASAGRAM - MUMBAI') and billing_fy=0 union all select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a where projects='History - SASAGRAM - MUMBAI' and billing_fy!=0;

And it gives the required output

 Projects WIP days outstanding History - NASIK - MUMBAI 89.92 History - AURANGABAD - MUMBAI 83.25 History - AURANGABAD - NASIK 0 History - PUNE - MUMBAI 0 History - SASAGRAM - MUMBAI 1386.52

This is just a wretched way of doing this, and I would like to know if it is possible to simply deny the WHERE clause or some "neat" alternative to do what I want to do.

Thanks!

PS I am using SQL Developer and Oracle 11g (just in case someone asks)

EDIT Input Values ​​on Request

 Projects Cost_Project Billing_FY History - NASIK - MUMBAI 65696067.99 54937478.46 History - NASIK - MUMBAI 41385613.61 151909546.44 History - NASIK - MUMBAI 18029488.91 216353866.92 History - AURANGABAD - MUMBAI 33191393.23 57073935.95 History - AURANGABAD - MUMBAI 52681451.68 139055661.74 History - AURANGABAD - MUMBAI 74576522.31 390092578.24 History - PUNE - MUMBAI 0 0 History - PUNE - MUMBAI 0 0 History - PUNE - MUMBAI 0 0 History - SASAGRAM - MUMBAI 107540114.08 40653734.06 History - SASAGRAM - MUMBAI 209167760.1 28823862.66 History - SASAGRAM - MUMBAI 0 0 History - AURANGABAD - NASIK 0 0 History - AURANGABAD - NASIK 0 0 History - AURANGABAD - NASIK 0 0
+4
source share
4 answers

If I read your question correctly, you need a not operator:

 select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a where not (projects='History - SASAGRAM - MUMBAI' AND billing_fy=0);

As @ShannonSeverance points out, this will cause a problem if you have null values ​​in any field, since not (false and null) evaluates to null, which will be considered false. If you need to make this null safe, so that it only excludes a string that has both of these values, you need to do something like this:

 select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a where (not (projects='History - SASAGRAM - MUMBAI' AND billing_fy=0)) or projects is null or billing_fy is null;
+3
source

I think this should do it:

 select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a where (projects != 'History - SASAGRAM - MUMBAI' OR billing_fy != 0);
+6
source

try the following:

 select distinct a.projects , case when(billing_fy!=0) then(select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table a where (projects!='History - SASAGRAM - MUMBAI' AND billing_fy!=0);
0
source

I think the easiest way to do what you want is to group rather than use different ones:

 select a.projects, case when sum(billing_fy!=0) then (select round(((sum(cost_project)/(sum(billing_fy)/((10/12)*365)))),2) from table1 b where a.projects = b.projects group by projects) else 0 end as "WIP days outstanding" from table1 a group by a.projects
0
source

Source: https://habr.com/ru/post/1396196/

More articles:


All Articles