Query Syntax

48K

Solutions

14

Articles & Videos

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

Notice in my query below where my 2nd Join statement includes the field B.ID...that throws a "B.ID could not be bound" error. I just need to be able to pass the B.ID coming from my 1st Join statement (or even the A.ID from tableA would be alright) to the nested query that makes up my 2nd JOIN table. I considered making my nested select-query in the 2nd join its own function, but I'm not sure if that would even work inside a join statement with the ID being passed into that function as a variable (ie. "B.ID" instead of a literal ID like "1234").
SELECT A.Name, B.ID 
FROM tableA AS A 
LEFT OUTER JOIN tableB AS B ON A.ID = B.ID 
LEFT OUTER JOIN (select top 1 Car from functionC(B.ID) where color = 'green' order by orderDate desc)

Open in new window

0
Major Incident Management Communications
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

I'm running into the below error when using the OLE DB (ADO) SQL Server Native Client 10. Server is running SQL Server 2008 R2. We are able to make a connection using Microsoft OLE DB Provider for SQL Server fine however the reports are putting out unreadable data. Thanks in advance.
Crystal reports error
0
For Microsoft SQL, 2012, I was told that the following, ""Database encryption scan" indicates that  a database is being created.  A quick google search is pulling up information that suggests otherwise.  I am looking for something definitive.   I am working with others to be alerted when a database is created and another when the database is encrypted.
0
I have been tasked to build an online audit questionnaire/survey. I am fairly new to asp.net. I want to create the questions and server controls dynamically with data from sql server. For each questions I will need to dynamically create two  (Yes/No) radio buttons. Any help would be greatly appreciated. I am using asp.net forms.
0
Hi expert i have a query with below code please help....

-- 1st i have created a table .
SQL> CREATE TABLE TARGET(SNO NUMBER(12),NAME VARCHAR2(12),SID NUMBER(12) CONSTRAINT PK_RAJ_SID PRIMARY KEY,
  2  LOC VARCHAR2(12));

Table created.

SQL> INSERT INTO TARGET VALUES(&NO,'&NAME',&SID,'&LOC');
Enter value for no: 1
Enter value for name: RAJ
Enter value for sid: 100
Enter value for loc: ODISHA

1 row created.

SQL> /
Enter value for no: 2
Enter value for name: TAPA
Enter value for sid: 101
Enter value for loc: PURI
1 row created.
SQL> /
Enter value for no: 3
Enter value for name: MIHIR
Enter value for sid: 102
Enter value for loc: BBSR
1 row created.
SQL> /
Enter value for no: 4
Enter value for name: SOUMYA
Enter value for sid: 103
Enter value for loc: HYD

1 row created.

SQL> /
Enter value for no: 5
Enter value for name: RANJAN
Enter value for sid: 104
Enter value for loc: BNG

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TARGET;

      SNO NAME               SID LOC                                                                
--------- ------------ --------- ------------                                                      
        1 RAJ                100 ODISHA                                                            
        2 TAPA               101 PURI                                                              
        3 MIHIR              102 BBSR                                    …
0
I have two tables (TBL_A and TBL_B). There is a one to many relationship between them. Every record in TBL_A has zero to many related records in TBL_B.

When all TBL_B records with the same TBL_A idnum have a statuscode of 'INACTIVE', I want to change the related TBL_A statuscode to 'INACVTIVE'. Following are more detaiils:

TBL_A has 2 columns (it actually has more but for this example, it is enough). They are:

- IDNUM  (this is the key field for TBL_A)
- STATUSCODE

TBL_B has 3 columns (it actually has more but for this example, it is enough). They are:

- IDNUM (this is the key field for TBL_B)
- STATUSCODE
- TBLAIDNUM (this is the foreign key that points back to TBL_A)

I've attached an excel file with a couple of examples and the results that I am trying to achieve.

I hope this provides enough information for you. Also hope you are okay with the excel example.

Any ideas will be appreciated.
TBL-A-AND-B-EXAMPLES.xlsx
0
I have a SQL Server table with column FullName VARCHAR(300)

