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

Query Syntax

52K

Solutions

Content

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.

Xamarin mobile app

Hello,
I am using Xamarin crossplatform app with VS 2017. Any example to create an app connecting to SQl Server DB (on prem).

Thanks

2 Comments

Hello,

Any idea please how to convert Date stored in SQL Server column with type bigint to normal Date format.
bigint is totally a different format storing a date.

Thanks.

1 Comment

Question by

MIHIR KAR

23h ago

E-R Model !

Hi Expert,
Could anyone please share the E-R for this. (Looking for E-R model without numeric ids)?

Problem Statement
-----------------------------
A company record information on its feet of vehicle and the employee who are permitted to drive them. The database is used by three groups of people, the department managers, the finance division and the service department.

For the department managers, each vehicle has a unique registration number and each driver has a unique employee number. Drivers may be authorized to drive a number of vehicles and any vehicle may be used by a number of drivers. Vehicles are allocated to departments within the company, although they may be used by drivers in other departments.

Some classed of vehicle require specialist driver-qualifications. There are occasional accidents which may lead to the vehicle being written off and/or the driver being disqualified from driving some or all classes of vehicle.

For the finance division each vehicle, identified again by registration number, has a current and a replacement value, must be taxed on a certain date and was bought on a certain date. For accounting purpose the allocation of vehicle to departments is also required. Finally details are recorded of any insurance claims associated with accidents or repair costs if no insurance claims arose.

The service department is responsible for giving regular services to each vehicle. There are different types of service corresponding to …

1 Comment

I need to convert a list of dates to date ranges. In SQL.

Date set
Name Dates
John      10/01/2018
John      10/02/2018
John      10/03/2018
Casey      10/09/2018
Casey      10/10/2018
John      10/11/2018
John      10/12/2018
Tim        10/13/2018

Desired Results
Name Start Finish
John      10/01/2018      10/03/2018
Casey      10/09/2018      10/10/2018
John      10/11/2018      10/12/2018
Tim        10/13/2018

9 Comments

Installing a new 2016 server. Loaded SQL Express 2017 onto it and created an instance. I am able to see the instance using SQL Management Studio on the server. Client PCs on the network can't see the SQL instance. I created runs int eh server 2016 firewall following the Microsoft KB and clients still can't see the SQL instance on the network.

11 Comments

How can I compare my ONE table named "CUSTOMERS" in my two Microsoft SQL 2014 databases (TEST and PRODUCTION) to see what fields changed ?

Example
Column Name, DatatypeTEST, DatatypePRODUCTION
CUSTOMERID, varchar(10), nvarchar(20)
etc

Maybe something like the below ?
1. https://solutioncenter.apexsql.com/compare-sql-server-database-schemas-automatically/
2. https://www.red-gate.com/products/sql-development/sql-compare/
3. https://www.mssqltips.com/sqlservertip/4824/easy-way-to-compare-sql-server-table-schemas/

2 Comments

Question by

mcrmg

2d agoSolved

Convert to text to display in excel

Hi,

When I display data in SQL, I have one decimal point, for example. 1.0 or 2.0

When I copy it and paste on to Excel, decimal point disappear. Here is my code to convert to text, but it is still not showing .0 in Excel.

	Select top 1 CONVERT(VARCHAR(10), CONVERT(DECIMAL(2,1),[Version]) ) + ' '
	from codex
	where [Trans Id] = W.[Trans Id]

Open in new window

4 Comments

Hi Experts,

I am using MS Query in Excel and I am trying to use a parameter (which is based on a cell value) in my case statement but I am getting a syntax error or access violation.

Select case when ? < value then do something

Open in new window

The ? mark represents the parameter value but obviously, I am using it wrong.

Thank you in advance!

2 Comments

PMI ACP® Project Management

LVL 12

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Question by

SpaceCoastLife

2d ago

ODBC Crashes MS Access

MS Access crashes when attempting to attach SQL Server tables using ODBC

37 Comments

Hi Below is my sample data and I need to write a SQL query to derive 6 columns from the column CONTACTVALUE below and this is my CASE statement as per the requirement

