DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
0answers
27 views

Table functions unnest, cardinality not found in DB2 10.1 LUW [on hold]

I'm having difficulty in trying to re-deploy a stored procedure in DB2 10.1 LUW. CREATE TYPE "TBSGL"."ACCT" AS ROW( ACCT_NO VARCHAR(10), ACCT_DESC VARCHAR(40)); CREATE TYPE "TBSGL"."ACCT_ARR" AS ...
0
votes
1answer
26 views

Oracle: How to create secondary index based on every word in a CLOB

I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX ...
1
vote
2answers
40 views

MySQL Escape Characters

I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP. This is an example: The name to ...
0
votes
1answer
52 views

How to insert values through inner join

Could anybody tell me how to do insert statement with inner join. i am trying to insert ProductName,ProductCompany,ProductPrice,ProductQty in Product table and CategoryName in category Table. And ...
-2
votes
2answers
52 views

Syntax error on JOIN [closed]

I keep getting syntax error on my join and I can't figure it out: Syntax error in JOIN operation SELECT [tblJobInfo].[Job Name], [tblJobInfo].[ShipDate], [tblJobInfo].[ShipVia], ...
-1
votes
0answers
32 views

Database(In Recovery) Mode [closed]

I am using SQL Server 2008. My database is often switching to (In Recovery) mode. But still i can access the database through SSMS. When I try to access my database from ETL tool it is not ...
1
vote
1answer
21 views

Sql parent Child Query not working

I want to get parent and their child data which means, parents first, then children. Below is the query that I tried to convert to parent and their children SELECT a.FactorTitle, a.FactorCode, ...
0
votes
1answer
24 views

Pivot Results By Day [duplicate]

I have a table in SQL Server 2008, with mails received from a pop account. The table has the following columns: +------+----+---------+------+------+ | from | to | subject | body | date | ...
0
votes
1answer
37 views

Connecting to the default instance of SQL Server connects to a named instance

I have 2 instances of SQL Server installed on one server: SQL002. One instance is a default instance, SQL2008 R2, and the SQL service is generally not running except for specific tests. The other ...
2
votes
1answer
50 views

SQL query to identify missing hinted indexes

We have a fairly large number of queries using index hints in our 11.2.0.4 database. I recently uncovered some performance issues and found that some of the indexes referenced by the query hints were ...
0
votes
0answers
21 views

Query to get result in Excel file [duplicate]

The following query gives me the error Incorrect syntax near 'OUTPUT'. SELECT * FROM dbo.sales ; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c:\\test\\sales.xls; READONLY=0' INTO ...
1
vote
1answer
28 views

table or view does not exist when using insert into

I'm trying to insert into but getting the "table or view does not exist" error. It tells me the specific line where the error occurs but I can't figure out what the problem is: INSERT INTO c_ord_cur ...
1
vote
0answers
27 views

Howto measure improvement after create and move new filegroups in SQL Server

How can one measure the (hopefully) positive effect of moving couples of indexes to a different finegroup, so to say seperate them from the data filegroup, and move this filegroup to another LUN in ...
0
votes
1answer
30 views

Batch for PostgreSQL query

1) the pgpass.conf (of PostgreSQL) is: localhost:5432:*:postgres:mypassword I haven't experience in batch and I have spent the day searching in the net. I'm using windows if it helps. 2) I tried ...
4
votes
2answers
155 views

Distinct Combination of Two Columns

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other. The data is all around backup policies being run on against ...
2
votes
1answer
10 views

Alternate in access database

I want to check whether a row exists or not before inserting a new one in Access 2007. I have the following working query in SQL Server but I'm having trouble converting it to Access. update category ...
1
vote
0answers
28 views

HQL pagination returns same result on different pages - with SQL

We have the following query in the server (using sql): Query query = this.entityManager.createQuery( "SELECT type, id, date, amount FROM Transaction ORDER BY type ASC"); The type field ...
1
vote
1answer
41 views

Merging adjacent date intervals into a single row

Here is the scenario: If the employee transaction is continuous display as-is. if the employee got any gap display as a new row. Below is the sample table Emid Fromdate Todate ----- -------- ...
0
votes
1answer
27 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
2
votes
1answer
22 views

Problem with “not” in full text search

I don't understand why I'm getting these results with my full-text search query in SQL Server 2014. The following query returns results with "Supervisor" in the Title field and "Tokyo" in the ...
2
votes
1answer
32 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
4
votes
1answer
45 views

How to deduplicate SELECT statements in relational division?