I have a .net import from file process that runs fine except...
Where there are accent characters
It substitutes a ? as it goes into SQL.
Exemple   'PIñERO RODRIGUEZ' goes in as 'PI?ERO RODRIGUEZ'

However..if I run a simple update...
Things update correctly in SQL and can be selected with no issues

UPDATE dbo.Individuals
SET LASTNAME = UPPER('PIñERO RODRIGUEZ')
WHERE IndividualID = 204140


Or I do something simple in SQL like...
INSERT INTO Individuals
VALUES ( UPPER('PIñERO RODRIGUEZ'))   - (Abbreviated code of course...)

Inserts fine

So...

Do I need to change my import Sub (VB.NET) ?

Possibly the "AddWithValue" should actually be this type of format???
oCom2.Parameters.Add(New SqlParameter("@" + header(k), SqlDbType.VarChar, 300)).Value = returnRecords.Rows(i).Item(k).ToString()

This is how its sendingthe insert right now
Dim returnRecords As DataTable = Session("sheetData")
Dim test As String = hdJsonData.Value
 Dim header As String() = test.Split(",")
Dim columnNames As String() = returnRecords.Columns.Cast(Of DataColumn)().[Select](Function(x) x.ColumnName).ToArray()

Using... blah...blah...blah...
oCom2.CommandText = "InsertImportContactStaging"
 oCom2.CommandType = CommandType.StoredProcedure
oCom2.Parameters.AddWithValue("@ImportID", GUID)
oCom2.Parameters.AddWithValue("@ImportRecordType", Session("ImportFileType").ToString() + "")

             …
0
Hello:

Below is my code.  As you'll notice, I have hard-coded the YEAR as '2017'.

The KBUK..GL10110 table contains the YEAR as the field YEAR1, and I have aliased the table as gl.  But, when I try to have gl.YEAR1 represent the YEAR (instead of hard-coding the year), most of the results return NULL.

Why is that, and how can I overcome it?

Thanks!

John


select gl2.ACTIVE, Accts.ACTINDX, gl3.ACTNUMST as [Account], gl2.ACTDESCR as [Account Description], 
'2017' AS [YEAR], Period.PeriodNumber, 
case when Period.[PeriodNumber] = 0 then 0 ELSE ISNULL(SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC)-
(gl.DEBITAMT-gl.CRDTAMNT), 0) END as [Opening Balance], ISNULL(gl.DEBITAMT,0) as Debit, ISNULL(gl.CRDTAMNT,0) as Credit, 
ISNULL(gl.DEBITAMT - gl.CRDTAMNT,0) as [Net Change], ISNULL(CASE WHEN gl.PERIODID = 0 THEN gl.PERDBLNC ELSE
SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC) END,0) AS [Ending Balance] 
FROM (select distinct ACTINDX from KBUK..GL00105) as Accts
CROSS JOIN (select 0 as PeriodNumber union all select 1 as PeriodNumber union all select 2 as PeriodNumber union all
select 3 as PeriodNumber union all select 4 as PeriodNumber union all select 5 as PeriodNumber union all
select 6 as PeriodNumber union all select 7 as PeriodNumber union all select 8 as PeriodNumber union all
select 9 as PeriodNumber union all select 10 as PeriodNumber union all select 11 as PeriodNumber union all
select 12 as PeriodNumber) as Period
LEFT OUTER JOIN

Open in new window

0
I am importing a file into SQL Server table

Some of the Nast names are Hispanic with the ~ over the n?  

In file it is fine
Once it is in SQL it gets replaced with a ?

AQnt ideas?
0
Hello Experts,
In continuation to : https://www.experts-exchange.com/questions/29014655/Powershell-help-for-creating-accounts.html
I have a further enhancement request,
I have 2 Columns in SQL
ADGroup & O365Group

These columns contains values, which are separated by comma (,) these are existing groups in AD & O365
I want them to be integrated into the script so that when the new user is created he is added to the respective groups in both the places.

Thanks in advance.
A
0
Guide to Performance: Optimization & Monitoring
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Good morning,

