2,728 questions
2
votes
1
answer
54
views
Oracle DBMS_SQL giving ORA-1007 variable not in select list
I get this on Oracle 11g and 19c.
Below is a simplified example of the code I am using to process some queries dynamically. It needs to be DBMS_SQL because the actual use case will have various ...
- 399
0
votes
0
answers
13
views
Dynamic SQL BCP on local desktop writes no file and generates no error
I have a script task in SSIS that executes Dynamic SQL and builds a BCP statement that is executed in the typical manor. But when editing in SSMS as a SQL script, I can execute it within SSMS and it ...
- 315
0
votes
2
answers
42
views
How to return columns from dynamic SQL in Python console
I'm trying to make a CRUD application that uses Oracle procedures for the operations.
For the READ component, I wrote a procedure that returns one or more entire rows depending on the specified ...
1
vote
2
answers
100
views
Select one value column from many in a pivot query
I have a table in SQL Server that has rows as follows:
ID (int)
Name (varchar)
IsEnabled (int)
ObjectName (varchar)
PropertyName (varchar)
PropertyValueString (varchar)
PropertyValueInt
1
Rule01
1
...
- 707
0
votes
1
answer
54
views
Dynamic Oracle Query, without dynamic
The title is indeed strange, but I will try to explain.
I have been programming Oracle (PL-SQL) in version 11G for several years.
I recently moved to a new workplace, and in the first task I was ...
- 39
1
vote
1
answer
89
views
How to declare variables and use them to UPDATE each row based on the preceding row(s)
I'm looping through table1:
create table table1(id,"date",quantity,"value")as values
(1,'2024-10-01',1,1)
,(2,'2024-10-02',1,1)
,(3,'2024-10-03',1,1)
,(4,'2024-10-04',1,1)
,(5,'...
- 131
0
votes
2
answers
108
views
Execute dynamic inserts in a procedure
I'm new to Postgres and trying to create code that inserts data from several tables into a single table.
All source tables have a name starting with "B3_HIST_", differing only in terms of ...
0
votes
1
answer
76
views
How to query a dynamic list of strings using SQL?
I have a table Customers with multiple columns one of which is state.
I want a dynamic query : When my stateInput is empty I want all customers, if not I want customers that have the state given in my ...
- 43
1
vote
1
answer
57
views
PostgreSQL Dynamic Query Execution with Table-Valued Variables
I am working with code inside of a PostgreSQL pgsql function. I have a merge statement that has multiple elements that are only known at runtime and are supplied to the function as parameters, which ...
- 419
0
votes
0
answers
65
views
Group_Concat() when is null, I get error 1064
I have the following query in MySQL 8.0:
set
@userid = 'XXX';
set
@lastdate = '2024-09-30';
SET
@sql = null;
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
'Count(CASE WHEN Checkedby = '''...
- 1
1
vote
1
answer
66
views
Oracle PL/SQL: generic and uniform journalling trigger without hardcoding columns in the trigger
In my Oracle 19c-database, I would like to make 1 trigger per table where the jornalling is handled for insert, update and delete. I would like that every trigger is the same for each table, with the ...
- 11
0
votes
0
answers
90
views
I am trying to use dynamic SQL query to generate dynamic columns but I received an error in MySQL
I am trying to generate a dynamic column based on the existing projects in my projects table, so I want to collect the user time he spends on each project based on another table that tracks records ...
0
votes
1
answer
77
views
How to assign a column to a variable in Dynamic SQL
I created the following dynamic SQL:
DECLARE @DATE nvarchar(4) = '0824';
DECLARE @LocalID INT;
SET @sql = N'SELECT Col1, Col2, Col3, Col4, ';
SET @sql = @sql + N'@LocalID = Col5 FROM Table WHERE ...
- 1
2
votes
2
answers
176
views
DuckDB: how do I create a table containing a column with a specific name from a `CREATE MACRO ... AS TABLE ...`?
Suppose I have the following macro:
CREATE OR REPLACE MACRO hello_world(col_name, series_start, series_end) AS TABLE (
SELECT generate_series::VARCHAR AS col_name
FROM generate_series(...
- 4,544
1
vote
1
answer
88
views
Dynamic SQL created with array of values
I have a list of column names, for example ('Name', 'Code', 'Title') that can be array but not necessarily, it can be anything. I just have a list of column names.
Then I have to create a dynamic SQL ...
- 367
0
votes
1
answer
64
views
Creating the same view in multiple tenant databases using a stored procedure
I try to create/alter the same view in a list of databases. Let's call them A, B, C.
The best would be to create/alter them all at once so I'm looking into a stored procedure to create the view:
...
- 302
0
votes
0
answers
180
views
dynamic union with trino
lets say i have an iceberg catalog in trino with a schema a and 3 tables in this schema.
i know that i can use this query to retrieve the snapshot information of test_table0
SELECT * FROM "...
- 1,283
0
votes
1
answer
90
views
How to call a variable inside dynamic SQL Query in Snowflake stored procedure
Need help to call variable inside snowflake stored procedure. It throws a "Missing column specification" - how to access variable allcols inside dynamic SQL in snowflake?
CREATE OR REPLACE ...
- 43
0
votes
1
answer
54
views
SQL While Loop using Dynamic SQL
I am attempting to use a WHILE loop with dynamic SQL. See code below.
I have a table [Users] that set out various conditions required to update fields in [DB1] with a Status Code. The code runs, ...
- 65
1
vote
1
answer
48
views
How to create a view of a query generated by another dynamic (meta) query
How do I turn the query below into a temporary view? Everything I've tried returns the text of the generated query (second quoted code block). "Everything" includes...
Wrapping the 1st ...
- 3,024
0
votes
1
answer
101
views
Dynamically named variables from query
I want to write a SQL Server stored procedure that contains the following:
Imagine a query like this:
SELECT VARNAME, TYPE, VARVALUE, LEN(CAST(VARVALUE AS VARCHAR(5))) + 1 AS LENGTH
FROM SOMETABLE;
...
- 155
0
votes
1
answer
145
views
Dynamic SQL vs. generic query with dynamic filters
I'm working on a SQL query where the filters can be dynamic, and I'm concerned about its efficiency. My current approach is as follows:
SELECT *
FROM table t
WHERE (:param1 IS NULL OR t.example = :...
- 299
0
votes
2
answers
176
views
Loop through all stages in schema within a stored procedure and pass them on as arguments
I am trying to tweak my stored procedure in Snowflake so that I can loop through all external stages (in S3) from my schema manual_adjustments and not have to call this other procedure CHECK_LOAD() ...
- 309
0
votes
0
answers
97
views
Snowflake - dynamic "copy into" across different stages
I am trying to create a procedure in Snowflake that will dynamically copy files from different external stages in S3 into a table. Please see the procedure below:
CREATE OR REPLACE PROCEDURE ...
- 309
-1
votes
1
answer
76
views
How do I identify if a class contains a foreign key field? [closed]
I'm trying to create a method in Java to create a table in a MySQL database. I have two classes:
User.java
public class User {
private int userId;
private String userName;
private String ...
- 41
0
votes
0
answers
77
views
SQL injection and resulting query of sp_executesql
I've developed a stored procedure that utilizes dynamic SQL to search for specific entities (this is a procedure for catch all logic). Is this procedure prone to SQL injection, and how do I get the ...
- 127
0
votes
1
answer
76
views
sp_executesql In Loop Taking FOREVER to Run. Slowing down as it runs. Memory Leak?
Long and short is that my query is taking FOREVER to run and I think it has to do with the sp_executesql queries. Yes, there are loops, and yes I am using Dynamic SQL.
I use loops enough to know ...
- 1,520
0
votes
1
answer
60
views
sp_executesql Not Returning Correct Value Using OUTPUT
The code with sp_executesql is in a loop (two actually), that goes through the list of IDs in the [tmp_compare], and compares the data in each of the columns in the [contact_compare_fields] from two ...
- 1,520
4
votes
3
answers
248
views
Macros / Meta-programming in Postgres queries
In the case that I have the same example data as in this question and additionally declare the following two functions:
CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_price(...
- 1,492
0
votes
1
answer
25
views
Dynamic copy function
I have a function with a dynamic copy statement and when I try to SELECT function, I get the error "query has no destination for result data"
select loadTodaysData();
CREATE FUNCTION ...
-1
votes
1
answer
283
views
Need variables in parameterized view in clikhouse
I need variable in parametrized view.
CREATE VIEW my_new_view AS
SELECT
CampaignName,
sentiment, -- **CASE WHEN variable** = sentiment THEN sentiment ELSE NULL END AS sentiment_match
COUNT(CASE ...
0
votes
1
answer
94
views
Dynamic order by in clickhouse row_number()
I have (24.1.4.20) view in clickhouse and want change order by columns inside over() of row_number().
create or replace view data.v1 as
select row_number() over(order by COLUMNS({p_order_by:String})) ...
0
votes
0
answers
44
views
SQL Server variable creation [duplicate]
I have the following query in SQL Server, I am trying to create variables dynamically
DECLARE @TESTEO NVARCHAR(max)
SET @TESTEO = '
DECLARE @SEG1HEM NVARCHAR(max)
SET @SEG1HEM = (SELECT ...
-1
votes
3
answers
332
views
Execute multiple DDL statements dynamically with a single script?
Note: My intention with this question was to learn about approaches other than mine, to execute multiple CREATE TRIGGER statements in a single string. Not "solving" this, by executing one by ...
- 327
1
vote
1
answer
74
views
How to club multiple select queries from one single table
I would like to query a table which has few columns (such as 4 below) from where i would like to create a snapshot table which can provide insight on the attribute count everyday
userId
attr1
attr2
...
- 47
-1
votes
1
answer
121
views
SQL Multiple Value Dynamic pivot
Below I have my current code.
What I am trying to do in this code:
The #BySite table has a list of records where all the columns listed before the PlayMonth column are a snap shot at the end of every ...
- 1,216
1
vote
1
answer
186
views
How to solve ERROR: operator is not unique: "unknown" - "unknown" in a dynamic SQL query?
I have a execute block inside a stored procedure and I am executing a query dynamically:
execute 'create table raw_mine.financial_multicase_xwalk_' || target_date || '
as
select distinct
a....
0
votes
1
answer
142
views
jOOQ - Building a dynamic `CASE WHEN` with DSL
I'm working with jOOQ and want to construct a dynamic CASE WHEN expression using the DSL. The desired outcome is a structure like this:
var myCase = DSL.case_()
.when(condition1, result1)
.when(...
- 6,423
0
votes
0
answers
37
views
How to process cedilla delimiter in redshift copy,unload commands inside a stored procedure(Copy delimiter single byte character)?
My query is like this:
unload('select * from table') to 's3://path'
credentials '*******'
header
parallel off
delimiter as '\307'
The delimiter is Cedilla Ç. and I have to unload it like this '\307'. ...
- 996
1
vote
1
answer
94
views
Create columns from distinct values of a column
I have a large dataset with many different product. In a small scale it would look like this:
product
month
amount
AA
1
100
AA
1
150
AA
2
200
AA
2
120
BB
2
180
BB
2
220
CC
3
80
I want get the ...
- 25
-1
votes
2
answers
55
views
Passing table name dynamically each time
In the below Query j.table_name is Parent table and it contains some dependent table
1 STORE_ORDER_JSON_DATA
2 ECOMM_SHIPDOCS_MQ_DATA
i am passing the parent table name dynamically and dependent ...
- 805
0
votes
1
answer
216
views
Postgresql equivalent of SQL Server query
I am migrating database from SQL Server to Postgresql and I am currently stuck at converting this query. I have referred to the documentation but I don't seem to find any solution.
Here is the query (...
1
vote
1
answer
904
views
Snowflake Dynamic SQL to select subset of columns in one table from undetermined number of values in another table
I need to create a SQL script that will run daily and select a subset of columns from table A.
The subset of columns is defined in table B - it will be periodically edited by marketing team and can ...
- 13
0
votes
1
answer
42
views
Replace tags in a string by reading dynamic columns values from a table
Using TSQL, I want to replace tags in a string by reading values from a column in a table.
E.g. I want to call:
DECLARE @TaskID nvarchar(10) = 'T001';
DECLARE @MyString nvarchar(100) = 'The person ...
- 2,822
2
votes
1
answer
154
views
How to run the prepared statement with arguments dynamically in a function?
I created person table, then inserted 2 rows into it as shown below:
CREATE TABLE person (
id INT,
name VARCHAR(20),
age INT
);
INSERT INTO person (id, name, age)
VALUES (1, 'John', 27), (2, '...
0
votes
1
answer
603
views
How to select date columns from every table in the database?
I'm trying to select the minimum date from every date column inside my database. What I mean with minimum is that I want to obtain the earliest date.
From what I've been investigating, it seems that ...
- 15
0
votes
1
answer
93
views
dynamic sql to rename columns, which contain string in name
I am trying to rename all columns in a table, which contain the string survey_ by simply removing this string from the column name. I wish to do this with dynamic SQL. I've formulated the following ...
- 468
0
votes
0
answers
50
views
Error with Dynamic SQL Data Transfer: Operand data type nvarchar is invalid for subtract operator
I need to transfer data between tables in a database. The issue is that the old table uses a 6-character format (YYMMDD) for dates in the "RecordDate" column, whereas the new target table ...
-1
votes
1
answer
212
views
Using SQL Variables as Column Headers with dynamic SQL
I have a pretty long stored procedure (in SQL Server) where a business user asked if it would be possible to rename columns with values that are being stored in declared variables.
Here are variables ...
- 21
0
votes
1
answer
106
views
Cannot create a row of size 11616 which is greater than the allowable maximum row size of 8060. When using dynamic SQL to create dynamic columns
-- Generate dynamic column list
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Attribute)
FROM AttributeValue
WHERE ProjectID = @ProjectId
...
- 13