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

learn more… | top users | synonyms (2)

0
votes
0answers
22 views

Using both NoSQL and SQL in a Project

I am working on a project and would like some advice on my data structure for my backend. Currently I am writing a SaaS Multi-Tenant application hosted in Azure for customers who use the same legacy ...
2
votes
3answers
119 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...
3
votes
1answer
55 views

Auto created Statistics vs Manually created Statistics

Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement. Will the manually created statistics get ...
3
votes
1answer
75 views

Find items matching only values from a specific list

I have a table like this: Id Status 1 Closed 1 In progress 2 Rejected 3 Cancelled 4 Rejected 4 Closed 5 In progress I want to show rows that have the ...
17
votes
1answer
215 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...
1
vote
1answer
34 views

Wrong Last Execution Time sys.dm_exec_procedure_stats

I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I ...
4
votes
1answer
117 views

INSERT INTO inserting multiple times

I have a table that contains all information about user called 'DataSource'. I have columns called 'First Name', 'Surname', and 'Address' (there's more but as an example). I have two tables called '...
0
votes
1answer
11 views

Distinct with timestamp and miliseconds

How can I do a distinct when the system display millisecond in my timestamps. I just need the date. My query is like this select distinct orders.client_po, orders.order_code, orders.created_at, ...
3
votes
2answers
80 views

Automate COPY command - Postgres 9.2

I've a backup table with several distinct IDS... I wanna do a BACKUP of those rows, by ID. The function has to be executed by a limit of account_id ( select dump(21); - Where 21 = number of ...
4
votes
1answer
113 views

How to re-enable read ahead (prefetch) feature in SQL Server after disabling it?

In Microsoft SQL Server, I've disabled the read ahead (prefetch) feature using DBCC TRACEON(652,-1) But does anyone know how to enable the read ahead feature? Before I disabled the read ahead,...
0
votes
1answer
31 views

Accessing a Linked server [closed]

I am fairly new to the whole DBA scene. With that said, i was able to get an ODBC connection established within Microsoft SQL Server 2012 to be able to access a database for a library. Now what I am ...
1
vote
1answer
36 views

Order by varchar mysql

Is there anyother alternative for order by varchar clause? At worst case consider I want to order by varchar column that is not indexed. When I tested a query with loads of data it took many times to ...
3
votes
2answers
476 views

Tricking SQL server into thinking it's a different date

I have a unique problem (at least for me) that I have solved in one way but would like an easier way if possible :) I often have to test our Windows app with dates in the past. Our app queries a SQL ...
3
votes
1answer
84 views

Error: “The certificate cannot be dropped because one or more entities are either signed or encrypted using it.”

I have a certificate I would like to remove from the database. If I issue the command DROP CERTIFICATE <FooCert> I get the error The certificate cannot be dropped because one or more ...
0
votes
2answers
32 views

CASE statement multiple conditions

I've got a simple table below, Select * from message_log where Message = 'BUILD' order by 1; +------------+-------------+ | **Message | Log** | +------------+-------------+ | Build | ...
3
votes
4answers
107 views

Update Null Values With Other Values

I am new to the SQL Server world. In my example below, I have two companies: Building Blocks Red Cement Only one entry for each company has a value for the columns power1, power2, power3. I need ...
1
vote
1answer
44 views

SQL query to check a table against a “rules table”

I have a table PIT that looks like this: PIT_TYPE PIT_STYPE PIT_APID 1 1 1313 1 7 1313 3 25 1316 247 1 1318 247 ...
1
vote
0answers
30 views

Fetching equal records of all options MySQL [closed]

there are 4 columns which I need in conditions. age, city, accommodation, sex I want to export total number of 1000 records in the following criteria: I choose ages between 31-35 years old with both ...
8
votes
3answers
522 views

Compute the total of SUM(column)

I have this code that sums up the qty for a certain item (itemid) and by its product date code (proddte). select sum(qty), itemid, proddte from testtable where .... group by itemid, proddte What ...
1
vote
1answer
33 views

Is it possible to set PostgreSQL roles with privileges this way?

I'm trying to develop a system, and for the first time I'm using PostgreSQL. I'm trying to organize and separate roles properly, but I'm losing my mind with it (about a week on this now). Following ...
1
vote
1answer
33 views

Notify on SQL trace event starting?

I was wondering if there was a method of receiving some sort of notification when a SQL trace begins - be it e-mail or some other method? The circumstances of needing this we currently have a ...
1
vote
1answer
20 views

Changing Key (PK, FK) properties in DROP/Add column statements

