SQL add filter only if a variable is not null

You can translate your requirement into : SELECT route_id [ROUTE_ID] FROM route_master(NOLOCK) WHERE route_ou = 2 AND (@l_s_query is null OR route_query = @l_s_query) AND lang_id = 1 OPTION (RECOMPILE) The OPTION (RECOMPILE) is optional but can give better execution plans at the expense of extra compilation time as discussed in the canonical article on … Read more

How to automatically generate unique id in SQL like UID12345678?

The only viable solution in my opinion is to use an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value a computed, persisted column to convert that numeric value to the value you need So try this: CREATE TABLE dbo.tblUsers (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY … Read more

Oracle IN vs Exists difference?

simply put, EXISTS is usually used for checking whether rows that meet a criteria exist in another (or the same) table. your SQL using EXISTS would look like this: select * from emp e where exists(select * from emp e2 where e.empno = e2.empno and e2.ename in (‘smith’, ‘brown’, ‘john’, ‘johnson’)) so you can see … Read more

Sql query for tree table

Expanding on a_horse_with_no_name’s answer, this show how to use SQL Server’s implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question. declare @t table(id int,parentId int,name varchar(20)) insert @t select 1, 0 ,’Category1′ insert @t select 2, 0, ‘Category2’ insert @t select 3, 1, ‘Category3’ … Read more

MySQL subquery returns more than one row

If you get error:error no 1242 Subquery returns more than one row, try to put ANY before your subquery. Eg: This query return error: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); This is good query: SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?

Maybe I don’t understand the question, but wouldn’t this do it? (you must know what you want back) INSERT INTO some_table (…) VALUES (…) RETURNING some_column_a, some_column_b, some_column_c, … INTO :out_a, :out_b, :out_c, … @Vincent returning bulk collect into for multi-row insert works only in conjunction with forall (in another words if you insert from … Read more

Difference between === null and isNull in Spark DataDrame

First and foremost don’t use null in your Scala code unless you really have to for compatibility reasons. Regarding your question it is plain SQL. col(“c1”) === null is interpreted as c1 = NULL and, because NULL marks undefined values, result is undefined for any value including NULL itself. spark.sql(“SELECT NULL = NULL”).show +————-+ |(NULL … Read more