Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

8
votes
4answers
253 views

Separate letters from digits in alphanumeric string

I have an alphanumeric string as input and I want to get two results out of it: a string where all the numerals are removed and an integer that is the sum of all the digits in the input string. ...
0
votes
0answers
23 views

Specify what columns to display in a SHOW COLUMNS statement

Does MySQL offer any way to specify which columns to display in a SHOW COLUMNS FROM my_table statement? From this : Field Type Null Key Default Extra ------ ...
1
vote
2answers
37 views

Create view with group by and extra fields

I had a hard time putting the question title together but essentially I want to create a view like this: ALTER VIEW vPlaceStatistics AS SELECT COUNT (TypeID) as Count, TypeID FROM PlaceStatistics ...
0
votes
1answer
19 views

FullText Search

We came across an issue using FullText Indexes and SQL 2008 R2, not sure if it's by design or I am missing something. When we search using a fulltext index, I noticed SQL appears to trim leading and ...
0
votes
0answers
32 views

How would you migrate a virtual 2012 sql server cluster to a virtual standalone?

I have been tasked with coming up with a plan for p2v and v2v migration. I have never done v2v migration so I'm unsure how to plan it. Specifically, from a virtual SQL server cluster to a virtual ...
1
vote
2answers
40 views

Order by custom filter without certain data

I have a similar issue to the question in: ORDER BY using custom priorities for text columns ...except that I want to only get the data out if I have certain values. To clarify, we have a table ...
0
votes
3answers
33 views

Formatting out a column value - mm:ss.hh

