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.
671,302
questions
0
votes
0
answers
2
views
I have a table with transaction details, I want to apply subtract the amount of each payment to invoices on FIFO basis. ny help is greatly appreciated
Posting Date
Transaction Type
Document No
Value
Remaining Amount
13-06-23
Invoice
2324PSI2802
176866.8
0
04-07-23
Invoice
2324PSI3778
79725
12297
19-07-23
Invoice
2324PSI4407
9835.8
9835.8
02-08-...
0
votes
0
answers
2
views
Using a merge function using scala via Databricks is creating additional columns in target table which is causing failure due to schema mismatch
so I have a problem with a task in work. My Manager has asked that I create scala code that will upsert data from source table to a target table as well as delete from target table if the data row ...
0
votes
0
answers
9
views
MsSQL stored procedure syntax error when using TestContainers in Spring Boot integration test
I'd really appreciate some help with MS-SQL server stored procedure syntax.
I am trying to integration test a Java Spring Boot application using MSSQL TestContainers with R2DBC connection: mcr....
- 126k
0
votes
0
answers
4
views
Can I Pivot in Snowflake without single quotes, without manually aliasing the column names?
My use case is quite specific, and I couldn't find a way of doing that as for now.
I am dynamically pivoting my tables using a stored procedure, because my pivoted outputs sometimes have hundreds of ...
- 3
0
votes
0
answers
6
views
H2 DB executes wrong query while configuring test db for spring boot test
I am writing Junit for one my applications in which I want to use H2 as a test db. So, I have created a test with using @AutoConfigureTestDatabase and used below application-test.yml values
spring:
...
-2
votes
1
answer
34
views
SQL join 3 databases [closed]
I am right joining a table "FinalData" at last which should bring all the values of the right joining table and the matching values of the inner join tables
but as the inner join values are ...
- 799
-1
votes
1
answer
15
views
filter on union entities with JPA specification
Considering this java JPA model:
@Entity
@Table(name = "PARENT")
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class ParentEntity { }
@Entity
@Table(...
- 1,080
0
votes
0
answers
11
views
SQL Stored Procedure adding values to table run successful, but no result show in the table
I have created a stored procedure in SQL Server to add new student record to Student table. I have grant permission to one of the roles I created that is "DBAdmins". I have also login as &...
-3
votes
0
answers
35
views
Split week number between two dates [closed]
I am using SQL Server 2019. I want to calculate the week number between two dates.
E.g.
StatDate = 2024-01-01
EndDate = 2024-01-31
Expected output:
StatDate
EndDate
WeekNo
2024-01-01
2024-01-07
1
...
- 92.4k
-1
votes
0
answers
15
views
Result of multiple querys based on other querys values in PHP
I have a php that receive from a form some values as variables, i make a query with this values and show them in a table. PHP code is:
$sql1="SELECT via,codvia,cp,municipio,provincia,( 6371000 * ...
- 7
0
votes
1
answer
34
views
How to get the first and last records from an unordered table in sql
Here from the below image I want to get only first and last records in SQL.
From the below table I want to get "White Clover Markets" and "Cardinal" as a result.
- 799
0
votes
5
answers
71
views
SQL query to count blank data
I need a SQL query to count blank data as shown below; I want to count number of null for each regions. The code I use is giving only the null but not the total count of null values for each region
...
-2
votes
0
answers
28
views
How can I solve sqlite3near "(": syntax error? [closed]
import base64
import sqlite3
def pic_bin(pic_file):
conex = sqlite3.connect('products.db')
cursor = conex.cursor()
code = "T0003"
pic_file= base64.b64encode(pic_file)
...
0
votes
3
answers
59
views
Get the distinct names an account has prior the last day in TERADATA or Oracle without using stored procedures, just queries
I need to calculate the distinct names an account had, where I have the date of each update the account had:
Date
ACCOUNT
NAME
2024-01-01
A
XOXO
2024-01-02
A
XOXO
2024-01-02
A
OXXO
2024-01-04
A
...
- 125
0
votes
0
answers
4
views
SQL/BQ Error: there is no matching signature
Im trying to query a table in BQ. I keep getting an error that "there is no matching signature = for argument type string." Its weird because the data formats are all strings
'''Select city
...
- 45
-1
votes
1
answer
19
views
DuckDB prepared statement list
I wanted a parameterized query using which I wanted to fetch persons dynamically:
import duckdb
query = "SELECT * FROM person WHERE id IN(?)"
duckdb.execute(query, [[4,5,6]])
This is ...
- 9,803
25
votes
14
answers
152k
views
ModuleNotFoundError: No module named 'pyodbc' when importing pyodbc into py script
I've written a short python script which tries to import the pyodbc extension package so I can access my SQL table.
import pyodbc as pyodbc
cnxn = pyodbc.connect('Driver={SQL Server};'
...
- 11
6
votes
5
answers
10k
views
storing binary data in mysql
I have a PDF file on a local machine. I want to upload this file into a BINARY BLOB on a SQL database. Other approaches mentioned here [Binary Data in MySQL all use PHP. I want a simple clean way to ...
- 14.8k
0
votes
2
answers
31
views
How to Query JSON Array of unnamed objects. JSON Path Expressions Syntax
I have rows containing Json like: [{"Qty": 1, "Amt": 2.99},{"Qty": 3, Amt": 4.50}]
How do I get a sum of Amt per row?
Reproduction Code:
DROP TABLE IF EXISTS dbo....
- 66.7k
0
votes
0
answers
29
views
deleting record and all records belong to hem
i design this database for a simple clinic application, a user can be doctor or not
The question is when I want to delete a user and the user is a doctor so I need to delete all appointments that ...
- 504
1
vote
5
answers
10k
views
Oracle - using connect by prior in Hierarchical Queries and put them into rows
I am new in Oracle, I am going to use connect by prior to implement the flat table instead of Hierarchical one. but I am a little bit confuse. My table is like this:
empTabl:
empID
empName
managerID
...
0
votes
1
answer
133
views
Login/signup script in python using sqlite3
I have created a login/signup script in python using sqlite3 and kivy. After creating the database, the user can enter their username, email and password. The password is hashed before being inserted ...
- 744k
1
vote
1
answer
16
views
How to create a dynamic SELECT clause with jOOQ
I want to create a select query using JOOQ. I have couple of fields which I want to include or exclude from the query based on a system property value.
In the below method
String getBankDetails(){
...
- 216k
0
votes
5
answers
33k
views
Insert radio button selected value into database
I am building a C# Windows application with 2 radio buttons for gender. I'm using a simple insert query to insert data in the database.
Can anyone help me with inserting the selected radio button ...
CommunityBot
- 1
0
votes
1
answer
25
views
Split String based on condition in Snowflake/SQL
In a column if there is a string in the column col1 like 'abc_def', I can split and take the part like this
split(col1, '_')[0]
In the same column if there is another value like 'abc_def_xyz', I need ...
0
votes
1
answer
14
views
Data entry for 2 related tables with form, automatically input foreign key into main table
Working in MS Access.
I am developing a customer loyalty database and I have 2 tables:
Customers
Names
Admission
Creation_Date
Expiry_Date
Contact_ID
John Smith
Adult
01/02/2024
01/02/2025
1
...
- 13
0
votes
0
answers
19
views
Disallow user from querying database structure
How can I use SQL Server 2019 Developer edition's permissions to deny the curerntly logged in user from selecting data from the sys.objects, sys.tables, sys.columns, etc. system views, as well as the ...
- 126
0
votes
0
answers
36
views
Same query, same data - but much longer execution time and more reads in another server
I have a query that includes an inner join between 2 tables.
When I use SET STATISTICS to get some information, I can see that the order of execution and reads is much different from one server to ...
- 744k
0
votes
1
answer
48
views
Comparing and updating changes - SQL
I have 2 tables: latest and history. History will have all the rows loaded before and latest will have latest data in it. I want to come up with a consolidated table based on load_date and the table ...
- 26.4k
0
votes
0
answers
13
views
How handle null in dynamic pivot query
I am writing a SQL query for create a sheet for attendance of employees, I am using pivot for that which looks like this
ALTER PROCEDURE [dbo].[demoforattendance]
(
@year varchar(max), ...
- 744k
0
votes
1
answer
26
views
Python merge/join/concat with boolean condition
I would translate this SQL code (see below) in python code. I think I can use pandas.merge/join but I don't know how define the boolean condition.
create table gg as
select b.*, a.value
from table b ...
- 16.6k
0
votes
0
answers
12
views
1 When I run my pgm class I get the following error:
Apr 22, 2024 8:45:54 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate ORM core version 5.6.5.Final
Exception in thread "main" org.hibernate.internal.util.config....
-2
votes
1
answer
51
views
Change the result of SQL query by PIVOT
I am going to change the model of result in SQL query.
Imagine the query below:
SELECT
es.LatinName,
es.CreatedOn
FROM Salex.ExportFile ef
INNER JOIN Salex.ExportFileStep efs ON efs....
- 614
0
votes
1
answer
27
views
String formatting with object parameters in intellij
I am creating a standard function that uses jpa's createQuery function, as it will be a generic class, I can format the sql elsewhere, but when using the same pattern and definition of parameters only ...
- 310
2
votes
1
answer
56
views
Is a HAVING condition with no aggregate always exactly equivalent to WHERE?
I recently ran across an interesting comment in the PostgreSQL test suite:
-- HAVING is effectively equivalent to WHERE in this case
SELECT b, c FROM test_having
GROUP BY b, c HAVING b = 3 ORDER ...
- 6,390
5
votes
6
answers
14k
views
SQL function to convert UK OS coordinates from easting/northing to longitude and latitude
Please can someone post a SQL function to convert easting/northing to longitude/latitude. I know it's incredibly complicated but I haven't found anyone who has documented it in T-SQL.
This ...
- 991
0
votes
2
answers
42
views
How to populate a table from a json array column in another table?
In my Postgres database I naively created a column in table Foos as a json[] that looks like this:
TABLE Foos
id | ... | baas
--------------------------------
1 | ... | [
| ... | {
| ... | ...
- 632k
1
vote
5
answers
39
views
Mapping NULL values via CASE statement in Oracle SQL
I am trying to map few values using CASE statement. Everything seems to work fine except when there is a NULL value in the ID column. It shows 'OTHER STATE' from else instead of 'NO STATE' from WHEN ...
- 1
0
votes
1
answer
36
views
Could we get the expected output with cross join similar to the SQL code
I am trying to write a similar code with cross join in R like that we do in proc SQL.
However, I am unable to write a code, please help me.
Here is the data:
data.frame(
all_names = c("PARENT1&...
- 260k
1
vote
1
answer
41
views
SQL : creating query to add reducing totals
I have following two tables.
Table #1
ID
Amount
1
5000
2
3000
3
4000
Table #2
PO_ID
PO
Sequence
PO_Amount
1
PO1
1
2000
2
PO2
2
4000
I want to join these 2 tables in such a way that PO amount ...
- 17.2k
0
votes
1
answer
39
views
I am using PostgreSQL. I am facing an error while inserting values in columns, everything is correct all column names, case sensitive, values
I am learning SQL, here while entering values into the customer table with five columns CustID, CustName, Age, City, Salary. I am entering everything correctly, and case sensitive, but I still get an ...
- 744k
0
votes
1
answer
44
views
CDS View SQL Aggregate breakdown
I wanted to ask how can i see which values have been aggregated during the aggregation when using for example a sum agg in SQL.
E.G
Product
Quan
A
5
A
10
Consider this i want to agg the Quan and ...
- 51
0
votes
0
answers
41
views
Complex SQL update in the stored procedure
I'm beginner in stored procedures for Postgres. i have one working procedure and I need to change it a little:
CREATE OR REPLACE FUNCTION krv.delete_krvs_sridsv2(_krvs jsonb)
RETURNS void
LANGUAGE ...
- 971
-2
votes
0
answers
17
views
How to get the resulted error from SQL server insert query to be displayed in a textbox using JSP
I am in the middle of a project , where I have to display the results of a set of insert command ran in SQL server to be displayed in a text box in UI with JSP.
I can able to initiate the SQL Insert ...
- 171
-2
votes
0
answers
21
views
Need help in postgreSQL version 13 for triggers [duplicate]
CREATE TRIGGER update_summaryofdetailed1
AFTER INSERT ON detailed7
FOR EACH ROW
BEGIN
UPDATE summaryofdetailed1
SET amount = amount + NEW.amount
WHERE country_id = NEW.country_id;
END;
...
- 48.3k
0
votes
1
answer
50
views
How do i solve 'Error Cannot add foreign key constraint'?
So I'm using sqlfiddle but I always get the error 'cannot add foreign key constraint'. Can somebody help me find the error? I'm sure the problem is on the first table (Professor), as if you delete ...
- 35
1
vote
2
answers
3k
views
how to use the index with springboot or JPA
Table A
ID
shortdescription
thumbnailimage
linkactions
Country_id
165
shortdescription
thumbnailimage
linkactions
E1796BA
Table B
ID
name
Country_id
1
India
E1796BA
Controller
public ...
- 178
0
votes
1
answer
35
views
VB.Net application connect to SQL Azure Instance with MFA
I have a VB.NET application that has 2 routines to open databases in SQL which all worked fine until we migrated databases to an SQL Azure Instance with MFA.
The first sequence works fine with MFA and ...
- 1
0
votes
1
answer
37
views
Json file from SQL Table using ADF is adding Escape characters
I ma trying to create json file from SQL table using ADF through Stored Procedure
Stored Procedue:
SELECT (
SELECT *
FROM [dbo].[TEST_TABLE]
FOR JSON PATH, INCLUDE_NULL_VALUES
) AS json_file;
I am ...
- 47
0
votes
0
answers
32
views
In MySQL deep page query, the select * and select [primary key] data are inconsistent. Procedure
Background introduction
MySQL table t_order, total record of 160w
select count(*) from t_order;
order_id is primary key, order_status is Secondary index
show index from t_order;
field order_status ...
- 1