SELECT
CASE WHEN CODE ='VOI' AND sub-code='PER' THEN contactvalue END AS PersonHomeTel
,CASE WHEN CODE='VOI' AND sub-code='PRO' THEN contactvalue END AS PersonWorkTel
,CASE WHEN CODE='VOI' AND sub-code='OTH' THEN contactvalue END AS PersonMobileTel
,CASE WHEN CODE='EMA' AND sub-code='PER' THEN contactvalue END AS PersonHomeEmail
,CASE WHEN CODE='EMA' AND sub-code='PRO' THEN contactvalue END AS PersonWorkEmail
,CASE WHEN CODE='EMA' AND sub-code='OTH' THEN contactvalue END AS PersonOtherEmail
from my table

however I need help in modifying my query so that I need to pick up the contactvalue based on the above rules
based on the LATEST date in case of more than one record. So for example for cust_id 1, for code VOI and sub-code PER has 2 records, then the one with LATEST date should only be picked up. If the LATEST date record contactvalue is NULL then the next contactvalue should be picked up based on the next highest date.

can anyone modify my query to meet my requirement ?

cust_id code sub-code contactvalue Date
1 VOI PER me@gmail.com 18/05/2014
1 EMA PER 00447804584 18/06/2014
1 POS OTH 00437804587 18/01/2014
1 VOI PER 00447804687 18/06/2014
1 EMA PER 00478025848 18/06/2014
1 …

11 Comments

I have installed SqlServer express 2017 and am looking to access it remotely. I have opened ports 1433 TCP and 1434 UDP and I and can access the server, but it comes up Login failed for user.
I then tried mixed authentication and have set up an sa user and can log in using it on ssms locally, but even specifying sa and the password I still get login failed for user. I then selected to enable a proxy account but still no joy.

6 Comments

Hi ,

We're wondering if it is possible to pull XML data directly from an online feed straight into a SQL procedure rather than saving the XML data as a stored table first?

Thanks in advance!

3 Comments

I need to fetch the aggregrate count data from 3 different tables and have them listed as column value in a single row.

eg.

TABLE_1

SELECT DEPARTMENT_ID,COUNT(*) AS TOTAL_COUNT FROM TABLE_1 GROUP BY DEPARTMENT_ID

TABLE_2

SELECT DEPARTMENT_ID,COUNT(*) AS TOTAL_COUNT FROM TABLE_2 GROUP BY DEPARTMENT_ID

TABLE_3

SELECT DEPARTMENT_ID,COUNT(*) AS TOTAL_COUNT FROM TABLE_3 GROUP BY DEPARTMENT_ID

Now , I want the the consolidate data from these 3 tables to be displayed in a view.

DEPARTMENT_ID, TABLE1 COUNT, TABLE 2 COUNT, TABLE 3 COUNT
10,5,2,3
11,2,3,4
12,1,2,3
etc
How can I do this transformation.

Thank you.

5 Comments

db2 export with delimiter set explicit
I execute the db2 command on a windows machine in the db2 command line interpreter shell
I execute a SQL command against db2 and export the result in a delimited ascii file. I want to be able to set the delimiter, default is space with fixed column length.
I start the export with the following command

db2 -tvmf \\somePath\mySQLCommandToExecute.sql > \\somePath\ExportedResults.csv
and the mySQLCommandToExecute.sql file
CONNECT to MYDB USER MYUSER USING MYPW;
With MultipleReferences as(Select distinct coulnm1 , tb1.column2, column3, tb1.column4 from schema1.table1 tb1, schema1.table2 tb2 where column2 > 1 and column3 <> column4 AND COALESCE(tb2.column1,tb2.column2) = tb1.column1 ) select * from MultipleReferences where column4 in('somefilter1', 'somefilter2');
This is only a schematic example of my SQL. As the SQL command is huge (a lot of filters given by a excel sheet) I generate the sql command via a programm and want to have it in a separate file for execution.

As my SQLToExecute is very long I want to have it in a separate file and not directly entered on the db2 command line. So any suggestions like db2 export to test.del of del select tabname from syscat.tables where tabschema ='VSC';
are NOT applicable in my case.
Being able to execute a SQLQuery that is in a separate file is mandatory. Also solution like adding the delimiter explicitly in my SQL command like select a || ';' || b from mytable is not appicable