I am trying, with the query below, to grab "scanning" data.  From the Transactions table I'm grabbing the user and sales order (SO).  I then grab quantities from the soitem table (looking for when SO = fsono).

The query works fine unless someone scans an SO more than once (thus resulting in the record being added again into the transaction table).

How can I grab distinct records from the transaction table and then left outer join the other tables to get me my results?

Thank you in advance.

Select 
  Description, sum(FnAltQty) as totalDoorCount, userID, fsono 
from 
  DoorTrak.dbo.Transactions 
  left outer join DoorTrak.dbo.Entities on DoorTrak.dbo.Transactions.userID = DoorTrak.dbo.Entities.entityID 
  left outer join M2MDATA01.dbo.soitem on DoorTrak.dbo.Transactions.SO = M2MDATA01.dbo.soitem.fsono 
where 
  userID >= 1000 
and 
  month(dateCreated) = 6 
and 
  day(dateCreated) = 21 
and 
  year(dateCreated) = 2017 
and 
  fprodcl in ('SD', 'SM', 'SR','LV','SL','SS','ST') 
group by 
  userid, description, fsono 
order by 
  userid, fsono

Open in new window

0
Hi

Is it possible to drop any key that may exist on a table in SQL using a SQL statement (not manually), or do you have to specifically know what that primary key is called?

Thanks
0
I have Microsoft SQL Server 2012 installed in a production environment.
The TempDB has grown to tak up the size of the C Drive (20GB in growth).
How can I shrink the TempDB in the production environment without impacting live users?
0
Hi

I am trying to drop a primary key in my SQL table [Machines]. The following isn't working

ALTER TABLE Machines DROP CONSTRAINT pk_Machine_Number

This is strange because with SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
I can see "Machine Number" so I am not sure what I am doing wrong
0
Hi,
Is it better to install Microsoft SQL Server on Azure box or to completely migrate to Azure SQL Server.
Migrating to Azure SQL Server might take long, so will getting used to it.
What are the benefits of moving to Azure SQL Server and are they worth the effort?
Are there any disadvantages of installing and using Microsoft SQL Server on the box instead?
Thanks!
0
I have table

 USER_ID  ROLE_ID  
    1            11,32,44
    2             34, 11, 32, 44
    3             11
    4             44, 22, 32
   5             32
  6              32, 44


If i pass 11, 32 role_id in where condition then it should fetch column which contains 11 and 32 , only 11 or only 32 should not be fetched , whatever role_id  i pass all should be present, please help me with query oracle , it will really helpful for me
0
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-06-21T11:42:47). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 10.155.16.253:25)
0
I have a SQL query where i attempted to use the solution presented in the thread, "Get the last 13 week from SQL query". It works WELL but I have two issues.
1.  When I use that solution in the where clause, any calculation that I have ROUNDS to 1 or 0, never the decimal
2.  How do you get the last 13 weeks using just the week number?

My data only shows week and year.
0
I'm following along with a video by Ben-Gan called Creative Uses of Apply Operator and he shows this code

Select orderiid, orderdate, nextyear
from sales.order
  cross apply(values(year(orderdate))) as a1(orderyear)
    cross apply(values(year(orderyear +))) as a2(nextyear)
where orderyear > 2007

---end code----

He comes up with a result set where the nextyear column is one year later than the orderdate, i.e., if orderdate is 2008-01-01, nextyear is 2009
you can see it video place...

 https://youtu.be/-m426WYclz8?t=1291

However when I try using it with this code on two different databases 2012 AdventureWorks or AdventureworksLT  I use the same code on different column names..


select SalesOrderID,  OrderDate, NextOrderYear
from SalesLT.SalesOrderHeader
      cross apply (values(year(OrderDate))) as a1(orderYear)
            cross apply (values(year(orderYear + 1))) as a2(NextOrderYear)
            where orderYear > 2001

I get the

 NextOrderYear as being 1905

