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

learn more… | top users | synonyms (2)

1
vote
0answers
27 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
1
vote
0answers
21 views

Why does SemiJoin node receive so bad statistic even after runnning ANALYZE

PosgtreSQL 9.4 I have two tables: CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer ); CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL ...
1
vote
0answers
103 views

Backing up / restoring a 20GB database with images stored as binary code

Please excuse me as I am a beginner. I am trying to backup and restore a 20GB database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues: The developers ...
1
vote
0answers
64 views

SQLite reference table question

I'm using SQLite and Python for making a sensor network database. I have a table holding the nodes: NODE, another 2 tables for sensor specification: SENSOR_TYPE ("Temperature, Humidity...) and ...
1
vote
0answers
47 views

Get a parent and the children using a child ID or the parent ID

I have a hierarchical table with 2 levels of depth – that is, parents and children. Having a parent ID, I want to extract the parent and its children. Having a child ID, I want to extract the parent ...
1
vote
0answers
45 views

Matching same unknown characters in two columns

So here is the situation. I have a database with values in columns that look something like this field_0_data1, field_0_data2, field_1_data1, field_1_data2 Is it possible to only select the columns ...
1
vote
0answers
44 views

Find the last record of group of overlapped records

I have a little dilemma with filtering out result. The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem: I ...
1
vote
0answers
73 views

SQL query using CTE not working after database migration

We have recently migrated an application, and in the process we have migrated from SQL Server 2005 to SQL Server 2008 R2. I have noticed that some SQL queries using CTE do not work anymore since. I ...
1
vote
0answers
88 views

Secure view on row level with custom security tables

Let's say I have a following table that lists all member of my application. CREATE TABLE dbo.Member ( MemberId BIGINT, LoginName NVARCHAR(64), SecurityRole NVARCHAR(64) ) I assume that ...
1
vote
0answers
63 views

Whats the difference between the following queries? Getting the top 3 salaries for different department

I am practicing SQL and came across this issue. I am trying to find the top 3 salaries for each department. The table structure is basic with one employee table with columns ...
1
vote
0answers
82 views

Queries which return more than 100k rows

I have a following query SELECT * FROM table_name WHERE #3_column BETWEEN start_date AND end_date which is taking about 10 seconds on my computer to execute and returns about 180k rows. Table schema ...
1
vote
0answers
15 views

Opponent Winrate Over another opponent sql

I am trying to find the most efficient way to build a counter warehouse. shortened the tables just to needed info for question Match Table MatchId, version, type MatchParticipants matchId, ...
1
vote
0answers
106 views

MongoDB very poor query performance vs Oracle

I'm a MongoDB noob and trying to import data from an Oracle table. My Oracle table has 1 unique index on a single column, which is an id (a number - primary key). I successfuly imported data to ...
1
vote
0answers
50 views

How does PostgreSQL compute the cost for index scan?

Consider the following plan: Index Scan using foo_c1_idx1 on foo (cost=0.00..8.38 rows=1 width=37) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (c1 = 500) Buffers: shared hit=4 Total ...
1
vote
0answers
27 views

what is getmore / cursor batch

In MongoDB world you see often getmore. For example in mongostat. What is that exactly? What would be the analogous term for MySQL/MariaDB or Oracle RDBMS?
1
vote
0answers
90 views

What would be the rule for parsing parameterized sql for Netezza to be used with nzsql?

I have a bunch of legacy parametrized SQL templates that were used by nzsql utility for Netezza. See examples here: http://queforum.com/netezza/347918-parameterized-sql-file-input-nzsql.html What is ...
1
vote
0answers
168 views

Two servers with the same exact specs taking way different times to run the same query

I have 2 servers with the same exact specs: Test Server and Production Server The same exact database with the same exact data is on both of these servers. I remote into each server using remote ...
1
vote
0answers
31 views

How to parse and display row data as Column

I'm trying to display the row data from tbl_cte as a column. The result of column1 is alphanumeric e.g : "Data1.2.3". I need to parse it and display the "Data" as the name and "1.2.3" as the version ...
1
vote
0answers
85 views

Sql query , please check whether my solution is correct?

Problem is: Consider the following relational schema: COURSES (cno, cname) STUDENTS (rollno, sname, age, year) REGISTERED FOR (cno, rollno) The bold attributed indicate the primary keys for ...
1
vote
0answers
18 views

How to copy the query results from the grids when having more text in the single column

when having more text in the single column the result is trimmed out to diffrent rows when copying to excel.This is working fine in SQL 2005 mgmt. studio but in later versions (2008,2012) the result ...
1
vote
0answers
108 views

MS Access 2010 report - SQL pivot query WHERE criteria not working

I have created a report that has a list of actions which are populated from an "Actions" table. (See Below) However, each action will have multiple updates populated from an "Updates" tables and I ...
1
vote
0answers
54 views

How do I correctly group output from multiple tables?

I have more than 2 tables where entries are associated with dates. Each table can have multiple entries per date. I want to produce output that lists a date, then lists the entries of each table for ...
1
vote
0answers
31 views

Can you optimise the choice of array for the Oracle IN clause

