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

learn more… | top users | synonyms (2)

0
votes
1answer
44 views

MySQL - Finding Gaps of Date Ranges - Overlapping Dates

I have been struggling for a long time with generating gaps in date ranges, according to a set. CREATE TABLE `terms` ( `id` int NOT NULL PRIMARY KEY, `term_begin` date NOT NULL, `term_end` date ...
0
votes
2answers
43 views

Grant select on tables with a specific naming pattern

How to grant select on all tables starting with VVC_? grant select on VVC_* to user1
18
votes
7answers
437 views

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
0
votes
1answer
67 views

Why did a query change its execution plan and how to anticipate the change?

We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple ...
0
votes
2answers
21 views

Thinking about Normalization should I create a column for specific behavior?

I would like to expose a problem I often have when I work with database and I never knows what to do. I need to load data from a file into a table (table1) line by line with the help of a software. ...
8
votes
3answers
258 views

Unexpected CASE evaluation logic

I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL ...
0
votes
1answer
58 views

Subquery returns more than 1 row

I'm executing a query to return the products of a specific sale in Magento database. It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row ...
0
votes
1answer
30 views

Query the last records by date, where condition is true

A made this query, which is works perfectly now: SELECT t.DocID, t.RevID, t.EffDate FROM tblRev AS t INNER JOIN ( SELECT DocID, max(EffDate) AS MAXEffDate FROM tblRev GROUP BY DocID ) AS tm ...
-1
votes
0answers
29 views

Using where clause to create a left join

TL:DR I have two tables Table1 and Table2. Table1 contains 1 row per request. Table2 can contain 0 to 20 rows per request. Current query simply gets data from Table1. I want to add Table2 to the ...
2
votes
1answer
54 views

Predicate re-write in where clause

How can I rewrite where col % 5 = 4 in SQL Server. The reason to rewrite this is because I am selecting some columns from the table based on this condition. Because of % used in where it is causing ...
1
vote
2answers
62 views

Ignore empty rows while loading data into a table

I created a table (tbldemo) with 22 columns using a CREATE TABLE statement in MySQL. When trying to insert the data from a .csv file using a LOAD DATA INFILE statement, I get this error: Error ...
0
votes
3answers
22 views

Create Table Syntax [closed]

I am trying to create a table in MySQL but I keep getting an error of incorrect syntax. Below is my create table syntax, what is incorrect? CREATE TABLE [Test].[dbo].[Ite]( [ID] [INT] NOT NULL ...
2
votes
3answers
26 views

SQL Login curretly in use or not

Recently we did a change on a client's production server. The change was to rename an sa login. Once we renamed the sa login we found out the client was having a problem because sa login was used in ...
1
vote
1answer
23 views

Top N Results Ordered by Joined Table Column

I'm trying to optimize a fairly simple query that just gets a list of the top N users ordered by the number of followers they have. I am running into performance issues when I run the query a certain ...
0
votes
1answer
34 views

frequently dropping table in sql server

Kindly help me understand my below concern. In our current business requirement. We have to pull the complete data everytime. We have to truncate out table and load it with 20million of data. I want ...
0
votes
1answer
16 views

How to support custom fields for an entity and list them as columns?

I am currently working on a system which supports "custom fields" for users. I will illustrate this with a simplified example of this database schema: users -------- user_id name age custom_fields ...
1
vote
1answer
26 views

Alias Column Name [closed]

I am trying to run a basic select Query in PostgreSQL, but I am having trouble using the column alias. I tried to create the alias as randomfield As [Space In Name] but it gave an error of Error: ...
2
votes
1answer
23 views

Count of range on datetime in MySql

I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this. id userId transactionTime 1 1 2016-04-13 22:15:44 2 1 ...
0
votes
0answers
21 views

Copying values from one table to another table which the column intended to be copied delared as default

Can i know, the below column late_allowed when creating attendance table i declared as default and with a numbers (such as 3). The problem is that i am not sure how to put and where can i put default ...
0
votes
0answers
10 views

don't understand atquery command script [migrated]

I'm using a big-data database. in one of it's tutorial it has recommended me to use below bash scripts if order to running queries: #!/bin/sh # this will launch the real atquery program with the ...
0
votes
1answer
49 views

Scalar UDF causing performance issues in SQL Server

I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect ...
0
votes
1answer
37 views

Updating column from another table - PostgreSQL 9.2 [closed]

There are two tables: ja_jobs junk.test_test1 I need to update the time_job column from junk.test_test1 to ja_jobs. Here is what I'm doing now: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM ...
3
votes
1answer
26 views

Store info in a column as JSON

I'm learning SQL Server 2016, I learned how to extract JSON from tables. Now I need to store JSON in a column. My JSON was: {"endereco":"R. Nome da Rua, 000", "cep":"00000-000"} I`m using nvarchar ...
0
votes
1answer
12 views

select duplicated rows by modified_date - PGSQL 9.2

Query: SELECT j.id INTO junk.ja_jobs FROM ( SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id FROM ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND ...
3
votes
1answer
32 views

Extract Multidimensional JSON

I have some tables: teste // id, descricao teste2 // id, descricao relacao // id, idTeste, idTeste2 With this structure, I got all products owned by a client. Now I'm trying to use FOR JSON PATH to ...
0
votes
0answers
35 views

SQL Server Trigger: Move deleted rows from 4 different tables to an archive table

I have a garage database with 20 tables. 4 of these tables - tCar, tCustomer, tAddress and tTelephone hold data about when we service a car. After a few months I want to be able to delete the data ...
0
votes
1answer
38 views

How to delete rows from the mid of the table without any where condition?

I have a table testing with two columns id: auto_increment name: varchar(20) I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records ...
2
votes
1answer
108 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table UserFriendlist that has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that it holds the following values: ...
0
votes
1answer
42 views

Application Using ODBC Data Source To Connect To A Database Not Working?

Ok so I have a program that needs to access a database in SQL Express 2008 using an ODBC Data Source. The program is used for the inventory of objects in the SQL database using a gui. It basically ...
0
votes
2answers
83 views

Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables?

My case: I have many organisms (for example human, mouse and etc.) and a few models for each one. All models are independent from each other and I will never query them together. Normally I will do ...
1
vote
1answer
16 views

Informix help - SQL DBA

I am a SQL Server DBA with minimal Informix 11 experience. I'm tasked with extracting an entire Informix database (circa 8GB according to systables in Informix) into a SQL Server database. I plan to ...
4
votes
2answers
370 views

Help undoing an accidental restore on SQL Server 2014

Yesterday I made a serious mistake¹: Restoring the wrong database. Background (you may skip this section): I right-clicked the Test database and proceeded to the "Restore Database" section as ...
0
votes
3answers
65 views

Get all records from inner joined tables if second table does not have data

I follow below syntax by which I achieve the result. but it takes time IF NOT EXISTS(select * from #tblxyz) BEGIN INSERT INTO #tblxyz select NULL END Select a.* FROM tblabc a ...
0
votes
1answer
29 views

How to write a SQL query to find the range from column values?

I have some fields in my database which contain values like 2483-5802 and 1245-3325. Now I have a user input and I have to find it from this column. Example if I have to search 2000 which will come ...
1
vote
1answer
13 views

Delete duplicated rows by modified_date - PostgreSQL 9.2

I've got some duplicated rows on a table called ja_jobs: To find those duplicated data, I'm running this Query: select * from ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted ...
3
votes
3answers
196 views

How to return rows between two datetimes stored as date and time?

How to view the result between 2 dates and times? My current Code: SELECT * FROM [DB].[dbo].[TABLE] WHERE [USER_ID] = '005' AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016' AND [TIME] >= ...
0
votes
1answer
15 views

Server configuration SQL Server 2008

I am really fresh in the DB world so firstly,sorry about my noob question. I have a data warehouse project to perform in a group of 3 people. When installing SQL Server 2008 R2 Developer in my own ...
1
vote
1answer
24 views

How to Increment duplicate strings in a column

I have a table similar to what is below. I have listed only one column since that's what I'm interested in. | column --------- | foo | foo | foo | bar | bar | bar Below is the output that I ...
4
votes
1answer
93 views

Update with default values in case clause

I have a single table like CREATE TABLE Mytable (ID int identity, Name nvarchar(10)); GO INSERT INTO MyTable (Name) VALUES ('test1'); INSERT INTO MyTable (Name) VALUES ('test2'); GO ALTER TABLE ...
-2
votes
2answers
101 views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
0
votes
1answer
68 views

Error launching sqlplus

I try to run sqlplus on my server with the command sqlplus /as sysdba and I got this error sqlplus: error while loading shared libraries: ...
0
votes
1answer
19 views

Communition Link Failure

I want to make a DNS to a sql database by SQL Driver. so I do it as it should be done. I have over hundred Clients with Windows 7 OS. All of them can connect to the database easily just a few of them ...
1
vote
3answers
180 views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
0
votes
1answer
38 views

How do I find the day of week of a date-time field in postgres?

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While SELECT EXTRACT(DOW FROM '2011-07-07') works, the ...
0
votes
1answer
39 views

Is there anyway to tell when the last query was made against a database? [duplicate]

Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?
0
votes
3answers
90 views

Select distinct value from row if condition exists

I am new to SQL and I am having trouble constructing a SELECT statement. What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I ...
3
votes
1answer
26 views

How can I assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows

I have a table from MySQL called References with 20 columns and 20 rows: CREATE TABLE bop_ppy ( start_time DATETIME (6) , end_time DATETIME (6) , high DECIMAL (5,2) , low DECIMAL (5,2) , close ...
1
vote
1answer
31 views

Is it possible to create a function for transactions that use more than one database?

As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have ...
1
vote
1answer
37 views

Slow Query Postgres 9.2

I've got the following query: SELECT j.id, concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client, c.email AS client_email, ...
1
vote
1answer
50 views

Grouping similar rows together based on time

I have a table that records all the changes done to a model by a user. The table looks something like this: (SQLFiddle) In the table an action_hash is generated based on the model, model_id and ...