Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. Answers to questions tagged with SQL should use ISO/IEC standard SQL.
155,144
questions with no upvoted or accepted answers
24
votes
0
answers
870
views
SAP Sybase SQL Anywhere NullReference Exception when openening and closing many connections in a service
Currently I've the problem that SAP Sybase SQL Anywhere randomly throws NullReferenceExceptions in a service which executes a lot of sql queries. The connections are always created in a using block ...
- 20.5k
23
votes
1
answer
696
views
ODCIAggregateMerge without parallel_enabled
These are quotes from Oracle docs:
[Optional] Merge by combining the two aggregation contexts and return a single context. This operation combines the results of aggregation over subsets in order ...
- 935
17
votes
0
answers
2k
views
How can I control the SQL table aliases that Hibernate uses in its generated queries?
tl;dr: Hibernate automatically generates SQL table aliases in its queries like jurisdicti4_ or this_. Here's an example query:
SELECT
this_.id AS id2_6_3_,
this_....
- 3,270
17
votes
3
answers
3k
views
Azure SQL stored procedure ridiculously slow called from C#
Summary:
We have two identical databases, one on a local server, one on Azure.
We have a C# system that accesses these databases, calling stored procedures.
The stored procedures are running very, ...
- 712
15
votes
3
answers
1k
views
Doctrine - Multiple models referencing same id field in another model
I have a Files Model, and Multiple (currently 3) different other Models (Article, Job, Event) that can all have files, that are stored in the Files Model.
The problem is that when i generate the ...
- 3,970
13
votes
2
answers
265
views
SQL update is using old column value
Given this table
create table FOO
(
ID number(19) primary key,
DATE1 DATE default sysdate,
DATE2 DATE
);
DATE1 is initialized with sysdate when I insert a row, then set to null and then ...
- 5,018
13
votes
1
answer
8k
views
How to create an empty array of struct in hive?
I have a view in Hive 1.1.0, based on a condition, it should return an empty array or an array of struct<name: string, jobslots: int>
Here is my code:
select
case when <condition>
...
- 955
12
votes
6
answers
3k
views
Insert row if not exists without deadlock
I have a simple table
CREATE TABLE test (
col INT,
data TEXT,
KEY (col)
);
and a simple transaction
START TRANSACTION;
SELECT * FROM test WHERE col = 4 FOR UPDATE;
-- If no results, ...
- 6,885
11
votes
3
answers
3k
views
Sequelize query with a where clause on an include of an include
I'm struggling to create a query with sequelize.
Some context
I have the following models:
A Manifestation can have [0..n] Event
An Event belongs to one Manifestation (an Event cannot exist without ...
- 11k
11
votes
1
answer
428
views
Is there a VSCode extension/setting for SQL files that will highlight the column of the selected value?
This feature on IntelliJ by default, but my employer requires me to use VSCode.
It's very tedious to look at an SQL file and have to count the columns to get to the see if you're looking at the ...
- 111
11
votes
0
answers
3k
views
Postgresql: partial foreign key?
Is it possible to create a partial foreign key (similar to partial indexes) in general sql / postgresql? I have not found a way to force referential integrity in the following case:
Table A is soft-...
- 1,990
11
votes
1
answer
6k
views
Choose a random row as aggregate function in Hive
I want to group by a column and then select random rows from another column. In Presto, there's arbitrary.
E.g. my query is:
SELECT a, arbitrary(b)
FROM foo
GROUP BY a
How do I do this in Hive?
...
- 25.4k
11
votes
1
answer
11k
views
Check if the unique constraint exists and drop it using liquibase
I have a changeset wherein I initially check if the unique constraint exists and then if it does it will drop the constraint.
<changeSet author="loren"
id="...
- 1,280
11
votes
1
answer
3k
views
How to map native sql results to oneToMany field with SqlResultSetMapping in JPA
For example, say I have the following database tables
Building
-----------
id
name
and
Room
-----------
id
roomNo
buildingId
the Building Java class will look something like
@Entity
public class ...
- 1,684
11
votes
1
answer
14k
views
CHAR(64) or BINARY(32) To Store SHA256 Hash in SQL SERVER
I am debating which datatype to use when storing a SHA256 hash in SQL Server. Should it be CHAR(64) or BINARY(32) ... The column will be part of a unique clustered index. I know that I'm probably ...
- 4,728
10
votes
1
answer
194
views
How to fetch all rows which will be removed on using cascading delete before delete query
What is the best approach to fetch all the rows, which will be deleted on cascade of primary key?
At present we know the list of tables which uses the primary key as foreign key, a simple select query ...
- 575
10
votes
0
answers
5k
views
How to get all tables detailed information under specific schema in AWS Athena
I am getting the tables detailed information in Hive with the below query, but I am not finding the equivalent in Athena.
use schema_name;
SHOW TABLE EXTENDED LIKE '*'
As part of the output of above ...
- 507
10
votes
2
answers
13k
views
Missing comma before start of a new alter operation. (near "CHARACTER SET")
Mysql - phpmyadmin
What's wrong with this query?
ALTER TABLE `invoices`
CHANGE `status` `status` ENUM('paid','due','canceled','partial','cheque')
CHARACTER SET `utf8` COLLATE `utf8_general_ci` NOT ...
- 518
10
votes
3
answers
3k
views
Processing Emojis in SQLite
I am hoping to identify which emojis are used most in a text conversation using SQL Lite. I am using DB Browser and the emojis show up like they do in iMessage (see below picture), but I am stumped on ...
- 537
10
votes
4
answers
10k
views
Automatically mapping output parameters with Dapper
I've been using Dapper to call stored procedures passing it an object. For example:
If I have an object:
public int ID { get; set; }
public int Year { get; set; }
I can create this object and ...
- 1,584
10
votes
2
answers
540
views
StoredProcedureCall 1x Varchar output 1x Cursor output
My question is maybe a little bit confusing. I have the problem that I'm calling an procedure with StoredProcedureCall from Java e.g.:
StoredProcedureCall call = new StoredProcedureCall();
call....
- 101
10
votes
1
answer
2k
views
Doctrine Filter in WHERE clause instead LEFT JOIN
I have a multi tenant application and i'm using the Doctrine Filters to filter my SQL by client.
So, when i want a list of my client Projects i just need to do a "getAll" and the filter will ...
- 1,087
10
votes
1
answer
2k
views
Hibernate duplicate primary key on restart using GenerationType.TABLE
We're running into an issue where we have Event subclasses that use GenerationType.TABLE to generate the primary key, and when we restart the servers we are getting duplicate primary key errors.
We'...
- 14.8k
10
votes
2
answers
770
views
JPQL Constructor Expressions, how to eagerly fetch the main entity in 'select new'
The original query I have is somewhat complex, but what I'm trying to do is obtain the entity AlertCondition plus some additional fields.
+ " SELECT new org.rhq.core.domain.alert.composite....
- 111
9
votes
0
answers
2k
views
Typeorm leftJoinAndSelect with subQuery
I have quite an issue here. I have three tables:
@Entity()
Vessel {
@OneToMany()
workOrders: WorkOrder[];
}
@Entity()
WorkOrder {
@ManyToOne()
vessel: Vessel;
@OneToOne()
order: Order;
}
...
- 536
9
votes
0
answers
7k
views
How use JSON_ARRAYAGG in JSON_OBJECT without escaping content
.Hello,
I have a SQL query that returns a JSON array. I want to simplify it using the native JSON functions of mysql/mariadb.
The problem is that it escapes me the sub-array.I think the sub-request ...
- 99
9
votes
0
answers
443
views
How do I write a query which returns a nested list using the Beam library in Haskell?
I need help rewriting myQuery so that I can retrieve all the class types associated
with a purchased class pass. The query I desire would have the signature:
myQuery :: Text -> Pg [(...
- 7,140
9
votes
1
answer
830
views
Could not synchronize database state with session org.hibernate.exception.ConstraintViolationException because of Duplicate GUID
I use hibernate to insert data in a table using autogenerated GUID, but insertion fails sometimes with duplicate GUID exception.
For Example:
From Logs , insertion fails for the first 2 attempts by ...
- 493
9
votes
1
answer
8k
views
Why does SQLite create empty sqlite_sequence table?
I have a database of several tables in which the first field is defined as:
'ID' INTEGER NOT NULL UNIQUE ... PRIMARY_KEY('ID')
I do not use the AUTOINCREMENT keyword for any of these tables yet my db ...
- 91
9
votes
0
answers
3k
views
Setting product visibility via SQL (WordPress + WooCommerce)
The following problem can be considered as a special case of adding categories to posts by SQL query [1], but answers relating to that general case are highly welcome too:
Let’s say we have 10.000 ...
9
votes
2
answers
3k
views
QueryDsl: Exception "argument type mismatch" with projection bean and oneToMany or manyToMany association
I have an association manyToMany between User and Role entities (User >---< Role)
I wanted to perform this query:
createQuery()
.from(qUser)
.leftJoin(qUser.roles, qRole)
....
- 412
9
votes
5
answers
7k
views
Sql LIKE in Arabic?
Consider this sample:
CREATE TABLE #tempTable
(name nvarchar(MAX))
INSERT INTO #tempTable VALUES (N'إِبْرَاهِيمُ'), (N'إبراهيم')
SELECT * FROM #tempTable WHERE name = N'إبراهيم'
SELECT * FROM #...
- 758
9
votes
1
answer
7k
views
OData in WebAPI without something like Entity Framework
I'm building a WebAPI that is getting its data from legacy systems so no Entity Framework available for me. Now I want to use OData functionality but is not going to work if I have somethink like ...
9
votes
2
answers
1k
views
How do I pass a table-valued parameter to SQL Server 2008 via EntLib 5.0?
How do I pass a table-valued parameter to SQL Server 2008 via EntLib 5.0?
- 7,304
9
votes
3
answers
3k
views
Problem with Insert query to Paradox table using C#
I have Paradox 5.x tables I need to connect to in order to select and update.
I am using OLEDBConnection.
selecting from the tables I have no problem.
while trying to insert into the tables I met a ...
- 651
8
votes
2
answers
235
views
Average of top 5 value in a model
I've got a django model with a lot of fields. I'm trying in a single query to get the avg value of a given field and the average value of the top 5 values of that same field (from my other question ...
- 2,426
8
votes
1
answer
194
views
Debugging performance differences (and issues) between a query with a double-subquery, a single-subquery and an all inner-join statements
I have a complex business logic that requires me to perform a 2-levels nested query. The queries are generated by Django's ORM. At the bottom of the question I'll provide the queries as-is as well as ...
- 14.8k
8
votes
1
answer
511
views
AlwaysOn Availability Group Listener access
This question is about the correct way of accessing an Availability Group Listener?
Assume I have two AlwaysOn Availability Groups with the following AG's: AG1 and AG2.
I also have 2 listeners called ...
- 3,266
8
votes
3
answers
9k
views
Postgresql transpose rows to columns
I have this query that yields a total of 1 million rows similar to the example extract shown bellow
SELECT *
FROM sales
shop
date
hour
row_no
amount
shop_1
2012-08-14
00:08:00
P01
10
shop_2
2012-...
- 81
8
votes
1
answer
2k
views
Entity Framework not respecting Command Timeout
I ran into an issue with my entity framework based api where 3rd party developers were mistakenly sending queries that were way too large and caused the system to drop performance. I've informed them ...
- 486
8
votes
1
answer
789
views
In sqoop export, Avro table to define schema in RDBMS
I'm loading data from HDFS to mySQL using SQOOP, in this data one record has got more than 70 fields, making it difficult to define the schema while creating the table in RDBMS.
Is there a way to use ...
- 1,569
8
votes
1
answer
2k
views
Can I exclude certain tables and views from my database project in visual studio 2012 by wildcard?
I would like to be able to do schema compare between my Visual Studio 2012 Database Project and a Development or Production database, however there are a number of tables which are dynamically created ...
- 146
8
votes
1
answer
2k
views
Entity Framework 5 Migrations creating procedures/functions
I'm creating SQL procedures in Entity Framework by using Sql methods in the migration. For example in an Up() I'm doing
Sql(@"SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo....
- 252
8
votes
1
answer
8k
views
Statement contains an OUTPUT clause without INTO clause error
I have a trigger that updates one of the inserted fields (RootId) with the value of the identity primary key (MessageId) of the same inserted record. The update should only happen when the RootId ...
- 4,652
8
votes
5
answers
8k
views
How to use left join like right join in Doctrine
In Doctrine there is no right join. You can use a left join like a right join but I can't figure it out for my example.
I have an entity in Doctrine which has a one-to-one relationship with itself ...
- 89
8
votes
2
answers
501
views
Rows Rotation For Mysql Table Consumer Process in Round Robin
We have automation to login into different websites and perform some operations on website accounts. It is required that only one BOT can login simultaneously into a specific account to avoid ...
- 652
8
votes
1
answer
3k
views
Is it possible to disable delete all query in Postgres?
Is it possible to allow only those DELETE queries that have a WHERE clause in them?
I've tried to use RULE for that, but I can't figure out what to put into it's WHERE clause, right now this RULE ...
- 12.2k
8
votes
3
answers
12k
views
SQL Error "uncommittable transaction is detected at the end of batch" without transactions on db
I'm facing the following problem at different points of my code.
An SqlException is returned from DB (SQL Server) with message "Uncommitable Transaction is detected at the end of the batch. ...
- 89
8
votes
1
answer
6k
views
SQL Server 2012 Read Only Connection
How can I setup a read only connection to sql server 2012 without the need to create a read only user?
- 7,157
8
votes
3
answers
4k
views
Convert an array into a Map
I have a table with a column like
[{"key":"e","value":["253","203","204"]},{"key":"st","value":["mi"]},{"key":"k2","value":["1","2"]}]
Which is of the format array<struct<key:string,value:...
- 421