New answers tagged where
0
If NULL value of @SubDesc1 means "return all records, do not apply filtering", then
SELECT *
FROM Table1
WHERE Category = 'Category1'
AND [Description] ='ABC'
AND ( SubDescription1 = @SubDesc1
OR @SubDesc1 IS NULL)
If NULL value of @SubDesc1 means "return only those records, where the field IS NULL too", then
SELECT *
FROM ...
1
You can't reference a column alias on the same level where you introduced it. You need to wrap your query into a derived table. Note that the columns in the SELECT list of your sub-query are pretty useless as you are only using the balance column in the outer query:
select *
from (
select PARTY_ID,
PARTY_NAME,
IT_ID,
IT_ID || ' -...
1
As you indicated by the small_table only containing one range, adding the date adds a hint that could be used in some cases. As worst it will just add a quick condition to an already retrieved row.
'had indexing' really doesn't help, it matters what the index begins with. Indexes can have multiple parts and the order matters significantly. An index with a '...
1
For the sake of completeness, you could have solved the problem (many excludes) on the following way too:
SELECT history.date
, history.new_value
, res.resource_name
FROM item_history AS history
INNER JOIN owned_resource AS res
ON history.com_par_id = res.own_resource_uuid
AND res.inactive = 0
WHERE (history.attr_name = '...
0
Based in my experience, using SQL functions in WHERE conditions is bad for the SQL performance.
You can rewrite your script to do the following:
Create a temp table or variable table
INSERT INTO SELECT to that temp or variable table but in the SELECT use the lower() to the column that you are currently using in WHERE condition.
SELECT [columns] FROM the ...
3
I would use join here instead of old writing way
Regarding "lower" are you sure that this is needed?
Did you check collation of new_value?
If it has CI(case insensitive) in collation name it you can avoid using lower.
If you do, you can write something like this
WITH cte
AS ( SELECT history.date
,history.new_value
,res....
Top 50 recent answers are included
