New answers tagged query
4
As soon as the connection was lost the transaction was rolled back. If it was doing a lot of work and it ran for some time before the connection was lost, what you are seeing in the activity monitor is the rollback which could take awhile.
Type EXEC sp_who2 to see more of what is going on.
If you want the procedure to keep running, and you are on SQL Server ...
1
Most objects in SSMS's Object Explorer are drag and dropable directly into the Query Editor tab you're currently in.
For example, if you hold left-click, and drag the name of the table you want, you can drop it on any line of code in your active Query Editor tab.
Furthermore, I use this as a quick way to list out all of the columns of an entity in my query ...
1
In this case I think you can avoid to use CASE, just use AND, OR.
Given the next example:
create table t (atr1 int, atr2 int);
insert into t values
(1, 15),(2, 30),(3, 45),(1, 30),(2, null),(3, 90);
declare @var1 int = 2;
declare @var2 int = 30;
You could write your query as:
select
*
from
t
where
atr1 = @var1
and
(
(@var2 not in (...
4
It is not clear to me what you are trying to do, so this is a wild guess:
SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER(:var1)
AND ((TO_NUMBER(:var1) NOT IN (1460, 1478, 1481) AND ATTR2 IS NULL)
OR
(TO_NUMBER(:var1) IN (1460, 1478, 1481) AND ATTR2 = TO_NUMBER(:var2))
;
I added some redundant parentheses for clarity. Using a union is ...
0
If you can put these conditions into table variables or Table Valued Parameters, the query becomes somewhat simpler:
DECLARE @ExclFilters TABLE (Value varchar(100);
INSERT @ExclFilters (Value)
VALUES
('Alpha'),
('Beta'),
('Gamma');
DECLARE @InclFilters TABLE (Value varchar(100);
INSERT @InclFilters (Value)
VALUES
('Delta'),
('Eta'),
('Epsilon'),
...
7
Hope you're doing great.
I would like to see your schema to make sure all is good.
I tried to recreate what I believe you did.
Here is what I did:
mysql> create database test295895;
mysql> use test295895;
mysql> create table my_table (my_column JSON);
Then I inserted the values you showed:
mysql> insert into my_table VALUES('{"my_value"...
2
In your query when evaluating NOT LIKE you should use AND instead of OR, because you want all of them to be met.
SELECT DISTINCT
Name,
Category,
CODE,
Participants
WHERE (Participants NOT LIKE '%Alpha%'
AND Participants NOT LIKE '%Beta%'
AND Participants NOT LIKE '%Gamma%')
AND (Participants LIKE '%Delta%'
OR ...
2
If all students returned by the first select need to be combined with all faculties returned by the second select, then you just need to cross-join the two subsets and select from the resulting set. You can use either the classic comma join or the explicit CROSS JOIN syntax:
Comma join:
INSERT INTO
dbo.ClassList (StudentID, FacultyID, ModifiedDate)
...
1
PL/SQL Developer implicitly executes additional statements in the background.
dbms_xplan.display_cursor(null,null,'basic') returns information from the previously executed statement. It was begin :id := sys.dbms_transaction.local_transaction_id; end;.
After executing your SQL, find it in V$SQL, example:
select sql_id, child_number, sql_text from v$sql
where ...
1
After running the below query to find out blocked sessions -
select s.machine, s.sid, s.serial#, s.event, s.state, s.blocking_session, sql.sql_text
from gv$session s
left join gv$sql sql
on s.sql_id = sql.sql_id
and s.sql_child_number = sql.child_number
and s.inst_id = sql.inst_id
where (s.blocking_session is not null
or (s....
1
If an insert into the table is hanging using the same primary key value then it suggests there is an open transaction which has inserted this value but not committed yet.
You can confirm this by checking for blocked sessions from a sufficiently privileged user:
select s.machine, s.sid, s.serial#, s.event, s.state, s.blocking_session, sql.sql_text
from gv$...
2
Better is a relative term as different people will have different opinions got different engines. That said, a simpler method to search for multiple records using the same column would be IN. Your query would look something like this:
SELECT *
FROM Customers
WHERE CustID IN ("11023", "11045", "11001", "101989");
...
1
Answer to your 1st question
NO this is explain plan and not execution plan.
EXPLAIN PLAN command - displays an explain plan for a SQL statement without actually executing the statement.
Answer to your 2nd Question:
Below will show execution plan of last executed SQL in the current session
Select plan_table_output from table(dbms_xplan.display_cursor(null,...
Top 50 recent answers are included
