Query Syntax Help

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

2 Comments

OWASP: Avoiding Hacker Tricks

LVL 19

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.

2 Comments

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.

14 Comments

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.

2 Comments

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

2 Comments

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.

12 Comments

Question by

KPax

1d agoSolvedPrivate

DB Diagramming tool

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

2 Comments

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

…

2 Comments

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"));

3 Comments

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

5 Comments

Learn Ruby Fundamentals

LVL 19

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

5 Comments

I'm using the following code and receiving Access error code:

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

8 Comments

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

2 Comments

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

3 Comments

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?

1 Comment

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!

11 Comments

LVL 6

Question by

bfuchs

2d agoSolvedPrivate

Create calculated field displaying fields with null values.

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

21 Comments

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

1 Comment

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

…

12 Comments

OWASP: Forgery and Phishing

LVL 19

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!

4 Comments

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

…

3 Comments

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

…

2 Comments

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

3 Comments

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

3 Comments

LVL 1

Question by

WeTi

2019-11-27SolvedPrivate

Automatic delete tables in dB.

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

3 Comments

Query Syntax

Related Topics

Query Syntax

Related Topics