[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Query Syntax

52K

Solutions

20K

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

Hello,

I want to be able to create a SQL trigger on my database to email me when a transaction is made and the margin is less than 20%.
A previous IT professional had created this trigger (which I think is the fundamentals) however it was never finished and I am unsure how to finish it off so it runs then emails me.
Are you able to help?

The example trigger is:

USE testdatabase
If EXISTS (SELECT name FROM sysobjects
WHERE name = ‘trig_mail’ AND type = ‘TR’)
DROP TRIGGER trig_mail
GO
CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE, DELETE
AS
Select from inserted where (ProductCode <> ‘Freight’)
@percentage = (Localcost-LocalUnitPrice) / (LocalUnitPrice*100)
If (percentage < 20)
Begin
Exec master.xp.sendmail ‘EmailID’, ‘EmailMessage’
END
GO

When I run the query I get:

Msg 102, Level 15, State 1, Procedure trig_mail, Line 6
Incorrect syntax near '@percentage'.
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

here is some sample data from a sql server table named orders

orderid orderamount custid
1             300                  5
2             150                   7
3              165                  7

trying to write a query that will only return rows that do not have more than 1 occurrence of same custid

using sample data above

would only want

orderid orderamount custid
1             300                  5

any ideas?
0
Hi,

I need following result set regarding SQL Server 2008 R2 Jobs status.

Job Name, Schedule Name, Frequency, Interval, Time, Next Run Time, Job Last Run Status for particular date.

Example: Please provide Job 'XYZ''s above info for 31/01/2018.

Thanks,
Chandra
0
This is a related question that Dustin and Ste5an helped me with
https://www.experts-exchange.com/questions/29129110/Replace-works-but-is-there-a-better-way.html?headerLink=workspace_answered_questions

I'll go step by step

1. Please run the attached script. It creates the sample data

2. This works. 1265 rows. However, you see I have "@seachtext" and "@searchtext2".

DECLARE @searchtext NVARCHAR(4000) = '%019%'

declare @searchtext2 nvarchar(4000) = '%O19%'

DECLARE @results TABLE(SearchText NVARCHAR(4000))

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

	 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )

 INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

[dbo].[camillaforEE] AS u
	-- INNER JOIN @results r ON u.SerialNo LIKE @searchtext
where

		  (u.[SerialNo] LIKE @searchtext)
		  OR
           (u.[SerialNo] LIKE @searchtext2)
        
          OR
		   (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )

DROP TABLE #unitsearch

Open in new window


3. My question above helped with replacing zero with O and vice versa. So, I removed @searchText2 and use the @result table to join. But, now I get an error that there's a duplicate for row 1518. I don't want to use "distinct" or remove "primary key". I think this isn't the right solution.

Any ideas why this doesn't work?


Open in new window

0
Hi

I'm trying to run a batch file from a SQL Script but error occurs.

Script:
EXEC xp_CMDShell 'C:\Users\Simon\Desktop\Stadium\Stadium Insert\Integration Services Project7\refresh.bat'

Error:
output
'C:\Users\Simon\Desktop\Stadium\Stadium' is not recognized as an internal or external command,
operable program or batch file.
NULL
0
Hello experts!

I need to make a joined query in PHP to get data from one table, depending on which user makes the request. In a previous question, I learned how to do exactly that but, this time I also need to get data from a second table. This is my schema:

DB schema
What I need can be described in the following scenario:

- User John (user_id 1) needs to see all the entries to which he has access to.
- PHP fetches him all the entries because the matrix table says that users with the user_category 1 can see every entry from entry_category 1 and 2.

I've got so far. The problem is that I also need the info for the author of the entry. The way I'm getting it, it brings me the entries and the info for the user requesting it. I guess (a wild guess) I need to add an OUTTER JOIN or something like that to my query? This is what I have:

SELECT entry.*, user.*
FROM entries entry
INNER JOIN matrix mx ON entry.entry_category = mx.matrix_entry_category
INNER JOIN users user ON mx.matrix_user_category = user.user_category
WHERE user.user_id = :uid
ORDER BY entry.entry_id ASC
LIMIT 10

