Query Syntax

54K

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

Hi,
I am following a tutorial on SSAS that was created using SQL Server 2016.
I am trying to find  a 2016 Developer Edition to download but Microsoft's site seems to only have the 2019 version
Any guidance appreciated.
Thanks
0
OWASP: Avoiding Hacker Tricks
LVL 19
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

I'm trying to remove the 1st 3 characters of a text field if the 3rd character is a dash (-) and the 1st 2 characters are numbers in an Access query or VBA. See attached sample result.


Image
0
We have a warehouse that has many locations in the database.  The locations are varchar values as below:
10A1,10A2,10A3,10A4,10A5,10A6,10A7,10A8,10B1,10B2,10B3,10B4,10B5,10B6  ETC.

We want to find the nearest location to say 10A7, we started with the below but it doesn't work:



declare @loc nvarchar(20)
set @loc = '10A7'


SELECT TOP 5 loc  
         FROM   location_mst
              where   loc <= @loc
             and description = 'pa1'
         --ORDER  BY  loc ASC
             union all
            SELECT TOP 5  loc
         FROM   location_mst
           where   loc >= @loc
              and description = 'pa1'
         ORDER  BY loc DESC


Does anyone know of a good way to get the results, we are looking for.  The table houses about 3500 locations all the same naming convention.
0
Does TDE encrypt  just the database or the entire SQL server?     I need to have just one of our databases encrypted (for now)  and need to make sure using TDE only that database is affected.

I currently have one database where I am encrypting a single column but the one in question is a separate database on the same server that also has many other databases.
0
Hi Experts,

I have a table with data in which I'd like to have a "Clickable" list of Equipements.
When an equipment is selected, it'll populate all the information related to that specific equipment to a form.
IE:
I'd like
E1
E2
E3
...
E16
to be in a selectable rectangle and when an equipment is clicked, it selects the form Inspections and it fills all data related to it.
Is there a way to do it without drop box/list ?
Planning.accdb
0
How can I group the following by month and day. I can make it work by months but this groups month in all the years in one record.

SELECT SWD_SCADA_Daily.[Well Name], Sum(SWD_SCADA_Daily.[Water Injection (bbl/day)]) AS CumInjection, Avg(SWD_SCADA_Daily.[Injection Pressure (Psi)]) AS AverageDischargePressure, Avg(SWD_SCADA_Daily.[WHP (Psi)]) AS AverageWHP, MONTH(SWD_SCADA_Daily.[Time/Date]) INTO SWD_SCADA_MONTHLY
FROM SWD_SCADA_Daily
GROUP BY SWD_SCADA_Daily.[Well Name], MONTH(SWD_SCADA_Daily.[Time/Date]);

Thank you.
0
Can someone tell me which tool is used for diagram. Looks so familiar to me, but can't remember what is it?

l6Ar7.png
0
I am receiving NULL values when calling a function within a view. The name of the function is RC_EXCUSABLE_DELAY_V2. I have attached the SQL for the view and the function. I have also included sample data that should have values for RC_EXCUSABLE_DELAY_V2. Thank you in advance for any help.

View:
select wo_sum2."WO_NUMBER",wo_sum2."WO_TYPE",wo_sum2."RC_NUMBER",wo_sum2."NOTES",wo_sum2."PEL_TAIL_NUMBER",wo_sum2."MANUAL_TAIL_NUMBER",wo_sum2."AMDT_MOD_IN", 

Open in new window

0
How do I implement full join in access. I want to see data from both table joined by two fields.

SELECT SWD_NDIC.*, SWD_SCADA_Daily.*, SWD_NDIC.[Well Name]
FROM SWD_NDIC full JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name]) AND (SWD_NDIC.Date = SWD_SCADA_Daily.[Time/Date])
WHERE (((SWD_SCADA_Daily.[Well Name])="Test") AND ((SWD_NDIC.[Well Name])="Test"));
0
Hi

I have a movie database some how I've got some file path with a new line character

Like this
C:\Path\To\MyFile.mp4\n

Open in new window


How do I locate these?

this caused
Msg 402, Level 16, State 1, Line 12
The data types varchar and char are incompatible in the '|' operator.

SELECT * FROM "file" WHERE path like '%' | char(13) | '%' or path like '%' | char(10) | '%';

Open in new window


Once I've found then I'll need to remove the "\n";
But I think there is going to be duplicate
and the file . id is a foreign key


BTW It is a MS SQL database
0
Learn Ruby Fundamentals
LVL 19
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Can someone please help me to understand why I am getting this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Lab@xyz.org'.

/trainingSessions/SQLRuns2.asp, line 58


 strSQL3 = "UPDATE _Employees SET Pager = e.LName + ""Lab@xyz.org"" FROM _Employees e INNER JOIN _Department d ON e.reportsto = d.DeptHead WHERE e.isactive = 1 AND e.isScientific= 1 AND e.reportsto = " & Session("strPIID")                                        
 set rsEMP = oConn.Execute(strSQL3)


This is the result in SQL Server Profiler:

UPDATE _Employees SET Pager = e.LName +  "Lab@xyz.org" FROM _Employees e INNER JOIN _Department d ON e.reportsto = d.DeptHead WHERE e.isactive = 1 AND e.isScientific= 1 AND e.reportsto = 7165
0
I'm using the following code and receiving Access error code:

Error

SELECT
CASE 
WHEN [tblConsolidated].[Appeal Category L3] IN
('180 MEDICAL INC',
'180 MEDICAL INC',
'180 Medical Inc.',
'180 Medical, Inc',
'180 MEDICAL, INC.')
THEN '180 MEDICAL INC'
Else [tblConsolidated].[Appeal Category L3];

Open in new window

