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
112 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
1answer
20 views

Create a view showing total minutes per customer per month and change from previous month

I have an existing database (SQL Server 2012, on premise installation) and need to add a column to a view which gets displayed via a Crystal Report. The issue I am having is with restricting the ...
0
votes
0answers
37 views

Shrink .ldf file issue

These are the timing of some jobs on SQL-Server: rebuild indexing at 10.30 am (all timing in IST) reorg indexing at 11 am full backup at 12.30 pm transaction backup every 2 hours truncate log ...
1
vote
0answers
37 views

Under which conditions would selecting a small column off a primary key be slow?

Usually querying the table is in the millisecond range at most. But sometimes it takes 30 seconds and then times out. What could cause SELECT smallColumn FROM SomeTable WHERE pkID=@pkID to be slow? ...
2
votes
1answer
8k views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
0
votes
2answers
31 views

Display list of SSRS reports in a dropdown list, and allow users to select which report to display

Our company has about 10 different SSRS reports. We would like to allow users to navigate from one report to another simply by selecting the report from a dropdown list. Is this possible?
0
votes
2answers
22 views

Read CSV and perform SQL in SQL Server Management Studio

Is it possible in SQL Server Management Studio to read a CSV and perform an SQL update per line? Example data.csv "column1","column2,"column3" "column1","column2,"column3" "column1","column2,"...
-2
votes
2answers
63 views

Query taking very long time in 12C environment, but the same query is taking just a few seconds in 10G production environment

We are migrating a database from 10G to 12C and currently we are testing in a dummy 12C production environment. Particularly 3 SQL queries are taking almost 50 min. time to complete and show the ...
1
vote
1answer
12 views

Selecting entries with the same count as in another table

I'm using a SELECT statement to select multiple IDs and than use GROUP BY ID HAVING COUNT(*) = someNumber to only have the IDs with a certain count. What I however actually want to do is to select ...
0
votes
1answer
16 views

Query to map a particular database to a syslogin

I need to map a database to a syslogin. Following is the query I have used to create login and make it a db_owner but after the execution, I can see a tick as db_owner for that database in ...
1
vote
1answer
24 views

Get relation data on comma separated values

I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table. I have one table "Form table" +------+----------...
0
votes
1answer
22 views

Merge sub-queries to a single query

I am trying to merge the nested MYSQL queries into one First of all I fire this query select names , city , category from names_data group by names , city ,category ; this produces a result of ~...
1
vote
3answers
881 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...
3
votes
2answers
1k views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
1
vote
1answer
64 views

SQL Server 2014 Triggers and where are they created

I am on SQL Server 2014 Standard edition. I am developing a Supply Chain DW and I am currently in the process of building a Landing schema which will be used to Land data coming out of the source ...
4
votes
1answer
224 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 ...
0
votes
0answers
31 views

How to visualize Redshift EXPLAIN query plan?

I've tried many different tools such as pgAdmin, DataGrip, http://explain.depesz.com, etc. None of them, however, seem to support Redshift's query plan with what I've tried. For simple queries, the ...
0
votes
1answer
173 views

Error code 1064 when trying to add anything manually