Open in new window

Later, the :uid param will be binded to the user requesting the entries (browsing the page).

This will give me a multidimensional array with one key for each entry and user info, but not the user that wrote the entry. It will just repeat 10 times the info for the person browsing.

What I need would be something like this:

Array
(
    [0] => Array
     

Open in new window

0
I created a scalar function to encrypt the plaintext CreditCardNumber using this SQL function with a strong phrase and varbinary "salt" of tableid and recordid.  The server is also encrypted and physically secure...the passphrase is stored in a text file that requires admin user account to access and must be read each time the function is executed.  

The decrypt function that makes it to front end UI is masked with only the last 4 showing.

Is this enough to be compliant?
0
Can someone please help me to change my query slightly?

I have a simple database table  which is named "City". It includes the id of the district, and last names of people in the district.
As you can see in the sample table the last names occur more than once, as there are many people in the city with the same last name in different districts.

I have a query which counts the number of unique names in the city by district. The name will not be counted more than once. Meaning if it was already counted in a previous district, it won't be counted again. So for example you can see in the query result that last name "Marconi" was only counted once, as it was counted in District 1, but not District 2.

The query works great, but it needs to be modified slightly so that it will also display the total of all of the names in a district, regardless if there are repeat names in a district. Please see the "Desired result" below to see what I need my query to produce. Attached is a copy of my table with data which can be recreated in sql server by running the attached script. My sql is very generic, so that it runs on other sql platforms like Sybase and Oracle.


My Working Query :
select t.Cityid, t.DiD, count(FamilyName) AS DistinctFamilyNames
from (
    select
            CityId, MIN(DistrictId)AS DiD,FamilyName  
    from City WHERE CityId = 'Austin' Group By CityId,FamilyName
    ) t
group by t.CityId, t.DiD;



City table :

CityId  DistrictId        …
0
help with a pivot table.  
i have a query that will correctly pivot on all the unique instances of variables for the specified column except for variable.
This variable is a blank.   i can't get it pivot for that column.
Please look at the line that has the yellow highlight in the attachment.   The data in the column doesn't have null fields.
pivot-table.JPG
0
I don't know if there's another way of doing this besides what I have in mind and I'll note it below...

Some users have access to all Scheds and some users only to specific ones. When FilterSched is false, it means user has access to everything and has to get all the rows. If FilterSched is true, means user has to get all the rows.

I'll go step by step with an example.

1. I create the temp tables
CREATE TABLE #Main -- main table that has all the rows
(
  SchedID int
)

CREATE TABLE #Authuser -- all the users
(
  ID INT,
  FirstName VARCHAR(100),
  FilterSched bit
)

CREATE TABLE #AuthuserSched -- users and the scheds they have access to
(
  ID INT,
  SchedID int
)

 CREATE TABLE #FilterScheds -- holds list of scheudles user has access to
 (
   SchedId int
 )

Open in new window


2. Populate tables

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 88, 'Edward', 1 -- has filter and will get data with the INNER JOIN I have because #FilterScheds table holds whatever Sched this user has access to

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 14307, 'Camillia', 0 -- doesn't have filter so this user gets all the data (*** but that INNER Join is going to fail because #FilterScheds doesn't get populated)

----

INSERT INTO #Main -- all my data
(
    SchedId
)
SELECT 9999

INSERT INTO #Main
(
    SchedId
)
SELECT 555

INSERT INTO #Main
(
    SchedId
)
SELECT 4567

---

INSERT INTO #AuthuserSched -- user 88 has access to Sched 9999
 SELECT 88,9999

Open in new window


