w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » SQL » Page 5
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

Categories : SQL

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] From ______________ where Status='WAITING' ; IF (GETDATE() - @WAITTIME) > 0.02 . . . I presume that you have done the calculation and 0

Categories : SQL

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 AS SET NOCOUNT ON; EXECUTE AS LOGIN = 'sa'; -- do stuff REVERT; 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

Categories : SQL

How can I grant a user limited permissions in SQL Server 2008?
use DatabaseCatalogue go exec sp_addrolemember db_datareader, StudentAssistantsUsername go http://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

Categories : SQL

Google Spreadsheet month,year aggregation stops at november
Stef. 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..

Categories : SQL

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

Categories : SQL

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:

Categories : SQL

Slow query with multiple joins
Try a union instead, to begin with? SELECT IDENT_ID FROM tbl_IDENT I JOIN tbl_TA AN ON AN.IDENT_ID = I.IDENT_ID AND AN.anchor_point = I.c_id JOIN tbl_AB A ON A.A_ID = I.A_ID JOIN tbl_FULL_ARCHIVE FT ON FT.ARCHIVE_ID = I.ARCHIVE_ID WHERE (I.DATA_STATUS = 'ACTIVE' OR I.DATA_STATUS = 'INACTIVE') AND I.FD = 'Y' UNION SELECT IDENT_ID FROM tbl_IDENT I JOIN tbl_TA AN ON AN.IDENT_ID

Categories : SQL

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 ( SELECT TOP(100) [ID] = c.[ID], [Name] = c.[Name], [Keyword] = [colKeyword].[StringVal], [DateAdded] = [colDateAdded].[DateVal], ROW_NUMBER()OVER(PARTITION BY c.ID ORDER BY [colDateAdded].[DateVal]) rn FROM @cards AS c LEFT JOIN @cardindexes AS [colKeyword] ON [colKe

Categories : SQL

Count multible values sqlplus
My guess is that you want something like SELECT bookID, COUNT( copyNum ) number_of_copies, SUM( CASE WHEN date_out IS NOT NULL THEN 1 ELSE 0 END ) number_copies_out, SUM( CASE WHEN held_for_CID IS NOT NULL THEN 1 ELSE 0 END ) number_copies_reserved FROM book_copy GROUP BY bookID

Categories : SQL

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

Categories : SQL

SQL query to delete databases with specific string
Warning This is a very dangerous practice. Consider renaming the tables instead of delete. Answer 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 Where Table_Na

Categories : SQL

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 ? SELECT t1.accm_pfx, t2.accm_pfx, t1.accum_no, t1.accm_value FROM table1 t1 INNER JOIN table2 t2 ON t1.accum_no = t2.accum_no and t1.accm_value = t2.accm_value

Categories : SQL

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)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Name VARCHAR(MAX) DECLARE CUR CURSOR FAST_FORWARD FOR SEL

Categories : SQL

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.

Categories : SQL

violated - parent key not found error
ORA-02291: integrity constraint (BASE.FK_DEPT) violated - parent key not found 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 ='Math')); Try this: insert into base.EMP1(ide,nome,rue,ville,refdept) select 7,'EMP5','agadir','dddd', idd from base.DEPT1 where

Categories : SQL

SQLAlchemy : FOR XML PATH equivalent
You can execute raw SQL query in SQLALCHEMY. Based on SQLALCHEMY doc and this, Try something like: # pymssql engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname') raw_con = engine.raw_connection() try: cursor = raw_con.cursor() # add your proper SQL code here cursor.execute('(SELECT DESCRIPTION FROM WEV_STY PS WITH(NOLOCK)

Categories : SQL

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.

Categories : SQL

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.

Categories : SQL

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_Mid_Name VarChar2(20), Cust_Last_Name VarChar2(20) NOT NULL, Account_Type Varchar2(15), Cust_Email VarChar2(30), Cust_Mobile Number(10), Bank_Id NUMBER(4), Account_No Number(4

Categories : SQL

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'

Categories : SQL

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 distinct: SELECT DISTINCT t1.columna, t1.columnb,

Categories : SQL

Insert into with conditional constant
You could use a case expression: insert into Table(a, b, c) select a, b, CASE WHEN a = b THEN 'Enum1' ELSE 'Enum2' END from OtherTable, YetAnotherTable where a > 0

Categories : SQL

SQL WHERE IN ... to JOIN table
WITH JOIN , you query would look like SELECT sum(CF.SUM) AS SUME, strftime('%Y - %m', CF.CheckDate) AS CheckDate FROM PriceList Join Category on PriceList.CATEGORY = Category.UniqID AND Category.UniqID = 2 JOIN CheckDetail CD ON CD.NUMBER = PriceList.UniqID JOIN CheckFinal CF

Categories : SQL

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

Categories : SQL

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 that. 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 ( SELECT 3

Categories : SQL

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, coalesce(t2.someint2,0)-coalesce(t1.someint2,0) 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

Categories : SQL

relative value of char variables
You could do it like this: select a.organic,a.groupedcount/b.totalcount * 100 as percentage from (select organic,count(*) groupedcount from products group by organic) a, (select count(*) totalcount from products) b Or if you don't want to use ANSI-89: select a.organic,a.groupedcount/b.totalcount * 100 as percentage from (select organic,count(*) groupedcount from products group

Categories : SQL

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

Categories : SQL

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 COMMON_TABLE SELECT table_id FROM ( SELECT t1.table_id FROM TABLE_ONE t1 WHERE t1.table_name = 'TEST_ONE' UNION SELECT t2.table_id FROM TABLE_TWO t2 WHERE t2.table_name = 'TEST_TWO' UNION SELECT tc1.table_id FROM TABLE_CHILD_ONE tc1 JOIN TABLE_ONE t1 ON tc1.table_name = 'TEST_CHILD_ONE' AND

Categories : SQL

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 select tqs.transactiondetailid as transactiondetailid, q.productid as productid, max(tqs.answer) as QAnswer, tqs.tra

Categories : SQL

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

Categories : SQL

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 ) t; In Oracle version 11+, you can use

Categories : SQL

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 SHELF. Try this

Categories : SQL

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.

Categories : SQL

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 instead of SELECT 1 as 'TestColumn' You could do what you want in the form of a query you execute through exec exec('SELECT 1 AS ' + CONVERT(nvarchar, getdate()))

Categories : SQL

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

Categories : SQL

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 b1 on a.A = b1.A LEFT JOIN b2 on a.B = b2.A LEFT JOIN c1 on a.A = c1.A LEFT JOIN c2 on a.B = c2.A LEFT JOIN . . . This should return similar results if you h

Categories : SQL

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 RAISE_APPLICATION_ERROR. You're OTHERS sections are still perpetuating the problem raised in your previous question: when an unknown er

Categories : SQL




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.