spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » SQL » Page 10
Calculation within the subquery, substract an amount from a different table
I came up with the following horrendous mess. There is some hope that your DBMS's query optimizer can do something reasonable with it, in part because it avoids correlated subqueries. Indeed, there is even some hope that your DBMS will accept the query more or less as written. SELECT MonthlyForecast.Month AS ForecastMonth, MonthlyForecast.Skill AS Skill, (MonthlyForecast.Total + COALESCE(

Categories : SQL

Search and Replace Byte Order Mark In Sql Server
What about this CTE: StripBOM AS ( SELECT CASE WHEN LEFT(text,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(text, 4, LEN(text))) ELSE text END AS text FROM BPM_Letters ) It should provide you with a new table where all BOM characters have been stripped off. P.S. This code assumes 'text' field is of type varbinary.

Categories : SQL

SQL Count Total ROWS for an ID
You need to use the HAVING clause with your GROUP BY statement, e.g. SELECT StaffID, COUNT(*) FROM StaffAbsence WHERE StartDate > DATEADD(MONTH, -6, GETDATE()) GROUP BY StaffID HAVING COUNT(*) > 3

Categories : SQL

SQL DB2 - Select query based on a column value unique to another column
You can approach this with aggregation and a having clause: select qa.id from question_attributes group by qa.id having min(Attribute_ID) = 6 and max(Attribute_ID) = 6;

Categories : SQL

find partial duplicated rows in a SQL table in IBM netezza database
The following query produces the output you want. Is this what you want to do? SELECT id1, id2, COUNT(*) AS value FROM myTable GROUP BY id1, id2; EDIT: If you want complete duplicates (of all columns) but only to show the first two: SELECT id1, id2, COUNT(*) as value FROM myTable GROUP BY id1, id2, id3, id4, id5, id6; You can add having count(*) > 1 if you only want examples with du

Categories : SQL

Reference a table from a scalar UDF - tied to schema name?
Your question is really two questions: Does my function need to have the same schema as the tables it's selecting from? The answer is no. dbo.MyFunction() can select from test.MyTable just fine. However, you may run into issues with ownership chaining -- different schemas mean explicit permissions must exist for the user to select from the table. See Books Online for details. Can I invoke a func

Categories : SQL

