|Query very long to execute|
In the Sort (step #2) in the explain plan, it looks like the database
is either lugging around unreferenced columns (not optimum) and/or
sorting by them (ouch). Honestly though, I don't work on Postgres, so
that is just an educated guess. The database engine may not be smart
enough to discard unreferenced columns early in the process. I'd try
this SQL to nudge the database engine into discarding u
|SQL custom alert|
You might have other problems, but your if code doesn't make sense.
You are executing a query that creates a result set and then you have
an if. This should do what you want in that part of the code:
declare @WaitTime datetime;
Select @WaitTime = [ITIME]
where Status='WAITING' ;
IF (GETDATE() - @WAITTIME) > 0.02 . . .
I presume that you have done the calculation and 0
|Run t-SQL command using another user and password|
Here are two options:
Encapsulate the EXECUTE AS in a stored procedure that only your user
has EXECUTE permissions on.
CREATE PROCEDURE dbo.DoSomething
SET NOCOUNT ON;
EXECUTE AS LOGIN = 'sa';
-- do stuff
Manage the elevated permissions via signing a stored procedure, that
again, only your user has EXECUTE permissions on, with a certificate.
Look at this answer here that sounds l
|How can I grant a user limited permissions in SQL Server 2008?|
exec sp_addrolemember db_datareader, StudentAssistantsUsername
|Google Spreadsheet month,year aggregation stops at november|
Don't know if this gonna help, but when month is used in QUERY the
'numbering' is shifted. I mean: dates in january are returned (by the
month function) as zero, december is returned as '11'.
That's why I always use month(A)+1..
|single-row subquery returns more than one row using subqueries|
To determine the offending subquery, use COUNT(*) OVER() and PARTITION
BY your join field(s) ie:
SELECT *,COUNT(*) OVER(PARTITION BY CC.CONTRACTID) As Dup_CT
FROM CONTRACTCOMMENT CC
JOIN CONTRACT_ACTIVITY_LOG CAL
ON CC.CONTRACTID = CAL.CONTRACT_ID
AND CC.CONTRACT_COMMENT_ID = CAL.COMMENT_ID
AND CAL.ACTION = 'Finish Review Task'
ORDER BY Dup_CT DESC,CC.CONTRACTID
Do the same for eac
|Update field based on condition met in a row|
As I don't have access to any MySQL server at the moment I wasn't able
to test this, but if memory serves me right the syntax for MySQL to
update from a joined table should look something like this:
UPDATE `your_table` a
JOIN `your_table` b ON a.`Client` = b.`Client` AND b.`Type` = 'VP'
SET a.`Employee Code` = b.`Employee Code`
WHERE a.`Type` = 'PM'
For MS SQL Server the syntax would be this:
|Slow query with multiple joins|
Try a union instead, to begin with?
FROM tbl_IDENT I
JOIN tbl_TA AN ON AN.IDENT_ID = I.IDENT_ID AND AN.anchor_point =
JOIN tbl_AB A ON A.A_ID = I.A_ID
JOIN tbl_FULL_ARCHIVE FT ON FT.ARCHIVE_ID = I.ARCHIVE_ID
(I.DATA_STATUS = 'ACTIVE' OR I.DATA_STATUS = 'INACTIVE')
AND I.FD = 'Y'
FROM tbl_IDENT I
JOIN tbl_TA AN ON AN.IDENT_ID
|SQL return only distinct IDs from LEFT JOIN|
using row number windowed function along with a CTE will do this
pretty well. For example:
;With preResult AS (
[ID] = c.[ID],
[Name] = c.[Name],
[Keyword] = [colKeyword].[StringVal],
[DateAdded] = [colDateAdded].[DateVal],
ROW_NUMBER()OVER(PARTITION BY c.ID ORDER BY
FROM @cards AS c
LEFT JOIN @cardindexes AS [colKeyword] ON [colKe
|Count multible values sqlplus|
My guess is that you want something like
COUNT( copyNum ) number_of_copies,
SUM( CASE WHEN date_out IS NOT NULL
END ) number_copies_out,
SUM( CASE WHEN held_for_CID IS NOT NULL
END ) number_copies_reserved
GROUP BY bookID
|correct query design? cross joins driving ad-hoc reporting interface|
Don't worry, historically Oracle used the first notation for inner
joins but later on adopted ANSI SQL standards.
The results in terms of performance and returned recordsets are
exactly the same, the implicit 'comma' joins are not crossing
resultset but effectively integrating the WHERE filters. If you doubt
it, run an EXPLAIN SELECT command for both queries and you will see
the forcasted algori
|SQL query to delete databases with specific string|
Warning This is a very dangerous practice. Consider renaming the
tables instead of delete.
You will have to create Dynamic SQL as an example of how to drop
tables (I just couldn't bring myself to write DROP DATABASE)
Declare @strSQL nVarChar (Max) = ';
select @strSQL = @strSQL + 'Drop Table ' + T.TABLE_SCHEMA + '.'+
TABLE_NAME + ';
From INFORMATION_SCHEMA.TABLES T
|SQL: Multiple column and rows comparison and finding the exact match|
I could be miss understanding what you're looking for, but it sounds
like you want to join the two tables on accum_no and accm_value ?
t1.accm_pfx, t2.accm_pfx, t1.accum_no, t1.accm_value
t1.accum_no = t2.accum_no and t1.accm_value = t2.accm_value
|UDF replace function - failing to recognize strings|
You can use a cursor to loop through values in the table replace each
name with the hyper link. But replace can force unwanted outcome. For
example if a configured name is part of a separate string function
will replace it with a hyperlink.
Create FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
DECLARE @Name VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
|Azure SQL connect from Web Application|
If I had to guess I would look at a couple of things.
is your web site in the same region as your DB? If not, I would make
them be in the same region if only for the improvement of speed..
is there a security block on your DB that is only allowing access from
certain IP addresses? If so you have to allow for the web site to
have access to the DB.
|violated - parent key not found error|
ORA-02291: integrity constraint (BASE.FK_DEPT) violated - parent key
There is no such a value in DEPT1, please check it first.
insert into base.EMP1(ide,nome,rue,ville,refdept) values
(7,"EMP5","agadir","dddd",(select idd from base.DEPT1 where nomd
insert into base.EMP1(ide,nome,rue,ville,refdept)
select 7,'EMP5','agadir','dddd', idd from base.DEPT1 where
|SQLAlchemy : FOR XML PATH equivalent|
You can execute raw SQL query in SQLALCHEMY. Based on SQLALCHEMY doc
and this, Try something like:
raw_con = engine.raw_connection()
cursor = raw_con.cursor()
# add your proper SQL code here
FROM WEV_STY PS WITH(NOLOCK)
|How to pull data from field with multiple values in SQL|
Then you have to use a wildcard in your condition.
AND (DENIALCD LIKE '%35%' OR DENIAL CODE LIKE '%48%')
This will bring back any rows with the 35 or 48 in the field.
|TFS2012 : SQL to get User's project list to which he has access|
You cant do this from SQL as permissions are not pushed to the
Warehouse. You can get this from the API. If you retrieve a
ProjectInfo object it should have a HasAccess property that is set to
true if you have permission.
To find out if another user has access you will likely need to query
the Security Store.
|I am trying to create a table but its not getting created. The issue is with constraints. Mostly NOT NULL and FOREIGN KEY|
Working SQL Fiddle: http://sqlfiddle.com/#!4/05311
Here's how your SQL should look like.
CREATE TABLE Customer(
Cust_Id Number(4) NOT NULL,
Cust_First_Name VarChar2(20) NOT NULL,
Cust_Last_Name VarChar2(20) NOT NULL,
|Update query in Access with variable that can be empty but constraint the prevents update|
How about using IIF?
SQLString = "UPDATE tblRegister" & _
" SET tblRegister.Status = " & IIF(StatusID=0,"NULL",
StatusID) & _
" WHERE tblRegister.ID = 'reg01'
|Complex Pivot like query in Oracle|
Assuming I'm understanding your requirements, given your desired
results, you want to group the red, blue and green columns by columna
and columnb, not including columnc in the grouping.
To create the pivot, you can use max and case. Then you can join
those results back to the original table using the first 2 columns and
|Insert into with conditional constant|
You could use a case expression:
insert into Table(a, b, c)
CASE WHEN a = b THEN 'Enum1' ELSE 'Enum2' END
from OtherTable, YetAnotherTable
where a > 0
|SQL WHERE IN ... to JOIN table|
WITH JOIN , you query would look like
sum(CF.SUM) AS SUME,
strftime('%Y - %m', CF.CheckDate) AS CheckDate
on PriceList.CATEGORY = Category.UniqID
AND Category.UniqID = 2
JOIN CheckDetail CD
ON CD.NUMBER = PriceList.UniqID
JOIN CheckFinal CF
|Postgresql change value based on the change of another field|
You can identify sequences where the value is the same by using a
difference of row_number(). After getting the difference, you have a
group identifier and can calculate the minimum id for each group.
Then, dense_rank() will renumber the values based on this ordering.
It looks like this:
select t.id, t.value, dense_rank() over (order by minid) as new_id
from (select t.*, min(id) over (partitio
|Is it possible to JOIN a table on the TOP 1 if there is no unique identifier?|
If you don't have IDs but know the order you want, you could create
artificial IDs using ROW_NUMBER() and then do your TOP 1's off of
WITH TEMP AS (
SELECT 3 a, 1 b UNION ALL
SELECT 2, 1 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 1, 2)
SELECT A, B, ROW_NUMBER() OVER (PARTITION BY B ORDER BY B ASC) as
RowNumber FROM TEMP
;WITH TEMP AS (
|adding two columns in two tables having multiple layers|
You can use join with coalesce to remove the null values:
select t1.id, t1.somefield, t1.someint,
from table1 t1
join table2 t2 on t1.id = t2.id
and t1.somefield = t2.somefield
and t1.someint = t2.someint
SQL Fiddle Demo
Based on your input data, this joins on the first 3 columns. Not
completely sure this is what you want, b
|relative value of char variables|
You could do it like this:
a.organic,a.groupedcount/b.totalcount * 100 as percentage
(select organic,count(*) groupedcount
group by organic) a,
(select count(*) totalcount
from products) b
Or if you don't want to use ANSI-89:
a.organic,a.groupedcount/b.totalcount * 100 as percentage
(select organic,count(*) groupedcount
|T-SQL - Arithmetic overflow while working with milliseconds|
DateDiff() does indeed return an int, but I suspect that it's
DateAdd() that's giving you the error message.
You'll need to work in that precision, unfortunately, as you said you
wanted to avoid, as you're wanting to work in milliseconds.
DATEADD (datepart , number , date )
number Is an expression that can be resolved to an int that is added
to a datepart of date. User-defined variables
|How to remove repeated lines in an Oracle SQL query for multiple parent and child tables|
USE UNION and get the values from all tables and then do one JOIN with
SELECT t1.table_id FROM TABLE_ONE t1
WHERE t1.table_name = 'TEST_ONE'
SELECT t2.table_id FROM TABLE_TWO t2
WHERE t2.table_name = 'TEST_TWO'
SELECT tc1.table_id FROM TABLE_CHILD_ONE tc1
JOIN TABLE_ONE t1
ON tc1.table_name = 'TEST_CHILD_ONE'
|Oracle combine 2 rows into one with different join criteria|
you can use GROUP BY and get the maximum value among the Qcode value
based on customer id, transaction detail id, product id, transaction
version in your Sub query before the PIVOT
tqs.transactiondetailid as transactiondetailid,
q.productid as productid,
max(tqs.answer) as QAnswer,
|Oracle SQL - seconds between two dates, ignoring a maintenance window|
If my guess in comments is correct, then:
select a.id, max((end_time - start_time) * 1440) - sum(nvl((mend2 -
mbeg2), 0) * 1440) duration
from (select id, start_time, end_time, mbeg, mend,
case when start_time between mbeg and mend then
start_time else maint.mbeg end mbeg2,
case when end_time between mbeg and mend then
end_time else maint.mend end mend2
|Fill null values with last non-null amount - Oracle SQL|
Here is an approach. Count the number of non-null values before a
given row. Then use this as a group for a window function:
select t.item, t.year, t.month, t.amount,
max(t.amount) over (partition by t.item, grp) as new_amount
from (select t.*,
count(Amount) over (Partition by item order by year,
month) as grp
from table t
In Oracle version 11+, you can use
|Select Row with Max Value into join|
Looking at your schema it I'm guessing this is a one to many
relationship with SHELF in PARCELORDER being a foreign key to
STORAGE_ZONE in STORAGE_PLACE. Your query is grouping on the ID of
PARCELORDER which I'm guessing is the primary key on the table so you
still get one record for every record in the table.
What you want to do is group by SHELF so you only get one record per
|Can I use STUFF FOR XML PATH using a temporary table|
Use an alias for the table name:
SELECT STUFF((SELECT ',' + io.RowText
FROM @InOuts io
WHERE io.RowID = 1
FOR XML PATH('')
), 1, 1, '' )
SQL Server does not allow table (and column) aliases to begin with an
@. The simple solution is to use a simpler alias.
|How to get date as Header in SQL Server 2005|
Column headers can not be dynamic. You can see this from the fact that
usually you don't specify them as string, but like an identifier.
SELECT 1 as TestColumn
SELECT 1 as 'TestColumn'
You could do what you want in the form of a query you execute through
exec('SELECT 1 AS ' + CONVERT(nvarchar, getdate()))
|How to run a count in Entity Framework|
As dasblinkenlight mentioned, it seems that you do not want to return
something, so make the method a void.
You probable want something like this:
public void GetPictureCount()
int propertyId = Convert.ToInt16(ddlSelectProperty.SelectedValue);
var _db = new RESolution.Models.PropertyContext();
IQueryable query = _db.PropertyImages;
var mypic = (from c in _db.PropertyImages
|SQL (tsql) - joining|
The problem isn't the chaining of joins in general. The problem is
the or. I would suggest writing this as:
SELECT a.*, coalesce(b1.A, b2.A) as A, coalesce(c1.A, c2.A), . . .
FROM a LEFT JOIN
on a.A = b1.A LEFT JOIN
on a.B = b2.A LEFT JOIN
on a.A = c1.A LEFT JOIN
on a.B = c2.A LEFT JOIN
. . .
This should return similar results if you h
|raise_application_error exception handling in subprocedures|
The way I would implement this is to use RAISE_APPLICATION_ERROR where
the error actually originates, then leave it unhandled in the other
layers (or, if you want to do logging in the database, catch it in the
OTHERS section, then re-raise using RAISE rather than
You're OTHERS sections are still perpetuating the problem raised in
your previous question: when an unknown er