Data in a subquery is not recognized as a number?
I created a view based on several tables created by an application that I use at work. The SQL generating the view contains a lot of CASE WHEN clauses as the raw tables are missing some of the logic needed for the reports I run.
One of the things I do is that I want 0 values in some columns when one item does not match between tables.
case when e.Item=p.Item then p.ColumnA else 0 end as NewColumnA
ColumnA is of datatype FLOAT and it looks like NewColumA is also FLOAT. But when I run the query in the view and indicate that I don't need records where NewColumnA is zero, it is very slow. However, if I add ROUND outside of CASE WHEN in VIEW, it is much faster (80s versus 0.5s).
round(case when e.Item=p.Item then p.ColumnA else 0 end,6) as NewColumnA
This now solves my performance problem, but I guess this is hardly the "best way" to solve it. I am also very curious to know what the problem is.
source to share
Using queries on computed values in the view will slow down your query as it must do all the computations in the view before it can perform a comparison.
Include e.Item and p.Item in your view under a different alias, in this demo I am using e_item and p_item.
Apply this to the WHERE clause and do not include NewColumnA in the WHERE clause:
WHERE
exists(SELECT e_item EXCEPT SELECT p_item)
Using the above syntax to prevent null values from yielding incorrect results.
If your columns never contain null, you can simply use
WHERE
e_item <> p_item
source to share