Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Query Syntax

50K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

I reinstalled SQL 2012 and SSMS 2017 (latest version).
When trying to deploy my local DB to Azure I get this error (Attached).

I had another computer with the same config and I was able to deploy. I must be missing some component.
I have Visual Studio 2017 Community also installed.
Screenshot_1.png
Screenshot_2.png
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Hi All,

Is it possible to check committed and not yet committed data 1-hours intervals before and after the designated timestamp “2017-12-19 06:35:18.0" in SQL Server DB 2014?

I want to fetch committed and not yet committed data of only main 5 tables not all tables in DB. The below command shows only current active details. I want to fetch designated timestamp.

SELECT * FROM sys.dm_tran_active_transactions
0
I have a table that has addresses
I need to delete the duplicates. I know there is a duplicate if the 'street' is exactly the same. I need to keep ONE of the rows that are duplicate, if there are no duplicates leave the record as is.

table = addresses
field = street

I tried this but did not work out:

DELETE FROM [Addresses] WHERE [Street] IN (
    SELECT id FROM [Addresses] GROUP BY id HAVING ( COUNT([Street]) > 1 )
)

Open in new window

0
hi,

i downloaded microsoft sql server 2012 express executable with work tool version from


when i try to run by clicking on that getting error as attached

please advise
sqlServer2012Express.png
0
I have a table called myUsers with 1000 UserID's.  This table is the result of a query.  

I have another table called EmailSent that records every email sent to a UserID and which EmailTemplate was used.

How do I get the subset of those 1000 myUsers that have not yet received  EmailTemplate = "Template1.cfm"

This is so I can send the remainder of the UserIDs, the email.

I could do this with spaghetti code but prefer to do it within a CFquery tag.
0
Hi experts,

I'm using sql server 2008.

I'm using the Employees table from the Northwind sample database.

Query 1A

Filter by EmployeeID

I have a query that works fine, it looks like this:

DECLARE @EmployeeID INT
SET @EmployeeID = -3
SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]
WHERE ( (@EmployeeID = -3) OR ([EmployeeID] = @EmployeeID) )

Open in new window


I'm using -3 to display all records.
When I set the @EmployeeID parameter to -3 the query returns all records like this:

p1.PNG
When I set the @EmployeeID parameter to 3 the query returns all records for EmployeeID of 3 like this:

p2.PNG
Query 1B

Filter by EmployeeID

I'm practicing writing dynamic sql queries.
So then I wrote this query. It works just fine. It gives the same results as Query 1A.

DECLARE @EmployeeID INT
SET @EmployeeID = -3

DECLARE @mSQL as varchar(5000)

---------------- Select ----------------

Select @mSQL = 'SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]' 

Select @mSQL = @mSQL + ' Where ( ([EmployeeID] = ' + RTrim(@EmployeeID) + ') Or (' + RTrim(@EmployeeID) + ' =  -3) )' 

---------------- Select ----------------

Exec(@mSQL)

Open in new window



Query 2A

Filter By LastName

So now I want to do the same query as Query 1A but filter by LastName.
I wrote this query and it works fine.

DECLARE @LastName VARCHAR(30)
SET @LastName = 'ALL'
SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]
WHERE ( (@LastName = 'ALL') OR ([LastName] = @LastName) )

Open in new window


I'm using the string 'ALL'  to display all records.
When I set the @LastName parameter to 'ALL' the query returns all records.
When I set the @LastName parameter to 'Leverling' the query returns all records with LastName 'Leverling.


Query 2B

Filter By LastName

So now I have the query that looks like this.
The results of this query should be the same as the results of Query 2A. But I'm getting an error.  
In my where clause where i set ALL i'm not sure of the syntax to use for a string.


Open in new window

0
I have a query that shows employee attendance. I created a cross tab query for this. The cross tab query shows the names of employees on the rows and the dates as the column headings. In the columns it show Present if atx_time_amount >0 and X if it is < 0. Only thing I realized is the atx_time_amount field will never be a negative number. It will always have a positive number or be blank if they are absent. So I am trying to figure out how to make it so the report shows present for atx_time_amount > 0 and X for a blank field. I added an attachment so you can see what I am working with.

THANKS!
Query.PNG
0
Hello All,

This is an extension of a question here - https://www.experts-exchange.com/questions/29071843/Display-Query-Results-in-Message-Box.html

I'm trying to "cache" the results of the input box and re-use it on reload of the script.

My thought is this:

Is it possible to send the result of objRecordSet("ID") to a spreadsheet, then put that result from the spreadsheet as the default data in the input box?

So, the below code works fine. It populates the fields as it should but I want to send the objRecordSet("ID") to the
spreadsheet and retrieve it from the spreadsheet at reload of the script to be input box default value - see bold font I added that's not part of the original script.

So, in effect each ID the user enters in the input box will be the default value when they re-run the script.

If there's another way to do what I'm asking, I'm all ears! I hope this makes sense, thanks!


strKey = InputBox("Enter Person ID","Search", MyDefaultValue)
If strKey = "" then
Field1 = ""
Field2 = ""
on error resume next
Else
strConnection = "Provider=SQLOLEDB;Server=;Database=;User ID=;Password="
strSQL = "SELECT * FROM Table1 where ID LIKE '%" & strKey & "%' or User_Name LIKE '%" & strKey & "%'"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open strSQL, objConnection, adLockReadOnly
do while objRecordSet.EOF = false
Field1 = …
0
We are using MS SQL 2012R2.  One of our tables has a field which is varchar(MAX), we are changing it to nvarchar(MAX) in order to allow non-latin characters to be entered.

We already tested and worked fine. My question is:  For all the entries we already have in that varchar field, will they be affected in ANY way if we change the field to nvarchar?
0
How can i do random balance in ms access? as like attached file
Bank-Statement.xlsx
0
What does it mean to be "Always On"?
LVL 5
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Hi,

How do I join these two databases?

db1: Visitors
Table: Users
This table contains all users on the system.  each one has a unique "UserID"


db2:  PT_UserData
Table: Client_Scores
This table contains only those users who have completed a test
If they complete a test, the field "YourType" IS NOT NULL
All rows contain the field "UserID"


Both tables have "UserID" in common

I'm looking to output those users who have completed the test, but I need their Email and LastName which is in the Users table
0
UPDATE_structure_code
SET ORGANIZATION = 'IT'+'&'+'DA'
WHERE ORGANIZATION = 'IT$DA';

I am getting error on the set statement - what is the proper syntax to include the ampersand in my text?
0
I have SQL server 2008 R2 running on my prod server. I heard Mainstream support for this product is already over an extended support going to over on 7/9/2019.  I decided to upgrade to new version.  Now SQL Server offering two types of licensing. Which one needs to choose? And how these Licensing will work?

    SQL Server Standard Edition (Server/CAL Licensing)
    SQL Server Standard Edition (Core-Based Licensing)
0
I have a Process_ID field where I only want to filter in a view all the starts with 6 or 9 only.
0
Hi,

I need to update old sql code(change schema,table,fields etc) with new schema ,table and fields  . I can do manually for few but have almost 150 of them and I don't want to do it manually. For example below code
SELECT  TOP 100 P.ProductID,
 P.Name,
 P.ListPrice,
 P.Size,
 P.ModifiedDate,
 SOD.UnitPrice,
 SOD.UnitPriceDiscount,
 SOD.OrderQty,
 SOD.LineTotal
FROM Sales.SalesOrderDetail SOD
INNER JOIN Production.Product P
 ON SOD.ProductID = P.ProductID
WHERE SOD.UnitPrice > 1000
ORDER BY SOD.UnitPrice DESC
source:https://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/

needs to be changed to below code(I have changed table,fields etc )

SELECT  TOP 100 P.ProdId,
 P.Name,
 P.ListPrice,
 P.Size,
 P.ModifiedDate,
 SOD.UntPrice,
 SOD.UnitPriceDiscount,
 SOD.OrderQty,
 SOD.LineTotal
FROM SalesRpt.SalesOrdDtl SOD
INNER JOIN Prod.Product P
 ON SOD.ProdId = P.ProdId
WHERE SOD.UntPrice > 1000
ORDER BY SOD.UntPrice DESC

how to approach this scenario? hmm i can use excel and do replace but that works for only single files..any idea?

Thanks in advance.

-
0
I have a vb.net App attached to a SQL MDF database.  Inno Script supports .net 45  The database is version 852.(2016+) MsSQLLocalDB will only run version 782database.   since inno script doesn't check for or install dotnet46, or the local db version, how do either:
ADD the new files to connect to this database or
Is there an existing script for the new version of dotnet.