…

1 Comment

I am using MVC 5 with entity framework verison 6 using a SQL database. The ID field is the primary key in the PL_System table and the foreign key in other tables. My problem is with the Create view of PL_System. Since ID is the primary key and is required, it has to have a value. It is set as an int.

The ID field in the create view is a dropdown. How do I get this to be an auto-generated field rather than a selectable list? I want this to be a new system which will take the ID that is next in the sequence of numbers. In this case, the last ID in the table is 55. I want the next system to have the ID 56 and so on...

[Disclaimer: This was an Access database imported into SQL]

PL_System Controller > Create

// GET: PL_System/Create
        public ActionResult Create()
        {
            ViewBag.Availability = new SelectList(db.PL_Availability, "ID", "Score");
            ViewBag.Confidentiality = new SelectList(db.PL_Confidentiality, "ID", "Score");
            ViewBag.Integrity = new SelectList(db.PL_Integrity, "ID", "Score");
            ViewBag.Information_System_Owner = new SelectList(db.PL_SystemOwner, "ID", "Full_Name");
            ViewBag.Status = new SelectList(db.SSP_SystemStatus, "ID", "Title");
            return View();
        }

        // POST: PL_System/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see

Open in new window

…

5 Comments

I have a parent table and child table. it is a one to many relationship.

child IDs are in one field. I want to convert each child ID into it's own field by concatenating child and position (child + #, i.e. child1) and join all the new child fields to the parent field.
I would then get a row of parent and child fields. there can be any number of children.

i.e.
parent ID, child1ID, child2ID, etc. for each unique child of a parent.

parent| child1| child2| child3|
John, Jimmy, Jan,Jerry
Don, Danny
Nancy, Mary, Mike

4 Comments

I have the below SQL statement which works as expected on my local machine WAMP server using MySQL, but when I upload to the live server using MariaDB, it doesn't work as expected anymore. The SQL statement is used for post and comment display. Any post or comment with latest postdate should be on top of the displayed list. As I said, it worked perfectly with MySQL on my local WAMP server, but when I upload to live server using MariaDB, no matter what I tried, it reverses the order by showing latest post/comment at the bottom of the list and old post/comments at the top. Please help me as I'm now confused on why it's behaving this way.

The SQL statement is shown below:

SELECT * FROM 
(SELECT once.id, message, audio, audio_desc, accepted, postdate, tag, broadcast, once.mobile, once.username1, state, users.username, users.firstname, users.lastname, users.fullname, users.avatar, users.alias, useroptions.aliascheck, content_provider.content_type, comment.comment_date as c1 FROM once LEFT OUTER JOIN users ON once.username1 = users.username INNER JOIN useroptions ON once.username1 = useroptions.username LEFT OUTER JOIN content_provider ON once.username1 = content_provider.provider LEFT JOIN comment ON once.id = comment.post_id AND once.tag = comment.post_tag WHERE once.username1='logusername' OR once.username1='myusername'
UNION ALL
SELECT daily.id, message, audio, audio_desc, accepted, postdate, tag, broadcast, daily.mobile, daily.username1, state, users.username,

Open in new window

…

6 Comments

Angular Fundamentals

LVL 12

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

I'm getting a couple of error message when running a sql query from a cold fusion file. I believe if I solve one I can solve the other but I'm not exactly sure how to best solve it.

Errors:

Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]IDENTITY_INSERT is already ON for table 'tlaSQL.dbo.TLAApprovers'. Cannot perform SET operation for table 'TLACLAIMS'. The specific sequence of files included or processed is: D:\inetpub\wwwroot\TLA\Admin\TLAattachments.cfm, line: 148

Open in new window

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot find the object "TLACLAIMS" because it does not exist or you do not have permissions.  
  
The error occurred in D:/inetpub/wwwroot/TLA/Admin/TLAattachments.cfm: line 146
 
144 : <cfset requestId = "#getNextID.nextID#"> 
145 : 	
146 : 	<cfquery name="qTurnOnInsert" datasource="#dsn#">
147 : SET IDENTITY_INSERT TLACLAIMS OFF
148 : </cfquery>