Where do I put WHERE command on complex sql command
SELECT to_char(IlkTarih, 'yyyymmdd') AS EXPR1, SUM(Toplam) AS ToplamDosya, ROUND(SUM(TekilBoyut) / (1024 * 1024 * 1024), 2) AS TekilBoyut, ROUND(SUM(ToplamBoyut)/(1024 * 1024 * 1024), 2) AS ToplamBoyut FROM (SELECT DOSYA, MIN(TARIH) AS IlkTarih, 1 AS Tekil, COUNT(*) AS Toplam, MAX(BOYUT) AS TekilBoyut, SUM(BOYUT) AS ToplamBoyut FROM INETISLE.XFERLOG WHERE DA

Categories : SQL

Connecting SQL Developer 1.5.4 to tnsnames.ora
I don't think you can. At my work, the DBAs years ago started keeping the current TNSNAMES.ORA on a network share, but we still had to maintain local versions for people using SQL Developer until relatively recently. To be honest, I don't remember which version the option was introduced to select a directory, but I would be quite confident it was later than the version you're on. As JaMaBing says,

Categories : SQL

How to populate a table with 5 million rows in MS SQL Server?
;WITH Numbers AS ( SELECT TOP (5760000) IdColumn = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 CROSS JOIN sys.all_objects AS s3 ) INSERT INTO dbo.YourTable SELECT IdColumn, RIGHT(REPLICATE('0',26)+CONVERT(VARCHAR(26),IdColumn),26) CodeColumn, GETDATE() DateAndTimeColumn FROM

Categories : SQL

SQL- Finding the users with the Maximun average score
select userid from (select userid, rank() over (order by avg(score) desc) rw from sample group by userid) where rw = 1; Calculate avg score for each user, than calculate the rank of each score using analytic functions (which are performed after the grouping). Lastly get the rows with the first rank

Categories : SQL

db2 specific query for count of distinct rows
If DB2 supports ansi standard sql I believe all you need is to add acountandgroup by: SELECT COUNT(DISTINCT ORDER_NUMBER) AS NUM_ORDERS, trdm as TRADEMARK, cmdnsm as CUSTOMER_NAME FROM DB1.ORDFA JOIN DB2.CCADF ON SFORD# = WDRSO JOIN DB2.DADA06 ON WD3ADF = WD3FLA JOIN DB1.CMP1 ON C1LK2 = SF2LK WHERE YEAR = '14' AND PART = '5484' GROUP BY trdm, cmdnsm ORDER BY NUM_ORDERS DESC, CUSTOMER

Categories : SQL

how to bind sql data to a label in a dialog result vb.net
Here is the final code Private Sub Button1_Click(sender As Object, e As EventArgs) Handles M01.Click Try Dim dt As New DataTable Using conn1 As New SqlConnection(connstring) conn1.Open() Using comm1 As SqlCommand = New SqlCommand("SELECT Shear FROM Production.dbo.tblFabWipLog WHERE FabWipID = 1", conn1) Dim sqlda As New SqlDataAdapter(

Categories : SQL

sql aggregating the selection results
Try this query: (As you want to combine the result of two separate answers from queries) SELECT b.User, a.Result1, b.Result2 from (select user, result1 from....) a full outer join (select user, result2 from....) b where a.user = b.user

Categories : SQL

SQL get number of hours on previous rows
This answer assumes MySQL since the question was not tagged with Oracle to begin with. I'm leaving this answer here, since it might inspire someone with Oracle skills toward a solution... Assuming a location always opens before it closes, a quick and dirty solution could look like this: SELECT RTL_LOC_ID, DATE(BUSINESS_DATE), MIN(BUSINESS_DATE) AS [OpenTime], MAX(BUSINESS_DATE) AS [Clos

Categories : SQL

Sql serverrRecursive query for BOM not showing child parts
OK, your updated explanation helped a lot. I think I have it: ;WITH SOCTE (Part) as ( SELECT DISTINCT SO_Items.Part_No FROM SO_Items WHERE (SO_Items.Fully_Del = 'N') ), CR (Part, SO_Part, Component, Step) as ( SELECT SOCTE.Part, SOCTE.Part AS "SO_Part", Components.Component_Part_No, 0 FROM Components JOIN SOCTE ON Components.Parent_Part_No = SOCTE.Part UNI

Categories : SQL

SQL property from inherited Parent-Child relationship
use a MSSQL functions. It will drastically improve performance comparing to nested and recursive queries. e.g. Function: CREATE FUNCTION getUserList (@inputUser VARCHAR(250), @inputDate VARCHAR(250)) RETURNS @UserList TABLE ( userName varchar(250), ) AS BEGIN INSERT @UserList SELECT a.Substitute_User, FROM Table2 AS a WHERE a.User = @inputUser AND a

Categories : SQL

Count how many percent of values on each column are nulls
Think there is not built-in features for this. You can make this self Just walk thorough each column in table and calc count() for all rows and count() for rows where column is null. There is possible and optimize this for one query for one table.

Categories : SQL

Can I avoid doing this Row by Row
You can do it in SQL by just adding the time constraint to your JOIN: SELECT us.UsersShiftsId, us.UserId, us.ShiftStart, us.ShiftEnd, MIN(fs.ScannedTime) ActualStart, MAX(fs.ScannedTime) ActualEnd FROM UsersShifts us LEFT JOIN FingerprintScan fs ON us.UserId = fs.UserId AND fs.ScannedTime BETWEEN us.ShiftStart AND us.ShiftEnd GROUP BY us.UsersShiftsId, us

Categories : SQL

Only want the first if several matches are returned
You can probably use ROW OVER analytic function for this like below, then collect only those row for which Row value is 1. ROW_NUMBER() OVER(PARTITION BY exerciseindex ORDER BY exerciseindex) AS Row For your case, it would be (You can use a CTE for that purpose) WITH Query1 AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY exerciseindex ORDER BY exerciseindex) AS Row FROM ( select distinct eb.exe

Categories : SQL

How range queries identified attribute name in analytic function oracle?
PARTITION BY deptno defines groups ORDER BY sal defines the order of rows in each group RANGE BETWEEN [start] AND [end] defines a window for each row in each group. You cannot specify this clause unless you have specified the ORDER BY. Some window boundaries defined by the RANGE clause let you specify only one expression in the ORDER BY. RANGE specifies the window as a logical offset. It looks

Categories : SQL

Choosing the view query at runtime (postgres database)
Just OR them together into the WHERE-clause. The optimiser will find the invariant conditions. CREATE VIEW my_view AS ( SELECT * FROM X WHERE ( cast(current_setting('first_select') as int)=1 AND <condition1> ) OR ( cast(current_setting('first_select') as int)=0 AND <condition2> ) ... ) ;

Categories : SQL

Database Recovery Model alter script error
Don't just gloss over the problem: understand it. It's because you have some sort of HA (you said mirroring in the comment above, but availability groups (for sure) or log shipping (I think) would also cause this) that requires that the database be in full recovery. When you try to set it to simple recovery, it fails because you can't set the recovery model to anything but full for such a databa

Categories : SQL

Postgresql: select MAX value from two joined tables
I'm not sure why the most recent status for 91 would be on_track. But, you can do what you want using window functions. My guess is that you don't even need the month_digest table, because the ids would normally be in time order. select s.project_id, s.status as latest_status from (select s.*, row_number() over (partition by project_id order by month_digest_id desc) as seqnum

Categories : SQL

Oracle-SQL with multiple where-conditions extremely slow
There are two constructs you are using that tend to be associated with slow response. One is the use of subqueries (the target of the "in" operator). The other is the OR operator. You can often rewrite a query that uses IN with a subquery as an equivalent query with a JOIN. The result is often faster. Sorry, but I'm too lazy to do that for you in this case. You may have no choice but to us

Categories : SQL

How to change subQuery to Co-related Subquery?
I think this is what you're looking for. Just move the query into the FROM section and use a JOIN to filter the results based on the same conditions you had in the WHERE clause, except this time you reference the result set (RS) returned by the subquery. SELECT FirstName, LastName FROM AdventureWorks.Person.Contact a INNER JOIN [AdventureWorks].[HumanResources].[Employee] b ON a.ContactID

Categories : SQL

Apply a single case statement to all columns in sql
I have around 40 such columns in my table. Do i need to write " case when sum(col n) then..." 40 times in my query? Short answer: Yes. Longer answer: You might be able to use some kind of dynamic SQL to generate the statement automatically from the column metadata. But it might not be worth the trouble, as you can often just as easily copy-paste the statement in your query editor. All thin

Categories : SQL

Hive: substract one table from another?
Assuming you have Hive 01.3 version or later, you can use not exists clause: select * from old_books a where not exists ( SELECT 1 FROM old_books b JOIN new_books c ON (b.title=c.title) where a.book_id = b.book_id); Here is the reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries

Categories : SQL

How to find the default location in which Oracle DBF files are created?
DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles (see its entry in the Database Reference). You can retrieve its value with the following SQL query: select value from v$parameter where name = 'db_create_file_dest' To access the v$parameter view a user needs at least the SELECT_CATALOG_ROLE role.

Categories : SQL

How can I do a Trigger in SQL?
use this trigger, or split insert / dalete / update: Code: CREATE TRIGGER [dbo].[t_Update_Services] on Sales AFTER UPDATE,INSERT, DELETE AS BEGIN UPDATE a SET SalesTotal = SalesTotal - b.Amount FROM Services a JOIN deleted b ON a.ServiceID = b.ServiceID UPDATE a SET SalesTotal = SalesTotal + b.Amount FROM Services a JOIN inserted b ON a.ServiceID = b.ServiceID END

Categories : SQL

SqlCommand: Incorrect syntax near the keyword 'OVER'
Your ROW_NUMBER-"column" comes after the JOINs. Change it to: string sql = @" WITH TempTable AS ( Select JobDetails.JobCode, JobDetails.CurrentStatus, MasterModel.Name As ModelNumber, MasterModel.Code As ModelCode, MasterBrand.Code As BrandCode, MasterBrand.Name As BrandName, MasterDeviceType.Code As DeviceCode, MasterDeviceType.Name

Categories : SQL

Working days SQL query
Try this. If you have Unique ID's try this. create table #calen(ID int, FromDate date, ToDate date,isChargeable char(1),Username varchar(20)) INSERT #calen VALUES (1,'2014-11-03','2014-11-04','Y ','AUser'), (2,'2014-11-04','2014-11-06','Y','AUser'), (3,'2014-11-07','2014-11-07','Y','AUser') SELECT a.FromDate,a.ToDate,a.isChargeable,a.Username, CASE WHEN a.From

Categories : SQL

SQL Dynamic ASC and DESC
If the column you are sorting by is numeric then you could do this: order by case when :dir='ASC' then numcol ELSE -numcol END For a date column you could do: order by case when :dir='ASC' then (datecol - date '1901-01-01') else (date '4000-12-31' - datecol) end I can't think of a sensible way for a VARCHAR2 column, other than using dynamic SQL to construct the query (which wo

Categories : SQL

Select distinct returns duplicates
There are multiple columns in the select query, the query check for uniqueness across all the columns, not just the one in brackets. The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.. Please note it not just columns If you modify your query to this: SELECT DISTINCT (TK.TICKETID), TK.DESCRIPTION, TK.CR

Categories : SQL

Improving recursive SQL looping
You could use a common table expression for this: WITH Antecedents (ITemID, ParentItemID, Level) AS ( -- Anchor member definition SELECT ItemID, ParentItemID, 0 AS Level FROM Items WHERE ItemID = @StartingID UNION ALL SELECT ItemID, ParentItemID, Antecedents.Level + 1 AS Level FROM Items INNER JOIN Antecedents ON Antecedents.ParentItemID = Items.ItemID ) SELECT

Categories : SQL

powerpivot inner join
Couple of options: Add a column to your main table that uses a formula to pull in the Country Name from your LookUp Table e.g. =RELATED(LookUpTable[Country Name]) If you drag in any measure that references the main table you will get your desired result e.g. =COUNTROWS('MainTable') You then hide the results column if you had to.

Categories : SQL

Characters with accents not interpreted correctly during update on sql server
Turned out it was pretty simple. All I had to do was add 'N' infront of the text to explicitly state that it was nvarchar. Working solution update dbo.tbl_romanian_test set title = N'ă ț î' where Id = 1

Categories : SQL

SQL query to select records which must have all values from another table as substring
One approach is: Select r.RuleValue from Rules r join Input i on r.RuleValue like '%' + i.RulePart + '%' group by r.RuleValue having count(distinct i.RulePart) = 3 -- or (select count(*) from Input ) UPDATE More elegant way is using NOT EXISTS to represent for ALL select * from Rules r where not exists ( select * from Input i where r.RuleValue n

Categories : SQL

How to retrieve column name and its value from cursor
You would need to use the DBMS_SQL package instead of a simple FOR loop cursor. It's quite complex, but luckily Tom Kyte has already done all the work here

Categories : SQL

Pass id to inner SELECT of nested query with ORDER BY and ROWNUM
(SELECT CurrencySymbol FROM ( SELECT OP2.FK_ORDER, CURR.CURRENCYSYMBOL AS CurrencySymbol, ROW_NUMBER() OVER(PARTITION BY OP2.FK_ORDER ORDER BY P2.VALIDFROMDATE DESC) RW FROM MY_ORDER_TABLEPOS OP2 JOIN MY_ORDER_TABLE O2 ON OP2.FK_ORDER=O2.ID JOIN MY_PRICE_TABLE P2 ON OP2.FK_CONCRETARTICLE=P2.FK_ARTICLE JOIN MY_CURRENCY_TABLE CURR ON P2.FK_CURRENCY=

Categories : SQL


Recently Add
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
SQL Server 2008 Agent jobs. Two procedures and a view
Can't create table in Oracle Developer
Trigger error "ORA-01403: no data found" when It is not fire
SQL: alternatives and substitutions for GROUPING SETS and PIVOT
Excel Vlookup too Slow
Is WHERE field IS NULL quicker than >0
BFILE error on oracle
SQL Server : Select from two tables with default value 0
Use of uninitialized value after fetchrow()
Why did this UPDATE change all rows instead of just one row?
Oracle / SQL Counting Different values from one Column and storing it in a variable
How can I calculate the percentage of occurrences of a value in a column?
To compute sum regarding to a constraint
Enterprise Architect - Execute SQL Issue
First business day of the current month - SQL Server
Incorrect no. of rows fetched in SQL
SQL query design with configurable variables
Grouping dates by dateadd and datediff
SQLServer get top 1 row from subquery
SQL Using CONTAINS() doesn't work for AND & OR
SQL Server: Persisting computed column
Improve SQL Server query
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.