I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout table_name( Field_1 datatype PK, ...
11
votes
1answer
180 views

Modeling a database for Video Game related information with multiple many-to-many relationships

Scenario I am relatively new to database design, and I decided to make my own hypothetical database for practice. However I am having trouble modeling and normalizing it, as I esteem that there are ...
1
vote
1answer
99 views

Execute a stored procedure in a SELECT or FROM statement or a SQL function

Are there any possibilities to execute a stored procedure in a SELECT or FROM statement or a SQL function? Like select sp_name_and_parameters from table_name_or_sp_name_and_parameters or select ...
1
vote
0answers
33 views

How to export views and import it in other DB?

I am trying to export the database and everything is fine but I am not able to export the VIEWS. Is it possible to export the views from sql queries and import it?
1
vote
1answer
34 views

Select rows and add their previous rows

I have a table structure that holds historical data for another table. Something like id | name | date ---------------------------------------- 1 | John | 2016-01-01 14:38....
0
votes
1answer
32 views

SQL Developer auto trace does not show cascading deletes

I'm trying to understand the performance characteristics of SQL statement that does DELETE cascade on 4 linked tables. When I do the auto tracing of the statement it only shows me the DELETE ...
2
votes
3answers
64 views

2 fast SQL queries get slow when bunched as subquery on a very large table

I'm trying to use a subquery to speed up a query on a very large table right now 30 million row it most likely going to end up close to 1 billion rows. My table lnmntr is as follow Lnmntr_ndx: ...
0
votes
1answer
12 views

Oracle SQL: Why are column headers missing?

I want to understand why my columns are missing their headers. Consider this table: SQL> DESC customer; Name Null? Type ------------ -------- ------------ CUST_ID NOT ...
1
vote
2answers
40 views

How to find out why this query is in waiting state?

I have a query that has been several minutes in waiting and is not running at all. In the SQL Developer monitor session view it shows that query has been waiting for 667 seconds? How can I find ...
0
votes
0answers
34 views

Writing query for sorting based on foreign attribute

I am creating a voting system. My database looks like . Vote is a 'wrapper' for subitems. A vote has many subitems. When an action on that wrapper is performed (create/update/edit),a row is added in ...
1
vote
1answer
32 views

Consolidate multiple rows into single row

In PostgreSQL 9.5, I have a table named reports: CREATE TABLE public.reports ( id BIGSERIAL PRIMARY KEY, id_station character(11) NOT NULL, date date NOT NULL, element character(4) NOT NULL, ...
1
vote
1answer
26 views

Flat similar rows on few columns - Oracle

I wonder is there any way to flat data like this using sql query maybe you can help me The data looks like this: id name customer_code column1 column2 ----------------------------------------- 1 ...
0
votes
0answers
54 views

Get rank of users based on dynamic points

I have this db structure for my ranking system. users table id username 1 alex 2 john points table id name description value 1 foo bar 100 2 bar foo ...
1
vote
1answer
17 views

Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy ...
5
votes
3answers
150 views

Calculate 40 day moving average w.r.t to a field

I have a table that stores the information about user calls in a call center. The table has a call_id, date when the call was made, actual date and time of the call, call type and a score associated ...
1
vote
1answer
63 views

Create Materialized View from foreign table

I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server. What do I have to add to make the query access the foreign ...
1
vote
0answers
25 views

Querying pg_buffercache: relation “pg_buffercache” does not exist

I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine: psql -qAtX -h *** -p *** -U postgres -c "select count(*) from ...
0
votes
2answers
49 views

Why can I execute this query but not see its execution plan?

Consider the following query: SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||owner ||'.' ||table_name)),'/ROWSET/ROW/C')) AS COUNT FROM ...
3
votes
3answers
49 views

SQL, category tree with multiple parents

I need to make a category tree where some leafs can have multiple parents, for exaple g \--b \--a c \--d \--a above category 'a' has two parents(g and c) My first attempt was to create the ...
0
votes
1answer
50 views

Create pivot table from SQL query results

I need to convert the results from my SCCM 2012 query about user device affinity to show multiple columns per computer name. Example of query results :- Desired results format :- Is it possible ...
1
vote
1answer
57 views

Querying dates with different timezones at db and application level

There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to ...
1
vote
1answer
36 views

Copying Partitioned table data to another table on different SQL Server

We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment. But the problem is that , Import Export ...
-1
votes
0answers
30 views

How does IN and NOT IN work in SQL?

How does IN and NOT IN work in SQL ? For example if a query like as SELECT count(customer_number) Count_Customer FROM customer_master WHERE customer_number IN (SELECT customer_number FROM ...
0
votes
2answers
39 views

SQL Server stop/exit query on error

I have the below 2 commands (insert and drop) in a query. When I run the query I get an error saying: String or binary data would be truncated. When I go to check the source table I find out that ...
-2
votes
1answer
64 views

select from where row x column A = row x+1 column B

Query select from where row x column A = row x+1 column B Data Car ID | Time 1 | Time 2 1 | 5:15 | 4:12 2 | 3:13 | 5:15 3 | 6:12 | 5:15 4 | 4:12 | 2:15 Result It ...
3
votes
1answer
65 views

Importing and exporting data from a database to another using SyBase is taking a long time

In order to update a database(Sybase Adaptive Server Anywhere 8) I'm taking all the data from one database on clients computer and using SELECT * FROM tabelName OUTPUT TO 'C:\path' FORMAT ASCII ...
0
votes
0answers
15 views

How to fetch Records matched with Comma separated value stored in table column

I have data stored in this format. topic_id tags 1 `tag1,tag2,tag3` 2 `tag1,tag4,tag5` 3 `tag2,tag4,tag5` 4 `tag6,tag7,tag2` I want Mysql Query to ...
0
votes
1answer
113 views

Speed up query that have joined tables and function calls

I have a Bootstrap Table that use server side pagination to display results from a query like this: SELECT Item.a, Item.b, Item.c, Info1.Name, Info1.Name, Info2.Name, ...
0
votes
1answer
21 views

Insert data into oracle table with loop?

How to use sql to insert line detail data into header with the output like below. line detail MO_ID SEQ_NO QTY LEVEL 121M 2 3 121M 3 9 125M 2 6 125M 3 7 125M ...