I have a time in seconds and I want to convert it to the following formats possibilities: 121.17 -> 2:01.17 | 34.28 -> 34.28 | 133.56 -> 2:13.56 I am using the following: IF(Time > 60, ...
0
votes
1answer
35 views

Set values conditionally in Postgres UPSERT

We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1 ...
1
vote
2answers
84 views

Counting number of occurences in a time period

I have a view that has 4 columns, an order date, machine reference number, item number, and quantity. How would I generate a list of machines that have consumed an item more than once in a given time ...
0
votes
1answer
49 views

How to make a query on multiple columns without writting all possible permutations?

I'm at my first question on stackexchange, as i have a few days since I struggle on this matter: I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (...
1
vote
4answers
160 views

Display both null and not null under same id but not both null or not null like XOR logic

In this table I need to select only the ids which have both null and not null values, and it should not be both not null or both are null in col1.
1
vote
1answer
90 views

Slot Time Challenge - Doctor Appointment database schema

I am trying to make a doctor appointment system where patient online will have two options: First time visit (this visit should be 20 mins) Follow up visit (this visit should be 10 mins) ...
6
votes
5answers
480 views

UNION ALL or other ways to return first row of result-set

I am writing a table valued function to geocode an address based on its Suburb, State and Postcode. I try to geocode the address using different methods, in order of decreasing accuracy: Exact match ...
1
vote
1answer
32 views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
0
votes
1answer
46 views

Query distinct emails not logged in within a period of time

I need help in writing a query to return distinct emails that have not logged into ABC database in the past 5 months. The exact task I have been asked is as following: Create a table in ABC ...
0
votes
1answer
31 views

ORA-00932 on subquery join from subquery join

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK. SELECT DISTINCT kbq_ent.qid AS kbq_ent_qid, kbq_ent....
1
vote
1answer
41 views

Justified multi-value field in MySQL?

I have a table of data that contains 10 point values based on a score for an event (0 - 9 points). If a time is within the range, the point is awarded. There are 20 possible events. Is this a valid ...
0
votes
2answers
32 views

Update field in SQL Server Table Dynamicaly

I need to do a 1 time update of a Sql Server table. The table has a unique ID, contains a member number field and a FileName field which tells us what file the member data came from. What I am ...
5
votes
1answer
147 views

Concatenation, with separators, where any combo of input fields might be null

I have three columns, CITY, STATE, COUNTRY. One, two, or all columns may be NULL. With the following logic, a single string is made using the SQL below. It is messy, and I am wondering if there is a ...
2
votes
0answers
50 views

Does Trace Flag 8295 applies to SQL Server 2012?

I've been searching about the undocumented Trace Flag 8295. It's been introduced with KB 2476322 in SQL Server 2008 and "'Creates a secondary index on the identifying columns on the change tracking ...
0
votes
1answer
50 views

How to declare reserved words as variables in Oracle PL/SQL?

What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration? I realize bind and substitution may be acceptable alternatives for an actual query, but what about ...
0
votes
0answers
30 views

SQL Server Index recomended but already there (DMVs)

I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc. Some examples are Index ON X ( Y ) and Table ...
4
votes
1answer
20 views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 |...
0
votes
0answers
18 views

Error with displaying ranges of dates when using pivot table

can i know is there any problem with this codes as when i use datagridview and then te pivot table it sometimes the dates runs whereby sometikmes the dates does not follow in sequence. I not sure ...
1
vote
1answer
23 views

Add variable value in single quoted string. Expand variable within single quotes

I have tried different variations of the below commands but non work. I simply want to run the following command using a variable within the location string: CREATE TABLESPACE data OWNER appuser ...
0
votes
3answers
67 views

How to check if range between two numbers is available in table of ranges

I have following table of number ranges Create table Ranges { Id int identity(1,1) primary key, RangeFrom int not null, RangeTo int not null } Insert into Ranges(RangeFrom, RangeTo) VALUES (1,...
1
vote
1answer
25 views

Query: How to join on different tables depending on the filled column?

I'm pretty new to the field of SQL, but already solved a lot of problems, so until now i am familiar with the different possibilites of JOIN(s) (explicit and implicit syntax), group by, distinct and ...
5
votes
3answers
380 views

Why set Auto Update Statistics to False?

I've just inherited about 20 instances of SQL Server, as part of a wider acquisition project. I'm in the process of assessing performance and I don't like the way maintenance plans have been ...
1
vote
2answers
93 views

How to get the Sum of all column values in the last row of a resultset without using UNION?

I need to get the sum of all column values of a result set in the last row. Here is my SQL query. SELECT CS_YEAR AS YEAR, CS_MNTH AS MONTH, CS_WK AS WEEK_NO, 'Total' AS ...
1
vote
3answers
70 views

How to find values from one table missing in another?

I am a Junior Developer. I have been asked to create a subquery to solve this problem. If the question is not clear please let me know so I can provide more details. I have two tables - Article and ...
1
vote
1answer
44 views

how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time

how do I change the query below so it only reads and displays 132 characters each line with w(id, line#, line, rest) as ( select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), ...
0
votes
1answer
21 views

How do you normalize a table that is indirectly dependent on a has-many relationship?

I currently have the following database design: Product: contains various description attributes Belongs to many ProductSets ProductSet: Has many Products Belongs to many Stores Store: Has ...
0
votes
1answer
44 views

extract values from different stored procedures and create one report

At the company I work, the third party taking care of the database. So there is giant complex stored procedure that produces the result for one parameter only. When management needs a desirable ...
0
votes
1answer
63 views

Slow search by LIKE and other values (1 million rows+)

I'm currently developing a kind of search website where one can search for business companies in my country. The database table has 1 million rows and quite a few colums (42 columns). But I find it ...
5
votes
2answers
75 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
1
vote
3answers
71 views

T-SQL for Query that can be done in SQL. What do they want?

We are given tables Vendors(VendorId, VendorName, ..) , Invoices(InvoiceId, VendorId...) I am going over a book exercise that asks for a T-SQL statement to find all vendors with at least one invoice. ...
0
votes
0answers
31 views

Service Desk SLA Report

I need assistance with regard to a DBA task. I need to create an SLA report that will show incidents that were resolved after the SLA period which is 4 hours. I am using SQL Server 2014 with a Heat ...
0
votes
0answers
12 views

How to use column result as column name with MySQL [duplicate]

At the moment I have sample table as such doc_name | processed_date | doc_version ---------------------------------------- doc_1 | 2015-01-01 | 1.0 doc_1 | 2015-01-02 | 1.1 doc_1 | ...
0
votes
0answers
26 views

Error connecting from other SQL instance using powershell script

I have a job to run a powershell script that will rebuid fragmented index through source SQL instance to target SQL instance. The poweshell script didn't run due to the following error: Sqlcmd: ...
4
votes
1answer
195 views

Select multiple columns from subquery - Connect by

I have a table which manages folder structure. Hence it has a parent-child relationship. We trigger a query to sum all documents in the complete heirarchy: SELECT folder_name name, ...
1
vote
1answer
15 views

I have a query which does not return result with the desired alias names of columns in PostgreSQL

The following is my query which does not return result with the desired alias column names: with d as (select distinct emp.ahrims_id, rank.name as rank from hr_employee as emp, ana_rank as rank, ...
1
vote
2answers
38 views

Slow query / Indexes creation (PostgreSQL 9.2)

I have the following Query: explain analyze SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
0
votes
1answer
36 views

Get dates between two months

Can I know why am I getting weird output in my SQL statement whereby my table has two dates of the month May but it only appears for one month, the same things I test for dates range it is working. ...
0
votes
0answers
9 views

Python and SQL exception handling when internet cuts out

I have a python temperature monitoring program that runs every 30 seconds on my raspberry pi, and uploads the data to an SQL server. I am using try: and except: however this doesn't handle if the ...
0
votes
2answers
21 views

postgresql - how to find and extract text from a description column

I'm trying to create a new table using data from an existing. I have the following structure: Table "public.def" Column | Type ...
1
vote
1answer
14 views

Is DISTINCTROW in MS-Access the same as selecting all the fields and doing a group by on all of them?

I was looking though some queries in MS Access, and I found an SQL keyword I was unfamilar with called DISTINCTROW, from what I can tell, it's the same as an SQL GROUP BY which includes all the fields,...
5
votes
3answers
241 views

Selecting one row from each group based on a Varchar field

Given a table Avenger whose data looks like this: id Avenger Type Power 1 Captain America foo 2 2 Captain America bar 3 3 Me foo 5 4 You ...
1
vote
1answer
30 views

Using a join operation with conditionals

I have two tables that I'm working with in my database. I am using SQL Server 2014. One is 'versions', that contains version_id, version_number, start_date, end_date and server_id. The other is '...
2
votes
2answers
48 views

Using Variables to contain column names for SQL Server Merge statemtns

I'm trying to automate the column list grab for a merge job, and running into a bit of a head scratcher. I don't want to have to manually type out all of the column names, and thought I may be able to ...
0
votes
1answer
41 views

Altering column data SQL [closed]

I have a set of car data, one of the columns represents the registration year. I want to change this column to age. As the data is from 2010 I assume that the following statement should calculate the ...