I have a query that does a lot of duplicate work: SELECT visitor_id, '1'::text AS filter FROM events WHERE id IN (SELECT event_id FROM params WHERE key = 'utm_campaign' AND value ...
1
vote
1answer
21 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
2
votes
2answers
78 views

Select Count of one table into another

I have one SQL statement as: SELECT ARTICLES.NEWS_ARTCL_ID, ARTICLES.NEWS_ARTCL_TTL_DES, ARTICLES.NEWS_ARTCL_CNTNT_T, ARTICLES.NEWS_ARTCL_PUB_DT, ARTICLES.NEWS_ARTCL_AUTH_NM, ...
0
votes
0answers
41 views

Upsert - Should I verify the row exists first, attempt an update, or try to insert first?

In a software that I'm working, I have a table with a few columns and a id. In some events in my software, I need to update some column of a record with a certain unique field (other column), but ...
1
vote
3answers
69 views

Finding “islands” of holiday periods for employees

I'm looking for some tips to find a nice solution for my problem: Let's say I have a table, where days of vacation of employees are stored. e.g. CREATE TABLE test_dates ( id NUMBER ...
1
vote
1answer
40 views

fetch data from two tables

I have two tables one for property detail and other for amenities. In amenities i have inserted data one by one means it has three column amenity_id,property_id,amenity. Now I want to get the property ...
2
votes
1answer
64 views

How do i reverse this SQL Query Script, I am trying to remove filestream from my SQL 2014 Server

I am trying to remove filestream from my database, so I have to do the following I would like to know how to remove the following: ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL ...
1
vote
0answers
11 views

MS Access: Subform only displays first entry. Continuous Form follow-up question

I am new to MS Access and Database creation in general. Currently I am working on a simple employee database that can view an employee's information and can show their current (and previous) projects ...
1
vote
2answers
76 views

FLUSH TABLE : can't execute the given command because you have active locked tables mysql

If i execute these queries one by one , all get fail except first : Query1 : flush table tablename1 with read lock; Works Query2 : flush table tablename2 with read lock; Error : can't ...
2
votes
0answers
34 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for ...
1
vote
1answer
18 views

Logins mapped to dropped database sql server

I was told to delete the logins that are mapped to a database ABC but unfortunately the database ABC has been dropped and no backups exist. Is it possible to find the logins that WERE mapped to ABC? ...
6
votes
2answers
263 views

Trouble with a NOT IN query

SELECT DISTINCT d.customer_id, d.date_added FROM `order` d WHERE d.customer_id NOT IN ( SELECT DISTINCT i.customer_id FROM `order` i WHERE i.date_added > '2015-02-15 14:00:00' ) ORDER ...
5
votes
1answer
56 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
3
votes
1answer
80 views

How to enforce a constraint over multiple tables?

I have the following five tables: PRODUCT FEATURE PROVIDER PROVIDER_PRODUCT PROVIDER_FEATURE The logic is as follows: A generic PRODUCT can have one or more FEATUREs. There are different ...
1
vote
1answer
13 views

Feature of database-level audit in SQL Server 2012 Express edition

Like in SQL Server 2012 Enterprise edition, can we have database-level audit in SQL Server 2012 Express edition? Can we have Audit Folder in Security Tab?
1
vote
1answer
36 views

PostgreSQL “size of temporary files”

I've imported data into a new database (about 600m rows of timestamp, integer, double). I then created some indexes and tried to alter some columns (got some out of space issues), the database is ...
3
votes
2answers
69 views

How does TOPN influence a query with group by?

I have a query that looks like the following: Select t1.field1, t2.field2, sum(t1.field3) From t1 inner join t2 on t1.id = t2.id Group by t1.field1, t2.field2 It's simplified, normally there are a ...
3
votes
1answer
24 views

Inventory management in Rails

I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot ...
2
votes
1answer
99 views

How to join all combinations from two tables with a common column

I have a list of suppliers who supply a list of countries. For example: Supplier A supplies to: USA France China and another list of suppliers with their suppliers' factories For example: ...
2
votes
1answer
44 views

How to insert values in junction table for many to many relationships (MySQL)?

I'm new to relational database design and I've hit a stumbling block. I have three tables... PROJECTS project_id, name, ... PROJECTS_UNDERGROUND projects_FK, underground_FK (I have both FKs setup ...
1
vote
0answers
14 views

MaxDB - Strange behaviour when selecting from a CLOB-Row

I am using "sqlcli" for Linux to write the output of the following query into a file: select clob_field from table (...) in SAPGui (original user input) the contents are displayed as: First ...
3
votes
0answers
98 views

How to manage hundreds of SQL jobs? [closed]

I work in Microsoft SQL Server database and I have to monitor and manage hundreds of SQL Jobs to see which job will execute on what day and what time. I also have to troubleshoot the failure of the ...
0
votes
1answer
20 views

Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2

I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins ...
2
votes
2answers
63 views

MySQL: memory requirements for django app with 1 big table (460M rows)

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows). CREATE TABLE `genescorrelation` ...
5
votes
2answers
92 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
1
vote
1answer
18 views

mysql - select random rows with max 3 rows per parent id

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
2
votes
1answer
125 views

Order by values with most incidence

I have a table as this example: CREATE TABLE customerEvent( customerID int NOT NULL, eventID int NOT NULL ) customerID may appear in many records. I use the following query to identify ...
0
votes
1answer
83 views

Find columns with different values compared with a reference row

My table, MARC, has more than 200 columns with WERKS (plant) as the key. Most of the entries in MARC have values that come from a reference plant. So, usually, all the 200 columns will contain the ...
3
votes
0answers
100 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...