New answers tagged

0

Closest thing that I know is to overload a function... create or replace package test_pkg as function variableParams ( a in varchar2, z in integer, y in integer ) return integer; function variableParams ( a in varchar2, z in integer, y in integer, x in integer ) return integer; end; / create or replace package body test_pkg as function ...


1

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements. There is no nice and easy way for this. There is the method with collections, for example: create or replace type t_numbers as table of number; / create or replace function sum_plsql (p_numbers t_numbers) return number as rv number := 0; begin ...


1

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why. Because you copy pasted from the website and the page doesn't show a hyphen/minus but a en-dash. You can just position your cursor behind it and delete it, then type minus on your keyboard and the code will work. If you want to modify the ...


2

The best way would probably be to create a CLR function and use .NET framework libraries. A TSQL attempt is below. DECLARE @Pattern NVARCHAR (50) = N'%[^' + /*List from http://stackoverflow.com/a/18169122/73226 */ NCHAR(0x0009) + /*tab*/ NCHAR(0x000A) + /*line feed*/ NCHAR(0x000B) + /*line tabulation*/ NCHAR(0x000C) + /*form feed*/ NCHAR(0x000D) + ...


3

My personal preference is to use COALESCE as it is ANSI standard. CREATE TABLE dbo.Contact ([ID] int, [FirstName] varchar(5), [LastName] varchar(6), [MiddleName] varchar(4)) ; INSERT INTO dbo.Contact ([ID], [FirstName], [LastName], [MiddleName]) VALUES (1, 'Mark', 'Jordan', 'E'), (2, 'James', 'Blunt', NULL) ; --- below both tsql will give ...


0

You can always use CASE to get that reverse logic, but it's easier than that in your case. --With names in separate columns SELECT FirstName, ISNULL((MiddleName + '.'), '') AS MiddleName, LastName FROM Contact --With names merged together, removing any leading/trailing spaces. SELECT LTRIM(RTRIM(ISNULL(FirstName, '') + ISNULL((MiddleName + '. '), ' ') + ...


5

It's great that a NULL plus a value produces a NULL*, means you can do stuff like this... SELECT FirstName + ' ' + ISNULL(MiddleName + '. ', '') + LastName FROM Contact; Hopefully, this works for what you want. * Assuming CONCAT_NULL_YIELDS_NULL is set ON, as it should be;


2

I believe I don't quite get what your question is about. The following function would insert those values, with the same values on fields like create_time, within a transaction, on multiple databases. Is this what you're looking for? delimiter // CREATE PROCEDURE yourdb.yourprocname (_first_name varchar(100), _last_name varchar(100), _create_time datetime, ...


4

You have just ended the single quote near 'cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT(' so that the compiler is expecting the ; to end the statement but it has found Hello_Context','COMP_ID'),0)');. If you want to use single quote inside another single quote you can use q operator as demonstrated below. For details:Literals ...


9

I was lying when I said, I'm not able to recode it into SQL. I was just too lazy. Here is the code with an example of usage. The Code is based on a TheiSen perl library, using QuickMedian. Let's define a new table type to easily pass our data to the procedure. CREATE TYPE dbo.TheilSenInputDataTableType AS TABLE ( ID INT IDENTITY(1,1), x REAL, ...



Top 50 recent answers are included