4. Now I join to get the data for user (I have this in a stored proc )

 --1. see if this user has a filter
 DECLARE @filter BIT
 DECLARE @userId INT = 88 --- run it for user 88 first, then user 14307
 
 SELECT @filter = filtersched FROM #Authuser
 WHERE id = @userId

 -- 2. now that user has filter, get their Scheds
 IF @filter = 1 --user 88 needs filtering
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
  
  ---3. now get the results. I have all Scheds user has access to. So, I'm doing an inner join

  SELECT * FROM #Main
    INNER JOIN #FilterScheds ON #FilterScheds.SchedId = #Main.SchedID --ISSUE is here --*** for user 88, I only need what Scheds he has access to. For user 14307...users that don't have filter...all the rows should come back
	                                                                      -- 
  
  TRUNCATE TABLE #FilterScheds -- clear the table and run it again for user 14307

Open in new window


My solution is to do this. A better way of doing this?

IF @filter = 1
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
else
 begin
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #Main --- get all the scheds that MAIN has for users that have access to everything. So, #FilterScheds is always populated
end 

Open in new window

0
Amazon Web Services
LVL 12
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Hello,

I have to select all items from a MySQL table using PHP but from categories that the user is allowed to see. I've tried having all the categories in an array and then imploding them while using the IN statement in the SQL query (tried like 10 different methods with INT and STRING arrays) with no luck. The problem is that I'm getting dizzy while trying to figure out a somewhat complex (at least for me) JOIN statement.

I have the following tables:

Sample tables
As you can see, it should be pretty easy except that a user may have more than one category assigned to it. How can I show Helen in this example all the products and/or categories she can see in a single query?

Thanks in advance.
0
I just upgraded a series of SSRS reports from SQL 2014 to SQL 2017. For some reason on two of these reports SSRS is inserting page breaks in between the groupings. There are no breaks and there were none when displayed using SQL 2014. What can I can examine that would explain this? I am using VS 2017 Community to deploy the reports.
0
Looking for a query to query all non-system tables in an Oracle 11g database for a given value
Additionally, If you can given a flavour of the same query which can search all the table starting with "ENV"  or "DOC"  , it is much appreciated
0
Hello all,

I often calculate frequencies of values in SQL as follows:

SELECT MyField, COUNT(*) AS [Frequency]
FROM MyTable
GROUP BY MyFIeld

This is a simple quick thing to do and I can type it out in a couple seconds when I'm working through a file trying to get a sense of what's in there.  

What I would really like to do is to add the Percentage to my results (just as quickly).

Is there like a secret trick to be able to do that?  

TIA!
0
Hello!

I have a simple SQL query in a c# project that I have to convert to LINQ but am struggling with it due to my very limited experience and understanding of LINQ queries.  What's stumping me is how to handle the summing and grouping.  This is a pretty time sensitive project for me and once I see how it's done I'll be able to understand and build on it.
Here is the query:
SELECT Dept, Shift, ShiftDate, FLOOR(SUM(RunningGoal)) AS Target, SUM(UnitsProd) AS Produced 
FROM Vw_PRODCOUNTER_HourlyProduction 
WHERE Dept = '3500A' AND Shift = '1' AND ShiftDate = '12/13/2018' GROUP BY Dept, Shift, ShiftDate

Open in new window

Thanks for your time and help in advance.  Have a good day!
JeffH
0
In SQL Server 2012, Is there a way to create a table variable that references a table already in the database?  Note: I have a string variable that contains the name of the table.

DECLARE @tablename As varchar(MAX)
DECLARE @TB As Table

SET @tableName = 'table1'
SET @TB = table with name 'table1'
SELECT * FROM @TB

Note: I'm asking if I could do this without dynamic sql,  I already know that I could do 'EXEC sp_executesql 'SELECT * FROM ' + @tableName.
0
Hello Expert,

Need to identify all tables in database
that begin with prefix  'D_'.

In PL/SQL (Oracle 12c) have tried

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE
WHERE TABLE_NAME LIKE 'D_%'

SELECT table_name
FROM INFORMATION_SCHEMA.tables
WHERE name = 'd_%';

Both of these return
'Invalid table name'

Think these may be for SQL Server

Tried

SELECT TABLE_NAME
FROM ccm.user_tables
WHERE TABLE_NAME LIKE 'D_%'

This returns 'Table or view does not exist'

Thanks.

