Query Syntax

55K

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

I have a Access table (tblExcelProductivity) that includes FullName, FieldOffice, Mnth , and Goal. I would like to use an update query to update the Maximum Goal number with 0 for each like record based on FullName, FieldOffice and Mnth. For example below, Mary has 2 records in Arlington in the month of DEC with Goals of 45 and 67.2. In this case 0 would be updated 67.2 the max for the 2 records.




Image

qrySumAgedCase1.xlsx
0
I am writing a small API to learn Spring.

My entities are:
@Entity
@Table(name = "tblCheck")
public class Check {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column
    private int port;

    @Column
    private Protocol protocol;

    enum Protocol {
        HTTP,
        HTTPS,
        TCP
    }

    protected Check() {
    }

    public Check(int port, Protocol protocol) {
        this.port = port;
        this.protocol = protocol;
    }

    public int getPort() {
        return port;
    }

    public void setPort(int port) {
        this.port = port;
    }

    public Protocol getProtocol() {
        return protocol;
    }

    public void setProtocol(Protocol protocol) {
        this.protocol = protocol;
    }

    @Override
    public String toString() {
        return "Check{" +
                "port=" + port +
                ", protocol='" + protocol + '\'' +
                '}';
    }

}

Open in new window


and:
import javax.persistence.*;
import java.util.List;

@Entity
@Table(name = "tblConfigurationItem")
public class ConfigurationItem {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column
    private String hostname;

    @Column
    private String type;

    @Column
    @OneToMany(cascade = {CascadeType.ALL})
    @ElementCollection(targetClass = Check.class)
    private List<Check> checks;

    protected ConfigurationItem() {
    }

    public ConfigurationItem(String hostname, String type, List<Check> checks) {
        this.hostname = hostname;
        this.type = type;
        this.checks = checks;
    }

    public List<Check> getChecks() {
        return checks;
    }

    public Long getId() {
        return id;
    }

    public String getHostname() {
        return hostname;
    }

    public void setHostname(String hostname) {
        this.hostname = hostname;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    @Override
    public String toString() {
        return "ConfigurationItem{" +
                "id=" + id +
                ", hostname='" + hostname + '\'' +
                ", type='" + type + '\'' +
                ", checks=" + checks +
                '}';
    }

}

Open in new window


I am not 100% sure that my relationship makes sense.
Do you agree with my implementation?

If I try saving data it ONLY works if I insert a single record:
    @GetMapping(value = "/api/init")
    public String initializeData() {
        Check check80Http = new Check(80, Check.Protocol.HTTP);
        Check check443Https = new Check(443, Check.Protocol.HTTPS);
        List<Check> checks1 = new ArrayList<Check>();
        checks1.add(check80Http);
        List<Check> checks2 = new ArrayList<Check>();
        checks2.add(check443Https);
        List<Check> checks3 = new ArrayList<Check>();
        checks3.add(check80Http);
        checks3.add(check443Https);
        ConfigurationItem ci = new ConfigurationItem("test.com", "webserver", checks1);
        ConfigurationItem ci2 = new ConfigurationItem("test.com2", "webserver", checks2);
        ConfigurationItem ci3 = new ConfigurationItem("test.com3", "webserver", checks3);
        configurationItemRepository.save(ci);
//        configurationItemRepository.save(ci2);
//        configurationItemRepository.save(ci3);
        return "Done";
    }

Open in new window


If I insert more than one (uncommenting the save for ci2 and ci3 I get:
There was an unexpected error (type=Internal Server Error, status=500).
could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.TBL_CONFIGURATION_ITEM(ID) [1, 'test.com', 'webserver']"; SQL statement: insert into tbl_configuration_item (hostname, type, id) values (?, ?, ?) [23505-200]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.TBL_CONFIGURATION_ITEM(ID) [1, 'test.com', 'webserver']"; SQL statement:
insert into tbl_configuration_item (hostname, type, id) values (?, ?, ?) [23505-200]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

Open in new window


Can you help me in understanding what is the issue?

Thanks!
0
I installed the on-site connector and successfully added it to Power Apps and Power Automate, working fine.  However, when I try and get data from SQL in Power BI, there does not seem to be a similar connector available.  I tried using just the SQL connector, specifying the Windows server name as the SQL server and the Table name I want and using the Windows user credentials, but it kept failing.

I found a thread that suggested the workaround was to run Power BI desktop as administrator, and when I tried that the response went to a time out message.

Really appreciate it if you can help.
0
I have  two fields I want to update using CASE WHEN Syntax.
The fields are PipUPC and TUPC. I don't know how to set the fields with the ELSE END syntax using the CASE WHEN.
Essentially what I need is:
1) check the PipUPC and if it is null, update  update with the List_UPC  value  from ActionList2020  table ELSE keep the  current value in the cell.
2)Check the TUPC and if null, update with the List_UPC  value  from ActionList2020  table ELSE current value of the in the cell.



