New answers tagged

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