--------------------------------------------------------------------------------
 
VENDORERRORCODE   1088 
SQLSTATE   HY000 
SQL    SET IDENTITY_INSERT TLACLAIMS OFF  
DATASOURCE   tlasqlpp 
 
Resources:

Open in new window

Sample code w/in cfm file:

<cfquery name="qTurnOnInsert" datasource="#dsn#">
SET IDENTITY_INSERT TLAApprovers OFF
</cfquery>

<cfquery name="qTurnOnInsert" datasource="#dsn#">
SET IDENTITY_INSERT TLACLAIMS ON
</cfquery>

<cfquery name="InsertClaim" datasource="#dsn#">
insert into TLAClaims (ID, lastName, firstName, eeEmail, travelbegindate, travelenddate, numberofdays,  unit, approver, submitdate, tono, claimAmount, customerNotes,  employeeId, documentation, status, deoEmail, claimType) 
values ('#requestId#', upper('#lastName#'), upper('#firstName#'), '#eeEmail#', '#travelbegindate#', '#travelenddate#', '#numberofdays#', '#unit#', '#approver#', '#todaysDate#', '#tono#', '#claimAmount#', '#customerNotes#', '#employeeId#', '#documentation#', 'Inactive', '#deoEmail#', '#claimType#')
</cfquery>

Open in new window

I've researched and come up w/ turning the insert off then back on after the query. I've also landed on possibly using result_name.IDENTITYCOL but that looks like its only useful for a cf dump from what I've found. Can anyone help w/ this?

Thanks much for any help.

11 Comments

I need a calculated min column using datediff & min functions but not exactly the min value but the 2nd min value or date, below is the query for your reference:

Select r.rfq_Id, datediff(min(q.published_at),r.published_at) as "RFQ to 1st Quote TAT in days"

from quotes q
Left join rfqs r on r.id=q.rfq_id

This gives or groups me the rfq id column and date difference. But i want it use the 2nd min value for calculation rather than min value. Please suggest.

38 Comments

Just trying to find out more information about SQL SSIS which I know nothing about. Can SQL SSIS be used ingests a Multi-Worksheet EXCEL file and separate each worksheet into a separate output file like text or csv? We are think about using SQL SSIS as a preprocessor for an application that does not support multi-sheet EXCEL files. I am aware we can use SQL SSIS to do data transformations but now i want to know if this can file re-engineering tasks like this. Also, SQL SSIS be configured to generate an XML file?

3 Comments

I am getting this attached error when setting up a listener to a newly created always-on availability group. It throws this error every time I try to create one. The users I am using has all the rights on the domain and on the both the sql boxes.

Any idea?
alwayson_error1.JPG

1 Comment

Question by

Curtis Long

3d agoSolved

Excel connecting to SQL locks up

I have a spread sheet that connects to an outside database and draws data from predetermined views.

This spread sheet has 20 connections.

When I first start the spread sheet up it almost completely locks up. The database slows down horribly as well.

After about 5-10 minutes the spread sheet finally delivers an error message like the following:

The following data range failed to refresh:
datasets.mydomainname.com program 2017 product
Continue to refresh all?

If I click "yes" it finishes its data refresh and all is well.

If I look under data, connections, this connection does not seem to exist. I would love to find this connection and delete it. This spread sheet is supposed to be 2018 and all 2017 data is outdated.

I am using Excel 2013 and am connecting to SQL server 13.0.4451

Thoughts??

2 Comments

Dear Experts

I am new to Python and MySQL and as part of my training I am creating database listing all my files accross all my NAS. I have table TPaths and TFiles, TPaths contains obviously PathName and TFiles containes FileNames. PathName is foreignkey for TFiles which links Path and FileName together.

This is clear.

My question is maybe very easy - When I am walking accross files and folders, for each file found I need to check whether its path is already listed in TPaths table. What is quickest way to do it? Should I index field Pathname? Should I search it like full text? Should I search it with clause LIKE?

Many thanks for your answer

Vladimir

1 Comment

First
Prev
1
2
3
4
5
6
7
Next
Last

Query Syntax

52K

Solutions

Content

20K

Contributors

Query Syntax

Related Topics

Query Syntax

Related Topics