Update
.[dbo].[UpdateProcessSales] 

Set PipUPC=
(

CASE WHEN PipUPC is NULL then t2.List_UPC,
 WHEN TUPC is NULL then UPC=t2.List_UPC
ELSE END,
)
from
[dbo].[UpdateProcessSales] t1
inner join
[dbo].[ActionLIst2020] t2
on t1.PNumber=t2.Price_Code

Open in new window

0
I have 3 Application Servers and One SQL Database Server. One Application server cannot connect with SQL DB.
Whereas other two Application servers are connected and working fine.
Please suggest how to troubleshoot the issue.
0
We currently have a vb.net application that is connected to a SQL server database through LINQ, table adapters and stored procedures.
For a while now we have been getting a lot of problems with things like double adjustments sale not processing etc - (When a sale is processed for example the stored procedure for the sale are run twice, other times is does not run at all and most of the time the sale is processed correctly, we think this could be caused by deadlocks but we are unsure. When a sale is processed, we sometimes get that the stock has been adjusted incorrectly – double adjustments. These problems are currently occurring at our bigger clients).
Someone has mentioned making use of a transaction manger. The only one that I could find that is compactable with SQL server was Java database Connectivity (JDBC). Are there other transaction manager options we can use?
Or must we fix all the SQL in our application using SQL transactions?
0
I have 2 SQL Servers, let's call them SourceSQL1 and LinkSQL1.

SourceSQL1 is Microsoft SQL Server 2014 (SP2). Let's say it has 3 databases: db1, db2 and db3 , db4 and db5. I have a SQL login named sql_link. The SQL Login has been given db level permission to db1 and db2. (db_datareader)
LinkSQL1 is Microsoft SQL Server 2012 (SP3)

I have created a SQL user on SourceSQL1 and granted the public role as well as granted db_dataReader on db1 and db2. I created a linked server on LinkSQL1 with the option to use the security context of sql_link. When I expand the linked server I see 3 databases! I am expecting to see only 2 databases: db1 and db2, however db3 also shows up in the list. The other 2 dbs (db4 and db5) do not show up in the list. When I attempt to query db3 via the linked server the query fails saying the table does not exist, so I am not concerned about a security hole, but I just wondering why this shows up in the list.
0
CA SiteMinder SSO user store creation and migration from LDAP to SQL Database. User Store.
0
See the below screen shot attached.
My query is now working good. However, I need an additional step.

When I select the Share Button, I call a query: qSaveQREValueReport. I am prompted to enter a SelectedTagVariable.

Private Sub Share_Click()
DoCmd.OpenQuery "qSaveQREValueReport"
End Sub

Open in new window


When I manually enter the TagNumber the query executes perfectly.

However, I'd like to pass this variable from the selection in the Form (as highlighted)
as I select the "Share" Button and then call the query.

For Reference. Here is my query code
SELECT 
SkpiUpdate.NAMC,
 QREVALUE.TagNumber,
 QREVALUE.QPRQPINumber,
 SkpiUpdate.[Supplier Code],
 SkpiUpdate.Date,