I have a table which holds timestamped streaming data for events, it can be created like: create table event_data ( event_id integer, date timestamp, ... -- other data corresponding to ...
1
vote
0answers
29 views

Can i use PGP for securing My database

I have considered TDE and cell level encryption mechanism for securing my database. but these two cannot fully satisfies my requirements.through surfing i found that PGP may help me, but it is mainly ...
1
vote
0answers
30 views

Looking for a DR-like solution to enable full data warehouse re-load while having secondary copy live

I am looking for suggestions/ideas to the following problem. We have a number of data warehouses for each of our clients (on SQL Server 2014). These data warehouses are maintained fairly fresh with ...
1
vote
0answers
181 views

Update remote MySQL database upon changes in local SQL Server database

I have a Web store running with MySQL on the remote host. And I have inventory management software using Microsoft SQL Server on my local system. I want to update product stock in the web store when ...
1
vote
0answers
50 views

How to make a query from a database with schemas to a database without schemas, and vice versa, with DBLINK in postgres

I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions. But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i ...
1
vote
0answers
69 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
0answers
96 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
0answers
95 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
0answers
24 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 ...
1
vote
0answers
189 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
1
vote
0answers
4k views

The target principal name is incorrect. Cannot generate SSPI context - SQL Server 2012 error

I don't understand why this error is popped up suddenly. It was working till yesterday. It is a stand alone env. Problem is when me or any user wants to connect from their machine locally it is ...
1
vote
0answers
51 views

SQL Server Profiler

I had run a SQL trace on SQL server 2008 r2 and had originally saved it to the table format. This table now resides in the database and I was wondering could I export the contents of the table to a ...
1
vote
0answers
42 views

I need to find the “rank” in a sub-set of entries

I am currently working on some sort of a highscore list. But values displayed are individual for each user, ie. limited by constraints. One constraint that I am currently working on is "being part of ...
1
vote
0answers
146 views

Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many ...
1
vote
0answers
539 views

SQL Agent job history not found for few jobs

I have;backup ,rebuild Indexes , Update Stats and few other jobs like space alerts, replication jobs etc which run frequently on SQL Server 2102-SP2, Build Number:11.0.5058.0 and I do not see any ...
1
vote
0answers
63 views

MySQL Get first and last day of multiple records

I have a table HC_RSPI_data: | RADIO_TYPE | HOP | TOWARDS | RSPI | TP_NAME | DATE_TIME_END | TIME_LENGTH | RLTM_min | RLTM_max | ...
1
vote
0answers
183 views

How to resolve Error 7311 returned by a job running against a linked server?

I have a job and a linked server. The job was working fine for a while, but recently I started getting this error message and I couldn't find a soloution for this. Executed as user: ... Cannot ...
1
vote
0answers
8 views

JDBC - maximum allowable cursors - how do I see my open statements?

I've got some resource leaking and I'm trying to find what's causing them. I think I have some statements or resultsets that aren't being closed properly. Is there any way to see all of the cursors ...
1
vote
0answers
47 views

Creating a dynamic database

I'm using mysql server connected to Access, and I want to do a form, to fill the table. My question is how to do it. I need to select the data from a listbox, that's okay this is already done. And I ...
1
vote
0answers
62 views

Get all data from two tables and group it into different category in mysql

These are my tables to build an FAQ: CREATE TABLE `tbl_section` ( `section_id` int(11) NOT NULL AUTO_INCREMENT, `section_title` varchar(100) NOT NULL, PRIMARY KEY (`section_id`), ...
1
vote
0answers
29 views

ANY in postgresql gives weird error

when I do: SELECT ARRAY[4] I get array integer[] ------ {4} and if I do: select * from b where b.ID = ANY (array [4]) That works. Now, something else: select array_agg(4) I get: ...
1
vote
0answers
56 views

Complicated MySQL table reorder

I should do really complicated tables reorder. ****The main table looks like:**** id - some data 1 - ... 2 - ... 3 - ... 6 - ... 9 - ... 12 - ... As you can see, there are "empty" IDs (no 4, 5 ...
1
vote
0answers
58 views

Update all foreign keys to a different primary key

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. ...
1
vote
0answers
163 views

Problem forming a query with subqueries in design view? (MS Access 2010)?

I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql. That being said, I cant seem to ...
1
vote
0answers
37 views

How does 'exists' affect output

I previously asked a question about how to organize a long search query. I have taken it apart. I am presently attempting to understand the function of some of its parts. Specifically, I am ...
1
vote
0answers
37 views

Need some help building a query to get info from several tables

I have this semi-complex table structure: And I need to return this data: { "id": "10111001", "rep_id": "30000001", "targets_id": "60011001", "brands_id": "10010001", "brand_name": ...
1
vote
0answers
179 views

SQL 2008 R2 Agent stopping sp_who2 running and can't view database or Agent properties

We're experiencing an unusual issue with SQL 2008 R2 SP2 (10.50.4297) where the SQL Agent had stopped but would not restart (this has been fine for months). After investigating the issue we had to ...
1
vote
0answers
255 views

How to connect to a |Database from SQL Script, in SQL developer

Hi i am trying to connect to a DB using a connection string mentioned in a Script file.. in SQL developer. I am using the below format Connect USERNAME/PASSWORD@HOSTNAME:PORT/Service_name.. But its ...