0
Hi Folks,
I have written a SQL code generator where end users can define their own tables independent of developers. These are financial tables and so the structure can be quite weird and row size can exceed 8k. To tackle this for these tables i am using NVARCHAR(MAX) as data types for the columns and also using  
EXEC sys.sp_tableoption 'dbo.table_names','large value types out of row','ON'

Open in new window

but still i get the following error message.
Cannot create a row of size 9307 which is greater than the allowable maximum row size of 8060.

Open in new window

Can you please help?

I have attached the file of one of the tables

regardsTable.sql
0
I have a SQL Server Reports database (mdf & ldf) that survived the crash of a SQL server which had to be rebuilt.  The tables are valid.  I can do queries on them, such as

select * from catalog

the most important table, as I understand it.

But I cannot use the database as a data source in my newly built SQL Server.  I get an error trying that.

So I built
0
I'm trying to extract from AX the above data, does anyone know which tables I need to use and how to link. I do not have access to the AX client only the SQL database.

I'm being told to use CUSTTABLE, EMPLTABLE AND DIREPARTY table but I cannot work out how to link these.

Can anyone help?
0
Hey there!

I'm using a DENSE_RANK function in MS SQL to group by a datetime +/- 2hrs and it's working really well but I need to adapt it to Oracle and I'm having some trouble making that happen.

Here is the TSQL:
MyRank = DENSE_RANK() 
OVER (ORDER BY 
MyPID,MyAgency,MyCCD,(DATEADD(hour, (DATEDIFF(hour, '', MyDtTime)/2)*2,'')))

Open in new window


I tried a TSQL to Oracle converter thing but that's not getting it.                    

Any help will be greatly appreciated.

Thanks!
0
Hi Experts,

I want to create a calculated field (or formula) that will show which of the field/s on the table is null for that record.

So for example if FieldA, FieldC and FieldE are null, the value of that calculated field should display "FieldA, FieldC, FieldE".

Need this in SQL 2008 compatible syntax.

Thanks
0
Hi,

I have SQL ssrs standard (which has feature klinkt available) but would need some kpi data on the top of my report.
F.e. number of items in report, number of items with certain value, number of items with other value etc

How can I do this?

J
0
Hi all i am like 99% finshed this pain in the but script but i am now getting a error mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual
 that corresponds to your MySQL server version for the right syntax to use near

for the life of me i cant work it out and i tihnk i am going half blind just looking at the code can you please help me out

from requests_html import HTMLSession
import mysql.connector



mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="*****",
  database="flightdata"
)




mycursor = mydb.cursor()

# create an HTML Session object
session = HTMLSession()

# Use the object above to connect to needed webpage
resp = session.get("https://www.adelaideairport.com.au/flight-information/flight-search/?flt_no=&carrier=All&city=&dte=Current&leg=Departures")

# Run JavaScript code on webpage
resp.html.render()
data = []

airline_spans = resp.html.find('.SearchResultFlightListRow')
print (airline_spans)
airline_list = [span.text.split('\n') for span in airline_spans]

for flight in airline_list:
    if len(flight) == 7:
        flightno, From, to, scheduled, estimated, gate, status = flight
        print ("This is a " + estimated)
        if estimated == "":
            print (" currently no dely ")
            print ("This is a " + estimated)
            estimated = 'IDEL'
    #    print (f'Flight no {flightno} from  {From} to {to} is scheduled to depart at {scheduled} from 

Open in new window

0
OWASP: Forgery and Phishing
LVL 19
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Hi Experts,

I have a table with 2 columns (Last name) and (First name).

I'd like to extract all the first name that has the same value as another column value.

IE:
The db is:
First name  |  Last name
Olivia                  Jones
Jones                  Mertil
Nicholas             Jay
Avril                    Lavigne
Charles              Brown
Annie                 Charles

The result would be the table

First Name | Last Name
Jones               Mertil
Charles           Brown


Thank you!
0
What is the correct syntax to call a function with four parameters within a view?


View:
select wo_sum2."WO_NUMBER",wo_sum2."WO_TYPE",wo_sum2."RC_NUMBER",wo_sum2."NOTES",wo_sum2."PEL_TAIL_NUMBER",wo_sum2."MANUAL_TAIL_NUMBER",wo_sum2."AMDT_MOD_IN", 

Open in new window

0
Keep receiving this error when running the view below. I have included the SQL from both the view and the function I am calling within the view. Any help will be appreciated.

ORA-06553: PLS-306: wrong number or types of arguments in call to 'RC_EXCUSABLE_DELAY_V2'
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:
Error at Line: 10 Column: 157


View:
select wo_sum2."WO_NUMBER",wo_sum2."WO_TYPE",wo_sum2."RC_NUMBER",wo_sum2."NOTES",wo_sum2."PEL_TAIL_NUMBER",wo_sum2."MANUAL_TAIL_NUMBER",wo_sum2."AMDT_MOD_IN", 

Open in new window

0
Hi,

I have an .html report (generated by.powershell)that queries a sql db to show (almost) live data on software requests: number of requests in a table, then per item who requested it, when, total through put time, status etc.
Wonder if  could use a sql reportingserver for that too (live data)? We have one, but not on same sql as on which my db is.

Please advise.
J
0
Dear experts

I would like to export a table from MSSQL to a csv file, using query, anyone know how?

And I also need to know, if its possible to import this csv file to another MSSQL dB?

Thanks
0
Dear experts

Please provide a solution for me, I got a MSSQL that got tables:
test1_201911110
test1_201911120
test1_201911127
test1
Those are table names.

Every month we need to delete test1_201911110m test1_201911120 and keep test1_201911127 and test1. The table names are diffirent each month. last month it was
test1_20191010
test1_20191020
test1_20191027
test1

Is there a automatic way doing it? Thanks
0

Query Syntax

54K

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.