A function defined in a relational database system. Most RDBS's include numerous built-in functions and also allow for the creation of additional custom functions.
Questions tagged [sql-function]
908 questions
2
votes
0 answers
WITH FUNCTION inside PL/SQL block
In Oracle 12c WITH FUNCTION was introduced. Simply:
WITH FUNCTION f_double(i IN INT) RETURN INT
AS
BEGIN
RETURN i*2;
END;
SELECT f_double(t.c)
FROM(SELECT 10 AS c FROM dual UNION SELECT 20 FROM dual) t;
-- output:
20
40
Now if I try to use it…
Lukasz Szozda
- 162,964
- 23
- 234
- 275
2
votes
1 answer
What does @@FETCH_STATUS <>-1 AND @@FETCH_STATUS <>-2 mean?
I came across an examination question, which had the following code in it:
ALTER FUNCTION fncF2 (@HWStreamIndex int = 0)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @OnSiteGroups AS nvarchar(150)
DECLARE @OnSiteGroupDetails AS nvarchar(1000)
…
Burhan Khalid Butt
- 275
- 1
- 7
- 20
2
votes
2 answers
SQL Server : function return column values that can be used in where clause?
I want to fetch record from table with WHERE IN clause like this:
Select *
From Table_A
Where table_id IN (Query X that returns multiple or single id)
Now I want to move X query to a SQL Server function because it will use multiple places and want…
Umer Waheed
- 4,044
- 7
- 41
- 62
2
votes
2 answers
Write a procedure in MySQL to split a column into rows using a delimiter
Here is my table:
CREATE TABLE sometbl ( ID INT, NAME VARCHAR(50) );
INSERT INTO sometbl VALUES (1, 'Smith'), (2, 'Julio|Jones|Falcons'), (3,
'White|Snow'), (4, 'Paint|It|Red'), (5, 'Green|Lantern'), (6, 'Brown|bag');
Table…
Bhadresh Shiroya
- 261
- 3
- 13
2
votes
1 answer
Changing text to character varying not executing as intended
I'm learning PostgreSQL and I'm trying to create a function to ALTER the data types of a given table that has columns with character varying data type.
To achieve this, I've built a cursor that basically queries the information_schema.columns…
fndg87
- 331
- 4
- 13
2
votes
1 answer
Assigning values returned by function in Dynamic SQL
I am trying to generate a dynamic SQL to assign variables with values from a function. I am trying to generate SQL something like
Select @StartDate=@EID_Dept
from dbo.WorkHistory(@today,@EID )
The function returns a date (@EID_dept will be the…
Amaan Khan
- 89
- 2
- 10
2
votes
2 answers
SQL using inline XML variable in UPDATE always get null
I've tried to make this code work, but the result always NULL for output1, output2
I've tested the variable @xml after UPDATE, but @xml is NULL also. This make xml query in SET return NULL.
CREATE FUNCTION fnXml
(
@input1 DECIMAL(8, 2) ,
…
Hai Vu
- 35
- 6
2
votes
1 answer
Use sql function in cakePHP beforeSave
I have been having trouble trying to convert a column from 'varchar' to 'money'.
Apparently the default schema is wrong. When I try to save a field for PriceRule, this is the error I get:
SQL Error: Disallowed implicit conversion from data type…
Anthony
- 233
- 4
- 14
2
votes
2 answers
How to convert YES/NO to BIT automatically in SQL Server?
I want to override system defined conversion in SQL Server. is that possible.
Problem Statement
I have a large csv file to upload data in database. There is a column with BIT type contains True/False, Yes/No, 1/0
When I used bulk insertion…
Jitendra Tiwari
- 1,651
- 3
- 14
- 28
2
votes
1 answer
MySql Regular Expression Select Columns Matching (dynamic)multiple Values within Stored Procedure
I am trying to generate a query where I want to select columns(text) matching multiple values.
eg: I have two columns, id and description. suppose my first row contains description column with value
Google is website and an awesome
search…
Sandy
- 862
- 7
- 12
2
votes
1 answer
SQL Server: How to execute UPDATE from within recursive function?
I have a recursive scalar function that needs to update a record in another table based on the value it is returning, however UPDATE statements are not allowed in the function.
How can I update the table from within the function?
Jason Coon
- 17,601
- 10
- 42
- 50
2
votes
1 answer
Registering a SQL function with JPA/Hibernate without Dialect Class
I'm having a rought time trying to calculate duration between two dates in Hql, so I've read in a post that I have to register the hql funtion using a custom dialect,
but the probleme is I'm working on an open source project and I don't want to be…
benaich
- 912
- 11
- 29
2
votes
1 answer
Replace string in Apache Derby
Does Apache Derby have a way of replacing a string with another string?
I'm looking for something like:
select
replace('bruce bogtrotter', 'bruce', 'john')
from SYSIBM.SYSDUMMY1
Fidel
- 7,027
- 11
- 57
- 81
2
votes
2 answers
Function return table variable
I'm trying to create a function that return a table variable.So firstly i get data from Table1 and put it in another table variable. Here i want check if this variable isempty the function return the parameter result else return the result of the…
stoner
- 417
- 2
- 12
- 22
2
votes
1 answer
Postgresql function, result set into variable
I have procedure:
CREATE OR REPLACE FUNCTION func()
RETURNS SETOF bigint AS
$BODY$
DECLARE
rowsQuantity bigint;
BEGIN
return query select p.id from product p where...;
GET DIAGNOSTICS rowsQuantity = ROW_COUNT;
if(rowsQuantity < 8)…
user2870934
- 679
- 1
- 7
- 22