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

learn more… | top users | synonyms (2)

3
votes
2answers
787 views

SQL Server 2008 R2 Mirroring issues

I have this very frustrating problem that I've been trying to figure out for almost a week now. I have two SQL Server 2008 R2 STD w/SP3 box running on Windows 2012 R2 with two instances each, Server1 ...
1
vote
1answer
62 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 ...
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 ...
4
votes
1answer
116 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
52 views

Oracle equivalent of sp_rename in SQL Server

I am trying to rename a column of multiple tables in Oracle using a cursor over a table that has all the information of other table. In SQL Server I was able to do it correctly using sp_rename. I am ...
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, ...
0
votes
1answer
30 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 ...
-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 ...
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
1answer
81 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 ...
1
vote
1answer
3k views

eCommerce product table design - MySQL

I am designing database for an web application, I have designed the product and specification details database as follows, create table pr_size ( size_id int not null auto_increment, ...
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 ...
1
vote
2answers
13k views

exporting oracle tables on command line as sql files

I use exp and expdp to export database tables on command line but it generates binary .dmp files. I want to export/import tables as .sql* files on command line like pl/sql developer app can do. Is ...
9
votes
2answers
3k views

How to delete a procedure which name is ambigous?

I am using informix... I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a DROP PROCEDURE myProc; then i get a error message ...
0
votes
1answer
109 views

Oracle: Replace corrupted first datafile

A datafile associated with a tablespace has been corrupted (got truncated to size 0). I don't mind losing the data, because I have a full DB dump which I can import. Now I want to replace this ...
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 | ...
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 ...
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, ...
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 ...
8
votes
3answers
520 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
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 ...
0
votes
2answers
168 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
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
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 ...
6
votes
1answer
175 views

Create a function to generate a column value in a specific format

I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, ...
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, ...
0
votes
1answer
31 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 ...
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
759 views

Msg 39011 SQL Server was unable to communicate with the LaunchPad service

I was playing with the new "R" scripting in SQL Server 2016 CTP 30. Finally got it working, thanks to this site. I've had a problem that I'm sure others will run into as well. All of a sudden the "...
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....
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 ...
1
vote
1answer
46 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, ( SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p....
2
votes
1answer
99 views

Update column with different value for multiple rows using a csv file

I often find myself using Excel to create update queries. When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple). Here's ...
0
votes
1answer
124 views

Update query for Minimum absolute difference in Access [SQL]

I have 3 tables in an Access db. Table_1 and Table_2 are where the source data comes from and I am filling Table_3. Table_1 has the following fields: Acct_Name Amt Table_2 has the following ...
1
vote
1answer
31 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, ...
0
votes
1answer
215 views

Suitable schema to record email sent and response received and invoicing in DB

When a form submission is made on a website: An email with form's details is sent to a set of recipients email addresses stored in a DB. The email contains a link for the recipient to show interest. ...
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
39 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
2k views

How can I select data from two differents servers?

I have two databases: data1 and data2 in two different servers (server1 and server2). I am trying to join two tables of my two databases in MySQL like this: SELECT left(a.code,9) as fourn, ...
0
votes
1answer
35 views

Inserting two tables together with no id or unique anything

Let's assume we have two tables: table1 Name Height Age Mary 1.60 18 Mary 1.62 19 Mary 1.68 20 John 1.80 17 John 1.85 18 John 1.86 19 table2 Name Height Age Mary 1.60 18 Mary 1.62 19 Mary 1.68 20 ...
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 ...
5
votes
2answers
147 views

Why a SQL Server Table takes 1GB more after adding 55MB of data?

I've found that one customer database size exploded between yesterday and today. After investigation I've found a design problem in a table schema but after doing some math I know that 55MB of data ...
1
vote
1answer
39 views

Get last login time of each User ID

How to get last login time of selected IDs? Table | user_id | lastlogin | 100 | 2016-03-05 | 110 | 2016-03-21 | 100 | 2016-03-29 | 100 | 2016-04-03 | 110 | 2016-...
0
votes
1answer
37 views

Update a valid column when another column has changed

We have a valid_phone column that just stores 1 or 0 for valid or not valid. When the user changes their phone number we want that field to go back to 0 so they will have to validate their number ...
3
votes
3answers
265 views

9 digit zip codes are reporting LEN of 12

I am attempting to resolve some difficulties in getting zip codes to display properly. The original spreadsheet has zip codes of mixed 5 and 9 digit formats. After the import process, these 9 digit ...
5
votes
4answers
343 views

Summing data based on distinct dates

I am trying to get an accurate count of total days a tool is out for rental. Here is a data sample: CREATE Table #tmpToolRentalDays ( ToolId BIGINT, StartDate DATETIME, ...
0
votes
2answers
38 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 ...
4
votes
1answer
219 views

Reporting on a normalized Key-Value pivot

ERD & SQL Fiddle provided. We're trying to design a reporting tool for our stakeholders around a normalized Key-Value Pair model. We've tried to find a solution using Pivot Tables but can't seem ...