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
Filter by
Sorted by
Tagged with
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 ...
BendEg's user avatar
  • 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 ...
piezol's user avatar
  • 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_....
Kaypro II's user avatar
  • 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, ...
LMS's user avatar
  • 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 ...
smoove's user avatar
  • 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 ...
rlovtang's user avatar
  • 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> ...
Joha's user avatar
  • 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, ...
Jesse's user avatar
  • 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 ...
xavier.seignard's user avatar
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 ...
Chair's user avatar
  • 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-...
Ákos Vandra-Meyer's user avatar
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? ...
Leo Jiang's user avatar
  • 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="...
Loren's user avatar
  • 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 ...
duvo's user avatar
  • 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 ...
virtualadrian's user avatar
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 ...
Hari Prasandh's user avatar
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 ...
JSP's user avatar
  • 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 ...
Mohsin Raza's user avatar
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 ...
Dom Vito's user avatar
  • 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 ...
Alex's user avatar
  • 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....
Xanne's user avatar
  • 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 ...
costa's user avatar
  • 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'...
Dougnukem's user avatar
  • 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....
user2687644's user avatar
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; } ...
Chris Eikrem's user avatar
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 ...
NewbieDev's user avatar
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 [(...
therewillbecode's user avatar
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 ...
Vijay Manohar's user avatar
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 ...
MartinL's user avatar
  • 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 ...
Roger Grading's user avatar
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) ....
Thibaud Sowa's user avatar
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 #...
FindOut_Quran's user avatar
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 ...
Maurice van Lieshout's user avatar
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?
birdus's user avatar
  • 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 ...
Bjorkson's user avatar
  • 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 ...
BlueMagma's user avatar
  • 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 ...
alexandernst's user avatar
  • 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 ...
zurebe-pieter's user avatar
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-...
MG N's user avatar
  • 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 ...
jacobvoller.com's user avatar
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 ...
Rohit Nimmala's user avatar
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 ...
Scott Gall's user avatar
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....
DavidJones's user avatar
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 ...
Miro J.'s user avatar
  • 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 ...
Alex Mamut's user avatar
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 ...
Hassan Akram's user avatar
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 ...
Vsevolod Goloviznin's user avatar
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. ...
Gabriele.Pe's user avatar
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?
GordyII's user avatar
  • 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:...
John Constantine's user avatar

1
2 3 4 5
3103