FROM QREVALUE INNER JOIN SkpiUpdate ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag
WHERE (((QREVALUE.TagNumber)=[@SelectedTagVariable]) AND ((SkpiUpdate.[Fault Type])="PPM"));

Open in new window


Form Name: QREValue
Form Control: TagNumber

Table name: QREValue
Table field: TagNumber

Control Button: Share


saveRecord.png
0
Guys,
I got the suggested query below from link sql server powershell notification on how to trigger email notification when sql server service is stopped. However this script maybe worked with only one server,  In my company we have approximately 100 sql server and instances, so I would need the query will go and check every server line by line send sending  email when it found any of the sql server associated query  in stopped mode.

Try
{
    While(1 -eq 1)
    {
        Invoke-Sqlcmd -ServerInstance "YourSqlInstance" -Query "DECLARE @ConnectionTest int;"
        sleep 5
    }
}
Catch
{

    $smtpServer = "yourcompany.com"
    $msg = new-object Net.Mail.MailMessage
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

    $msg.From = "windowsServer@yourcompany.com"
    $msg.ReplyTo = "windowsServer@yourcompany.com"
    $msg.To.Add("dbaTeam@yourcompany.com")
    $msg.subject = "Connection to SQL Server Failed"
    $msg.body = "Connection to SQL Server Failed"
    $smtp.Send($msg)

}

Open in new window

0
Dear ,

Im looking Discover LDAP  SQL inventory tools to pull below fields from SQL databases :

1- Server Name
2- Server IP
3- OS Version
4- SQL Version
5- Instance Name
6- DB Name
7- Database Restore Date
8- Last database access
9- Logins last access

Regards
0
Are there any recommended Python, R and SQL source code samples which should read for learning purpose and which can be downloaded from GitHub?
0
Hi,

I have a sql database in which I gather info of other sql databases and use this to do execute some actions.
F.e. I gather which applications are.deployed on sccm, AppV Infra and where they are deployed to, which users requested it etc