Obviously something is not right. Just not sure what?
0
Free Tool: Path Explorer
LVL 8
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I would like to take this code and have it be reused for different stored procedures. This section of code would be called for each the the button on the form below. I have all the datatable, dbcommand, executenonquery, executescalar all in one c# class and call them when I need them I need to do this for the dataset code below

String connString = “”;

string sql = “select” – I would like to use a procedure so I can use the code in more than one place.

// create connection
Sqlconnection conn = new sqlconnection(connstring);

Try
{
Conn.open();

Sqldataadapter da = new sqldataapapter(sql, conn);

Dataset ds = new dataset();

Da.fill(ds, “”);
 
(would like to return ds.table
}
Catch
{
}
Finally
{
Conn.close();
0
How can I validate address using API  in C# or any batch process?
0
I have been asked to migrate/update an Access/SQL Application so it can run in a VM Environment on a true SQL Server; I have a general understanding to get started, but this is my first VM project, so step one is to plan for the unexpected. I request that you treat me as a novice when making suggestions.

Rather than write a novel about the function of this Access/SQL Application and to minimize suppositions, I would rather respond to questions about it.

To start this off, here is the current configuration ...
Standalone Workstation
Windows 10 64 Bit
Access Pro Plus 2013 (Front End)
SQL 2016 Express (Back End)

What would be the best approach and what pitfalls should be anticipated?
0
Hi,

I've just been told I'm now in charge of and managing two SQL databases and have never used SQL before.

One of them we can remove as it is a backup and is no longer required. How do I safely back this up and then delete it so we can, if required, restore it?

Also: The db has a total size of 6,927Mb. 6021Mb of which is transaction log space. How do I go about reducing this log space? We are nowhere near using up the space allocated to logs but we are short on disk space on the server.

I've tried backing it up by doing a full backup and also a transaction log backup but no reduction in transaction log space.

So I'm looking for 3 things:

1) Reducing transaction log space
2) How to create a full backup that we can restore from
3) How to take the db offline and then delete it from Studio (assume Tasks > Offline, then delete)

Thanks and apologies for the noob questions but this has literally landed on my desk.
0
Dear Experts,

Based on your experience could you please advise in which scenarios worth to use this "SELECT in JOIN" technique:
SELECT
	t1.LIFNR AS VendorNumber,
	t1.NAME1 AS VendorName,
	SubQuery.BUKRS AS CompanyCode
FROM
	AP.LFA1 t1
INNER JOIN
	(SELECT AP.LFB1.LIFNR, AP.LFB1.BUKRS FROM AP.LFB1 WHERE AP.LFB1.BUKRS = 'SX1') SubQuery ON t1.LIFNR = SubQuery.LIFNR

Open in new window


instead of doing the same filtering in WHERE section?
SELECT
	AP.LFA1.LIFNR AS VendorNumber,
	AP.LFA1.NAME1 AS VendorName,
	AP.LFB1.BUKRS AS CompanyCode
FROM
	AP.LFA1
INNER JOIN
	AP.LFB1 ON AP.LFA1.LIFNR = AP.LFB1.LIFNR
WHERE
	AP.LFB1.BUKRS = 'SX1'

Open in new window


Thanks in advance,
0
I want to return the Top 5 vCount for/in each vGroup in this query, but I'm having trouble figuring out where to put it ?

		/****** Region ******/		
		SELECT	vGroup, vType, count(*) AS vCount
		FROM (
		SELECT        'Region visits' AS vGroup, Districts.name AS vType
		FROM             (v_Visits INNER JOIN Customers ON v_Visits.customerId = Customers.id) INNER JOIN Districts ON Customers.districtId = Districts.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

		/****** Brand ******/
		UNION ALL
		SELECT	vGroup, vType, count(*) AS vCount
		FROM(
		SELECT        'Brand visits' AS VGroup, Products.brandName as vType
		FROM            v_Visits INNER JOIN Visits_r_Products ON v_Visits.id = Visits_r_Products.visitId INNER JOIN Products ON Visits_r_Products.productId = Products.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

Open in new window

0

Query Syntax

48K

Solutions

14

Articles & Videos

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.