OR should i consider a different installer.  This is not an area of my expertise and this will be installed on many different machines as a local APP.   I could use some advise.  Thanks
0
Hi
I have the following query
select min(odometer) as odometer_min, max(odometer) as odometer_max
From dbo.MyTable
Where gpsDateTime >= '2018-01-01' and gpsDateTime < '2018-01-18' and eventID = 1

Open in new window


this returns 2 values

What i actually need to do is return the difference between the min and max

What would the sql be for this
0
Hi

What is the most performant way to select the min and max value

here is my query

select (min odometer) and (max odometer) 
From MyTable
Where gpsDateTime between '2018-01-01' and '2018-01-17' and eventID = 1

Open in new window


i basically need  to return 2 item, the max odometer and min odometer reading based on the datetime
I'm looking for the most efficient SQL that can achieve this

SQL server 2014
0
Hi,

I am trying to create a form that only takes one string and pass it to a sp, but this parameter will look into several fields. This is my code, it works for only one field. Any ideas?  thanks

Select DISTINCT 
	  F1, F2
From T1   
Where
	((@AnySearch is null) or (F1 LIKE '%' + @AnySearch + '%')) AND
	((@AnySearch is null) or (F2 LIKE '%' + @AnySearch + '%')) 

Open in new window

0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I am upgrading SQL 2008 R2 to SQL 2016 Developer
Ran update advisor and took advice to install SQL 2008 service Pack 4, done successfully
However the feature rule step fails with Valid Database compatibility level and unsuccessful connection
The extended error message advises that:
The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility levels.
I knew nothing about the Reporting Service, but since then I have:
  • Checked Services that the SQL Server Reporting Services is started
  • Changed the security on SQL Server Reporting Services to Local system~Windows authentication
  • Stopped and started service
  • Run Reporting Service Config Manager as administrator.  It required a connection on opening.  Fails to Find connection to DESKTOP-PHACT and does not allow me to use other options.
  • Opened SSMS and opened report server.  Even run a Crystal type report (see attachment)  

Any suggestions?
SSMS_reportServer.jpg
0
I using the code below and would like to concatenate the C_COMPLETE_REQUEST_RECVD_DT in the following format;
FY YY

Output:

FY 02
FY 14
FY 14
FY 15
FY 15
FY 15
FY 15
FY 15


,Case WHEN EXTRACT(MONTH FROM C_COMPLETE_REQUEST_RECVD_DT) < 10 THEN EXTRACT(YEAR FROM C_COMPLETE_REQUEST_RECVD_DT) ELSE EXTRACT(YEAR FROM C_COMPLETE_REQUEST_RECVD_DT)+1 END AS FY

Open in new window

0
Hi All,
I am trying to migrate Goldmine companies with its hierarchies to another Db, does anyone did that before?
If yes, I need to know how you did that or what exactly represent hierarchies on Goldmine schema?
Also, I attach an image for a tree example that I need to migrate as is in the new Db.

Thanks, all.
TreeExample.png
0
How to migrate SQL Server 2008 to Azure Sql Server 2016 AlwaysOn.
Find the details below:

SQL Server 2008 (Standalone) which contains around 50-60 databases and total size of all these Db's around 80 TB.
Almost all database are in Simple Recovery mode. Only 5-8 databases are in Full Recovery.

Now we are palnning to migrate all these DB's to Azure (SQL Server 2016) and configure Always with 2 secondary replicas.
Server are ready in Azure. Next, migrating 80 TB and configure Always.

Please help us what it is procedure to migrate 80 TB data?
0
I have in table column named user_date, and I have jquery on frontend to get user status ADULT or YOUNG. When user pick the date, script automaticaly write ADULT or YOUNG, depending on his picked date and current date, = >18 or <18. Now I would, to get number of young users which for one month will be ADULT, and get that number like some alert.
0
I have a table that lists all possible combinations of class sections when we group them.

Example of existing table - three class sections have been grouped, 6134, 6135, 6136

YEAR      TERM      SECTION       CROSS_SECTION
2018      SPRING      6134         6135
2018      SPRING      6134         6136
2018      SPRING      6135         6134
2018      SPRING      6135         6136
2018      SPRING      6136         6134
2018      SPRING      6136         6135

I want to add a trigger on the existing table that will insert/update a custom table that identifies the class sections within a group.  I can create a table with unique group numbers, but am not sure how to identify the group:
YEAR      TERM      GROUP   SECTION
2018      SPRING      1              6134
2018      SPRING      1              6135
2018      SPRING      1              6136
0

Query Syntax

50K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.