So i tried adding via php and i noticed i got a error , so i went into phpmyadmin and tried adding it manually by clicking the SQL in phpmyadmin and write in INSERT INTO `test`(`IDtest`, `...
0
votes
1answer
79 views

Is it beneficial to include ORDER BY fields in the list of SELECT fields? [closed]

I'm just wondering when you do a query and you use ORDER BY should the fields you use in the ORDER BY clause always be included in the SELECT list even if you don't need them? Is this beneficial in ...
0
votes
1answer
169 views

Switching a partition on a table with one to many relation

There is two main tables in database (SQL Server 2014) table1: ---------------------------------------------- id name datetime image table2: -------------------------------- ...
1
vote
2answers
51 views

Full Join Query

I am doing a full join query from two tables. SELECT i.idinasistencia, i.fecha, i.valor, i.justificado, h.id, p.nombres, p.apellido_paterno, p.apellido_materno, p.rut_persona FROM historialalumno ...
0
votes
0answers
20 views

Basic Inventory/Stock Schema

Previously, I thought I needed an "attributes" table based on their category/subcategory (Medicines have "Dosage" but Soap doesn't right?) ... but while trying an example I made up in my head, ...
2
votes
1answer
196 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
0
votes
3answers
32 views

How do I REPLACE values in column after querying

I have a column with numeric values I am querying SELECT column FROM table WHERE column='1' OR column='2' OR column='3' OR column='4' THEN REPLACE (column, "1", "ABC"); REPLACE (column, "2", "...
3
votes
1answer
58 views

How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?

I am stuck on implementing a left join on dates for each individual customer. I have a table which stores customer purchases: SELECT purchase_date, customer FROM purchases I would like to create a ...
0
votes
1answer
33 views

SQL Server IOPS

I'm trying to evaluate the IOPS of TPCH queries on SQL Server. Specifically, for TPCH query 13, using "set statistics io on" and "set statistics time on", I got: Table 'orders'. Scan count 9, logical ...
0
votes
2answers
5k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
1answer
48 views

Group COUNT by month less than variable

In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus: CREATE TABLE [dbo].[Employee] ( [employeeID] [int] IDENTITY(9500,1) NOT NULL, [firstName] [nvarchar](50) ...
2
votes
1answer
112 views

Very slow merge replication synchronization

I had an unforeseen issue with a sub going down (generic error, was probably a connection issue) and now that I've reconnected the sub it is insanely slow re-syncing. It seems to download about 1000 ...
0
votes
1answer
74 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
1
vote
1answer
1k views

Printing comments or statements when executing script to a output file using TOAD/ORACLE

Coming from a SQL SSMS environment, I would like to duplicate their Print Command which allows you to print commands or variables while executing a query. I am looking for this feature to help ...
6
votes
6answers
30k views

Tempdb is getting full very quickly in microsoft sql server 2008

I am developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly . Is there any solution to clean the tempdb file in microsoft sql server 2008 on ...
0
votes
0answers
18 views

Write sql query to store alarm details for next 1 week with given user id [migrated]

I am making a scheduler where currently I am saving details for reminder set by client with specific user id. Now I want to add a button of Repeat weekly where the alarm will be repeated for next 7 ...
0
votes
1answer
41 views

Database design help. many-to-many relationship

I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of ...
3
votes
1answer
1k views

Slow Performance of Parametrized Query in Excel due to Parameter Datatype

I'm pulling Data from a SQL Server 2008 into Excel using a Parametrized ODBC Query created by the Excel Wizard. (Data-->getExternalData/FromOtherSources-->FromDataConnectionWizard-->ODBC DSN) My ...
0
votes
1answer
180 views

ALTER SESSION from a Control File

I am using a Control File to import data from a CSV file into a table, using sqlldr to import the data, where a large number of fields are numeric values with integer and decimal part separated by ...
0
votes
1answer
29 views

When defining a foreign key in this Oracle example, what is the significance of the CONSTRAINT keyword?

In the code below, The first column I create for the table courses is a column called department_code. I then define fk_courses_department_code and specify that department_code in this table will be a ...
1
vote
0answers
24 views

Database design - many-to-many relationship between three columns [duplicate]

I'm noob in database. So don't mind if its a silly question.This is used in my application and is the first one. I need a database design for it. value 4 is dependent on the other value 1,2&3. ...
0
votes
1answer
148 views

SQL Server 2014 Buffer Pool Extension - There is insufficient memory available in the buffer pool

I am running Enterprise Microsoft SQL Server 2014 - 12.0.4213.0 (X64), with a 288GB of Ram, with Sql max memory set to 280. Twice in the last month I have received the There is insufficient memory ...
0
votes
0answers
36 views

Oracle rows to columns

I have the following question: I have the following resultset for my query: SELECT ID, quantity, date FROM table: id quantity date 32758669 255150 17.08.2016 32758669 271350 24.08....
1
vote
1answer
242 views

Query multiple rooms with different conditionals (Adults, Children)

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
0
votes
2answers
15k views

How to Replace (“ ”) with no space(“”) in one column mysql?

I have table Like This ID | Propinsi | Kota | _________________________ 1 | Aceh | Denpasar 2 | Aceh | Banda Aceh 3 | Sumatera | Asahan This table have many rows The Problem is I want ...
4
votes
2answers
2k views

How to separate out individual sql statements from Oracle SQL script for execution from java code

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, ...
0
votes
0answers
38 views

how to get the data from table

Before this query is run an activity name (e.g. ‘Art’) should be assigned to a variable. The query should then output a list of the children registered for the specified activity. The output should be ...
1
vote
1answer
38 views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
2
votes
2answers
55 views

Database design - summary table vs always counting over tables

Imagine a system where a user can make a whole lot of different actions, like: Making a purchase Liking something Opening a specific view (pretty much every click) Inviting a friend ... The ...
3
votes
2answers
206 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
0
votes
1answer
609 views

Connect by prior - multiple fields and query efficiency

I have 2 tables one of which defines the hierarchy for the other equipment id desc 1 item1 2 item2 3 item3 4 item4 5 item5 6 item6 7 item7 8 item8 ...
0
votes
0answers
31 views

How to automatically update a table derived from another table when the latter is updated

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
2answers
157 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 ...