Therefore I query different sources  via Powershellandcreate different tables in my database (let's call it MyGatheringDB).
I.also have ssrs which does query my MyGatheringDB to show reports.

Isn't there a way I can make ssrs do the work and query ssrs right away?
In.other words, I d then query select * from sccm on ssrs and query these ssrs items (stored procedure then?) from my MyGtatheringDB without the need to store all the tables on MyGatheringDB?

Thanks
0
I have the following table:

clientid      configid      configvalue
100            a                  10
100            b                  20
100            c                  30
200            a                  10
200            b                  50
300            a                  a
300            d                  2/1/2020
400            d                  3/1/2020

I want to retrieve all the client id's who do not have configid, or if they have configid, the configvalue is in the future. Any ideas?
0
I am getting String was not recognize as valid DateTime when I try to upload to the database.  Any Ideas see code below..

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net;
using System.Security.Principal;
 
namespace CertificationCapture {
    public partial class _Default : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {
            if (!IsPostBack)
            {
                Cal1.Visible = false;
                Cal_exp.Visible = false;
            }
        }
 
 
        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {
            if (Cal1.Visible = false)
            {
                Cal1.Visible = true;
            }
            else
            {
                Cal1.Visible = true;
            }
        }
 
        protected void Cal1_SelectionChanged(object sender, EventArgs e)
        {
            ent_date.Text = Convert.ToString(Cal1.SelectedDate.ToString());
            Cal1.Visible = false;
        }
 
        protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
        {
            if (Cal_exp.Visible = false)
            {
                Cal_exp.Visible = true;
            }
            else
            {
                Cal_exp.Visible = true;
            }
 
        }
 
        protected void …
0
We have an SQL 2008r2 server based accounting system and I added a Win10 client box which performs a lot of the SQL db requests quickly, until it doesn't.  Wondering why?  The Win10 will open the DB browsers fine like the Win7's, but then clicking into open the detail on one of the browser's records... it takes 8 seconds to open, whereas on a Win7 box it takes less than 2 seconds.  Any common sense settings to do in Win10 to help it perform nicer?  Thanks!
0
I need a SQL select that can change the orientation of my record.  My input file contains one row per unique ID with separate columns for each reference ID and Type.  I need to format each row by breaking out each column reference type and ID into a new row.  



Input Table:

ID               Reference1 ID      Reference1 Type      Reference 2  ID             Reference2 Type
--------      -------------       -------------               -----------------            -----------------
10000       82                               GPH                             63                              LineBuy
10001       55                               GPH                             43                              LineBuy                                                                
10002                                                                                32                              LineBuy

Output Table:

ID              Reference ID        Reference Type
---------    -------------        ----------------
10000        82                        GPH
10000        63                        LineBuy
10001        55                        GPH
10001        43                        LineBuy
10002        32                        LineBuy
0
The following code is taking excel file to MS SQL table. The codes working but have some issue.

1. When the codes ran on my development Window 10 PC, it returns 15000. This is correct because Excel has 15000
2. When the codes ran on IIS 10 or 7, it returns 12000

I add all of the settings I can think of
https://stackoverflow.com/questions/288612/how-to-increase-the-max-upload-file-size-in-asp-net
or
IIS as well.

And the result is the same. Do you have ideas how to fix it?


Protected Sub test_Click(sender As Object, e As EventArgs)
        'Upload and save the file
        Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(BCTFileUpload.PostedFile.FileName)

         


        BCTFileUpload.SaveAs(excelPath)

        Dim connString As String = String.Empty
        Dim extension As String = Path.GetExtension(BCTFileUpload.PostedFile.FileName)
        Select Case extension
            Case ".xls"
                'Excel 97-03
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"
                Exit Select
            Case ".xlsx"
                'Excel 07 or higher
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"
                Exit Select

        End Select
        connString = String.Format(connString, excelPath)
        Using excel_con As New OleDbConnection(connString)
            

Open in new window

0
We have an old database in SQL Anywhere that contains gps positions, date and fuel purchase information, it's on a separate server on a not-supported anymore application called PCMiler Fueltax.

We're still using it since it works fine but our latest import didn't work, for reason I can't explain the system won't accept 2020 dates.

I'm at a loss trying to figure out why , the error simply says   "Invalid date ".

I use the same data, put 2019 and it works, 2020, 2021 gives me the error message so I'm thinking there's a setting somewhere that stop accepting data but ... what could it be.

If anyone has an idea or something I could try feel free to answer !

Thanks !
0
I have an Office 365 Excel VBA program which connects to a local Sql Server Express 2017 to run a stored procedure. Sql Server is set to Sql Server and Windows authentication mode.

I have a connection string in the VBA code:
"Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Trusted_Connection=Yes"
... which works perfectly.

When I set the connection string to a Sql Server user:
connString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & uid & ";Password =" & password

... I get the error message:
Login failed for user '3'

The Sql Server user (PFreeman) and password I specified has db owner privileges.
There is user or login named 3 in Sql Server.

Can you suggest why the VBA/ADO or whatever is sending the user 3 to Sql Server or what might be the problem with the connection string?  I have tried every permutation to the connection string I can think of with no success.

Thanks
0
Logged in using single user mode
0
In the event the entire row of measures is (null) this logic, that normally works, does not
it returns a Count of 1.

Can anyone tell me where I am going wrong?


// WITH SET [S] AS ({ [Measures].[PnL], [Measures].[Hire Rate], [Measures].[TCE], [Measures].[Offhire Days], [Measures].[Net Days] } )
 WITH MEMBER RowCount AS Axis(0).Count
 
//WITH MEMBER RowCount As
//	IIF({  [Measures].[PnL], [Measures].[Hire Rate], [Measures].[TCE], [Measures].[Offhire Days], [Measures].[Net Days] } = null, 1 , null)   	--		{ FILTER ( [Measures].ALLMEMBERS, (  [Measures].[PnL] <> null  ))  }  ,

 SET [S] AS { FILTER ( [Measures].ALLMEMBERS, (  [Measures].[PnL] <> null  ))  }  

        SELECT  { RowCount,[S]  } ON COLUMNS 
		       FROM ( SELECT ( { [Date].[Date].&[20200101]:[Date].[Date].&[20201231] } ) ON COLUMNS FROM      
			 	       ( SELECT ( {                 
					             FILTER         
								                 (       
												         [Voyage].[Vessel Tag].[Vessel Tag].ALLMEMBERS,    
														                    (         
																			           (         
																					        Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCBV') > 0 OR 
																							Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCL') > 0 OR 
																							Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCBV') > 0 OR 
																							Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCI') > 0 OR 
																							Instr([Voyage].[Vessel 

Open in new window

0
I have 2 tables which are shown as examples below. The ultimate goal is to get a query that includes all of the fields in the "Member_Table" and the "Risk_type" field in the "Risk_Table".
The problem is that both tables have effective and expiration dates and they don't match and the "Mem_Eff_Dates" can have gaps.    
There are 2 fields to join on, the "Prov_ID" and "RISK_PROD_CODE". The problem is that if I just use these joins the results will include duplicates.
The results example below is what the results of the query should look like.  The "Member_Table" records have to be split with new "Mem_Eff_Date" and "Mem_Exp_Date" dates
to accomodate the "Risk_Eff_Date" and "Risk_Exp_Date" date fields in the "Risk_Table".


Member_Table
Member_ID      Prov_ID      RISK_PROD_CODE      Mem_Eff_Date      Mem_Exp_Date
R11057276      L029      MEDCALQA                     12/1/2017              12/31/2017
R11057276              K112      MEDCALQA                     2/1/2018                      4/30/2019
R11057276              L029      MEDCALQA                     5/1/2019                     6/30/2019

Risk_Table                        
RISK_PROD_CODE      Prov_ID      Risk_Type      Risk_Eff_Date      Risk_Exp_Date
MEDCALQA                      L029      SR                      7/1/2015              4/30/2018
MEDCALQA                      K112      DR                      5/1/2018              10/31/2019
MEDCALQA                      L029      SR                      11/1/2019              12/31/9999

Results                              
Member_ID      Prov_ID      RISK_PROD_CODE      Mem_Eff_Date      Mem_Exp_Date      Risk_Type
R11057276         …
0
We need to purchase a license for SQL Server 2016 Standard edition to be installed on a new VM running on Vmware 6.5.

The pricing model is a little confusing as it is per core, but will be on a esxi vmware host - but i assume it will be looked at for the number of cores i assign it within vSphere.

Please assist in understanding the true cost (server license or server license plus CAL?) of creating a new VM with that version, one SQL instance with multiple databases.

Thanks!
0
INSERT INTO WELL_AliasIndex ( API14, ENERTIA_ID, WELL_NAME, PROPNUM )
SELECT WELL_ID_AriesSQLProperty.API, WELL_ID_AriesSQLProperty.ENERTIA_ID, WELL_ID_AriesSQLProperty.WELL_NAME, WELL_AliasIndex.PROPNUM
FROM WELL_ID_AriesSQLProperty LEFT JOIN WELL_AliasIndex ON WELL_ID_AriesSQLProperty.API = WELL_AliasIndex.API14
WHERE (((WELL_ID_AriesSQLProperty.API) Not In (select [WELL_AliasIndex].api14 from [WELL_AliasIndex])) AND ((WELL_ID_AriesSQLProperty.PROPNUM) Is Not Null));


I am just trying to append new records from WELL_ID_AriesSQLProperty.API to WELL_AliasIndex and this query does not work. Any help would be highly appreciated.

Thank you.
0

Query Syntax

55K

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.