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,149
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
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
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....
- 417
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:
...
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 &...
-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
-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 ...
- 25
-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
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
-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
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.
-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
...
- 315
-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 ...
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 ...
- 2,959
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
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(){
...
- 331
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), ...
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....
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
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 ...
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 ...
- 1,482
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 ...
- 83.6k
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&...
- 3,334
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
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 ...
-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;
...
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 ...
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 ...
- 1
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 ...
- 1
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
-1
votes
0
answers
19
views
How to connect DB to botpress [closed]
I'm making botpress that need to be connected to my sql data base. i want to connect it in a way that i could use it as a file (that the botpress AI could answer questions or even edit the data base)
...
-1
votes
0
answers
27
views
Confusion about errors when trying to create object views in PL/SQL. Encountering the following error: ORA-00932 [closed]
I'm currently trying to learn PL/SQL and in my practice I've encountered some issues I don't understand relating to the Oracle documentation.
I'm a novice at PLSQL but I hope you can help me further ...
- 1
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 ...
- 11
-1
votes
1
answer
41
views
best way to create a "String" sql query with multiple parameter values at runtime?
I need to create a SQL query which I further need to pass it as a request body in REST API. I am struggling to find a best way to create this sql as a string
What i have so far is via the ...
- 1,522
-4
votes
0
answers
19
views
Why the float in mySQL homework shows empty set? [closed]
I have a table here and I need to select all the values with the height of 1.65
Here's the result, why it is showing empty set even if there's 1.65 in that table, I used float as its datatype
I hope ...
0
votes
0
answers
28
views
"invalid input syntax for type double precision" error on PGADMIN
postgres error encountered excel column data
anytime i try to import the csv file on postgres I get the following error "invalid input syntax for type double precision" error, I have tried ...
-1
votes
0
answers
34
views
SQL Server Replication(Log Reader Agent error) [closed]
I tried setting up a transactional replication in SQL Server but I keep getting an error from the log reader agent with the below error message
Procedure or function sp_get_redirected_publisher has ...
-4
votes
0
answers
41
views
I am trying to create a table to build a datawarehouse but I had some problem that i couldn't solve it [closed]
This is the problem enter image description here
When I imported the source file into the table that I had made it caused a problem a CustomerKey. I couldn't set the type INT for it because the source ...
0
votes
0
answers
25
views
Optimizing SQL Query Performance for Hierarchical Data Retrieval
I'm working on a project where I need to retrieve hierarchical data from a table stored in a SQL database. The table structure follows the adjacency list model, with each row containing a parent ID ...
0
votes
1
answer
31
views
Error: maximum number of expressions in a list is 1000, how can I modify the code below to take more than 1000 records?
I have the code below and I know I could be probably close, but I still get the error that I have exceded the 1000.
I added the generate_sql_in_binds function but I guess I dont know how to pass it ...
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
25
views
how to query against a json column with key value pairs in mariadb
goal:
i need to create a query that finds the records that meet a given condition.
the condition is stored as JSON in sys_conditions column.
this is how the data is stored:
SELECT sys_conditions FROM ...
- 80
0
votes
3
answers
46
views
SQL query to sum some values from preceeding rows depending on the row values
I managed to get the following table:
from
to
value
id
start_row
end_row
A
B
20
1
NULL
1
C
D
30
2
NULL
2
B
C
100
3
NULL
3
E
A
50
4
NULL
4
A
D
70
5
1
5
C
A
20
6
2
6
For the rows having both ...
0
votes
1
answer
25
views
How can I populate a column with same values which is there in that column
I have the following table:
The flat_id column will only contain 2 distinct values i.e. 'ABC123' and 'NA'. I need to create a new column new_flat_id which will contain the 'ABC123' values in all rows ...
- 51
0
votes
0
answers
35
views
I would like to build a hierarchy view that splits out data across multiple columns
I have 40k rows of data which is a combination of codes and parents codes. There are two columns, codes and parent code, so for example
Code
Parent code
A
2
B
1
1
3
2
B
3
What I want to extract ...
- 11
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
-4
votes
1
answer
40
views
In PostgreSQL, how can I select all the contents in two tables without explicitly listing all the fields? [closed]
Since there are many fields in both tables A and B, when joining the two tables for query, if all the fields are explicitly listed, the workload will be huge and the code will be very long. How can I ...
- 185