2,736 questions
Advice
0
votes
3
replies
70
views
Procedure to show percentage of nulls in TERADATA SQL ASSISTANT
I'm working in Teradata SQL Assistant and I need to create a stored procedure that, given a table name, returns the percentage of NULL values for each column in that table.
Right now, I generate the ...
- 67
1
vote
1
answer
86
views
Is COL_LENGTH sufficient for parameter sanitation
I have a stored procedure that we are scanning using some AI tools to look for vulnerabilities. I am doing a dynamic SQL statement with an order by clause and that parameter for that order by I am ...
- 263
3
votes
1
answer
71
views
"invalid transaction termination" when executing dynamic sql
I am trying to do some transaction management in calls invoked by dynamic sql (EXECUTE).
The following works:
CREATE or replace PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
DECLARE r RECORD;
...
- 373
1
vote
1
answer
91
views
How do I make this query as a dynamic stored procedure?
I need this working query as a stored procedure where I can give two databases (one masterdb one testdb), start chainage, end chainage and UniqueRun as input
DECLARE @StartChainage FLOAT = 17.00;
...
- 31
0
votes
1
answer
98
views
Replace a character with an empty string inside a dynamic SQL using OPENQUERY
I need to replace a certain character inside a field with an empty string.
But this simple need becomes complicated because I need to extract data from a remote Oracle server and I need a parameter to ...
- 195
0
votes
1
answer
93
views
How can I use data from 1 table and then INSERT into a second using data as a column header SQL?
I am trying to build queries to take data from table_1:
id
path
curr_value
1
name
Company ABC
2
address
123 Main St
3
name
Company 123
And use it as the value AND column header for table_2 in SQL.
...
- 85
-2
votes
1
answer
122
views
Using sp_executesql drop multiple tables
I’m trying to write a script to purge all the tables (and views and functions and procedures) from the current dbo schema in the current database.
Bolting together what I have learned from other ...
- 17k
0
votes
1
answer
176
views
Possible ways of updating the catalog of an already created Linked server
Reason for change
I have a main Staging server with multiple linked server's setup.
Each one of these Linked Servers have their own DB on the main Staging Server.
I have an SP that extracts data from ...
- 3
2
votes
2
answers
116
views
How to dynamically build a query based on variable number of fields, in Java with PreparedStatement?
I have a UserDAO class where I need to retrieve users based on different search criteria.
Initially, I overloaded the method like this:
public User getUserByUsername(String username) throws ...
- 21
0
votes
1
answer
55
views
Dropping all Views dynamically in Db2
I want to include a drop everything section in a script which (re)generates a sample database.
I know I can find a list of views in the current schema and generate the DROP VIEW statements this way:
...
- 17k
0
votes
0
answers
93
views
How to create multiple files with different names and content using a comma-separated list without loops?
I have a comma-separated list of file names, and I want to create multiple text files in MySQL. Each file should contain some static text.
For example, if my input is:
'file1,file2,file3,file4'
I ...
- 85
0
votes
1
answer
86
views
Providing string variable into Dynamic SQL SELECT statement WITHOUT it being read as a column name?
I have a situation where I am trying to create a query using Dynamic SQL in SSMS where I am trying to generate a complete file path by concatenating both the name of a parent folder(s) (which change ...
- 135
-2
votes
2
answers
62
views
Dynamic sql to retrieve volume count throwing incorrect syntax error [closed]
I am getting incorrect syntax error at 'and Type=0 in the below dynamic sql. What I am trying to achieve is get the volume count of import activities between certain dates where the import id does not ...
- 45
1
vote
4
answers
205
views
Switch ASC / DESC in ORDER BY with a CASE construct?
In PostgreSQL, I am trying to have SQL which orders by multiple columns. The first column will always be priority DESC NULLS LAST. The second one needs to be dynamic based on some conditions (for ease ...
2
votes
1
answer
105
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 ...
- 419
0
votes
2
answers
59
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
110
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
...
- 1,041
0
votes
1
answer
84
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
106
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,'...
- 137
0
votes
2
answers
153
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
93
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
109
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
73
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
94
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
1
answer
84
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
683
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,664
1
vote
1
answer
152
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
84
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
1
answer
359
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
79
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
71
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,064
0
votes
1
answer
123
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
1
vote
1
answer
276
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 = :...
- 317
0
votes
2
answers
331
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
-1
votes
1
answer
103
views
How to detect if a Java class contains a foreign key field when generating a MySQL CREATE TABLE query? [closed]
User.java
// User.java
public class User {
private int userId;
private String userName;
private String userEmail;
private String userPassword;
// getters and setters
}
Post.java
//...
- 59
0
votes
0
answers
90
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
109
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,550
0
votes
1
answer
92
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,550
4
votes
3
answers
427
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,502
0
votes
1
answer
35
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
620
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
151
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
48
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
598
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 ...
- 337
1
vote
1
answer
99
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
243
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,232
1
vote
1
answer
360
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
387
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,533
0
votes
0
answers
51
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'. ...
- 1,018
1
vote
1
answer
123
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 info in ...
- 25