Allen in Dallas
0
I can't figure out why this SQL brings back the same amount of rows with and without the where caluse (been at it for a day...took joins out, etc and I still can't figure it out). I'll keep at it and if I figure it out, I'll post back.

1. I can't attach the SQL here. It's too big to attach here. You can download it from here and create the table with the data. https://drive.google.com/open?id=1WVbU7EeC-r748RM8dVa74guQvR-DAxbk
   It's 55645 rows of data inserted into a table.

2. This works fine. Brings back 53678 rows with the "where" clause

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 

  (
       ([Sched] LIKE @searchtext)
		 OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
        OR 
		(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN 

Open in new window

0
The database has a set of tables that, when an user clicks the Edit button,
creates a draft copy that preserves a copy of the record in case the user
discards the edit. The d_address table mirrors the address table.
The d_employer table mirrors the employer table. There are dozens of
pairs.

For rather complex reasons drafts that are older than a couple of months cause
primary key constraint violations and errors in the front end application.

So the boss says delete all drafts older then three months.

A SQL script is written:
Delete FROM
    ccm.d_address
   where trunc(last_update_date) < '03-SEP-18'

And so several dozen of these scripts should be
written. All the table begin with d_  with syntax
d_tablename.

Is there a way to loop thru (or other method) the tables deleting older
draft records?  

Thanks.

Allen in Dallas
0
Become a Microsoft Certified Solutions Expert
LVL 12
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Is there any way a simple SQL Delete statement using a WHERE numeric column = numeric value can result in an Arithmetic overflow error converting numeric to data type varchar without a trigger?

delete OrderMeetings  where OrderMeetings.ORDER_NUMBER = 71935.00
Arithmetic overflow error converting numeric to data type varchar.

Open in new window


I have a customer saying that they are getting errors when deleting a row in a table. They can literally go into SQL and run a delete statement - as shown above - and it gives an Arithmetic overflow error converting numeric to data type varchar. They swear there are no triggers on the table. They sent us a copy of their database - no triggers but we can't replicate it either. However they say it always happens every time for them.

The ORDER_NUMBER column is numeric(15,2)

Any ideas?

They are using Microsoft SQL Server 2016.
0
Hi
Finally, after buying a second-hand book on SQL Server two days ago I managed to spot a recommended code on linking access app to SQL server database tables see below what they are saying:
When writing VBA code in Access, ADO (ActiveX Data Objects) is the preferred object model for working with SQL Server. However, the DAO (Data Access Objects) object model is more efficient and full-featured for working with Access objects, and tables linked to SQL Server are Access objects. So although you could use ADOX code to create links, the code presented in this chapter uses DAO.


I want to put the attached code in a module and call it using an autoexe macro at start , now can somebody help on the following items within the code:

(1)      Set tdf = db.TableDefs(LinkedTableName) How to list 120 tables here, show me an example ??
(2)      db.TableDefs.Delete LinkedTableName (What do I put here? Any example)
(3)      Set tdf = db.CreateTableDef(LinkedTableName) How to list 120 tables here, kindly show an example
(4)      tdf.Connect = ConnectionString  (Is it the ODBC with Less DSN File string????)
(5)      tdf.SourceTableName = SourceTableName (How to list 120 tables here, kindly an example??????????)


Public Function LinkTableDAO( _
LinkedTableName As String, _
SourceTableName As String, _
ConnectionString As String) As Boolean
‘ Links or re-links a single table.
‘ Returns True or False based on Err value.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db …
0
there are two tables:
tblMain and DataUPdated
everything is the same, the only column Rate from tblMain should be replaces with Column NEWRATE from DataUpdated

this is select statement to get results below:

SELECT DataUpdated.Status, tblMain.Rate, DataUpdated.NEWRATE
FROM DataUpdated INNER JOIN tblMain ON (DataUpdated.AccNum = tblMain.AccNum) AND (DataUpdated.RATE = tblMain.RATE)
WHERE (((DataUpdated.Status)="Pending") AND ((tblMain.Rate) Not Like "TOU*") AND ((DataUpdated.NEWRATE) Like "TOU*"));

tblMain.Rate  DataUpdated.NEWRATE
DOMESTIC      TOU-D-B-SDP
D-SDP              TOU-D-B-SDP
DOMESTIC          TOU-D-B
DOMESTIC          TOU-D-B
DOMESTIC          TOU-D-B

how to change the select  query to update query and update the fields
0
I need to nest these queries together.

However the problem is  [Contracts].[dbo].[CONTRACT_DATA]( the botton query) is  in a different database called "Contracts"


This first query:
SELECT rcs.[CONTRACT_NUMBER] -- [program].[dbo].[rdr_contract_staging]
      ,VENDOR_CODE = CAST(rcs.VENDOR_CODE AS VARCHAR(250))
      ,rcs.[LIFE_CYCLE_MANAGER]
      ,CTS.[Sum_Of_Sales_Current]
FROM [program].[dbo].[rdr_contract_staging] rcs
LEFT OUTER JOIN (
      SELECT [SS_TRACKCODE], -- [program].[dbo].[CURRENT_TC_STC_CUST_SALES]
          Sum([sls_sum]) AS [Sum_Of_Sales_Current]
      FROM [program].[dbo].[CURRENT_TC_STC_CUST_SALES]
      GROUP BY [SS_TRACKCODE]
) AS CTS ON CTS.SS_TRACKCODE = rcs.VENDOR_CODE

to this select statement below:

[CONTRACT_NUMBER]   to     [contract_number]
(second query)
SELECT   [Freight_GIS]
        ,[CustomerName]
        ,[contract_number]
        ,[ContractName]
        ,[EffectiveDate]
        ,[ExpirationDate]
        ,[status]
        ,[Sales_Segment]
        ,[Sales_Region]
        ,[RSVP_Dir]
        ,[Sales_Manager]
[Contracts].[dbo].[CONTRACT_DATA]
WHERE (dbo.CONTRACT_DATA.status)="active/complete");

Thanks
fordraiders
0
I need to nest these 2 queries...the join is
 [SS_TRACKCODE] = [CONTRACT_NUMBER]

SELECT [CONTRACT_NUMBER] -- [program].[dbo].[rdr_contract_staging]
      ,VENDOR_CODE = CAST(VENDOR_CODE AS VARCHAR(250))
      ,[LIFE_CYCLE_MANAGER]
FROM [program].[dbo].[rdr_contract_staging]


SELECT [SS_TRACKCODE], -- [program].[dbo].[CURRENT_TC_STC_CUST_SALES]
Sum([sls_sum]) AS [Sum_Of_Sales_Current]
FROM [program].[dbo].[CURRENT_TC_STC_CUST_SALES]
GROUP BY [SS_TRACKCODE]

Open in new window


Thanks
fordtraiders
0
Hi, I've run into a problem that seems that is killing me.

In a report, I have a field that I'm trying to show the total value of purchase orders based on an ID.  I'm using   =Nz(DSum("Amount","PurchaseOrders","[id]=16 or [id]=22 or [id]=24"),0)
This does return a value, but it's incorrect.  If I try 1 or 2 ID's I get the correct value, but if I add a 3rd ID the value is not correct.

Example:  
This returns a correct value  =Nz(DSum("Amount","PurchaseOrders","[id]=16"),0)
This returns a correct value  =Nz(DSum("Amount","PurchaseOrders","[id]=16 or [id]=22"),0)
This returns an incorrect value   =Nz(DSum("Amount","PurchaseOrders","[id]=16 or [id]=22 or [id]=24"),0)
This returns a correct value   =Nz(DSum("Amount","PurchaseOrders","[id]=24"),0)

I'm also trying to add additional criteria, example would be

=Nz(DSum("Amount","PurchaseOrders","[FiscalY]='Q4_2018' and [OrderType]='CAPEX' and [id]=16 or [id]=22 or [id]=24"),0)
Here I'm trying to filter record to only include the FY "Q4_2018" and an OrderType of "CAPEX"

Any help would be greatly appreciated.  This is got me stumped.

Thanks
Bob
0

Query Syntax

52K

Solutions

20K

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.