I’d like to compare two tables and get a set of results where the lookup values are mismatched as well as where the key values are missing from the other table. The first part works fine with the …
How to kill/stop a long SQL query immediately?
I am using SQL server 2008 and its management studio. I executed a query that yields many rows. I tried to cancel it via the red cancel button, but it has not stopped for the past 10 minutes. It usually stops within 3 minutes. What could the reason be and how do I stop it immediately ? Answer What could
SQL minus 2 columns – with null values
I have this table (made from a SQL query): Row 1 Row 2 2 1 3 NULL And I want to minus the 2 columns, so I just select like this: Select Row1 – Row2 From table But then I get this …
Order by stored procedure parameter
I have a problem ordering my stored procedure by a parameter given by a user, I have tried reading but the solutions I have tried won’t work. So, is there somebody who can help me? I would like it to …
Defining size of CLOB in Oracle
I am making a table in which I am storing XML. To store XML I am using CLOB data type. The max size of my XML would be 5kb. What size of CLOB column should I define while creating the table? Answer you don’t define a size exactly when setting a clob (unlike a varchar). it is just simply clob.
Throw exception from SQL Server function to stored procedure
I have stored procedure in SQL Server 2012 say spXample and a scaler-valued function say fXample. I call a function fXample from spXample. Can I throw an exception in function and catch it in stored …
check if the column value exists in subquery
i have 3 tables Product Category and ProductCategory. Product table: Category table: ProductCategory: I need a query which returns products which fall under more than 1 categories. Based on the table data above the result would be: So i wrote a query to fetch all the ProductID’s which have more than one…
How to select a record if the query returns one row, or select no record if the query returns more rows?
I require to select a row if there is only one row exists, if there are more rows, it should select 0 rows. Answer If you’re using PL/SQL, then selecting the column using select-into will throw a too_many_rows exception if there’s more than one row returned: If you want to do this just using SQL, …
How to guarantee atomic SQL inserts with subqueries?
Given a simplified table structure like this: Can I use a subquery like this for inserting records without causing a race condition? Or are subqueries not atomic? I’m worried about simultaneous INSERTs grabbing the same value for num and then causing a unique constraint violation. Answer Yes, this can m…
query for first and last day of month
If I have the name of the month, how can I have the first and last day of that month in SQL? I have this query to returns the month names: Result: Now, how can i get the first and last day of that months? changing the query. Answer Try this :- Result :- Result obtained taking the help from