kwestground

CREATE FUNCTION [dbo].[STRING_SPLIT](
    @string nvarchar(max), 
    @separator varchar(10))
RETURNS @returnList TABLE
(
	[value] [nvarchar](500)
)
AS
BEGIN
    DECLARE @name nvarchar(max);

kwestground

CREATE OR ALTER FUNCTION dbo.func_TruncateWithCount
(
  @input nvarchar(max), @MaxLength int
)
RETURNS nvarchar(max)
AS
BEGIN

	IF (LEN(@input) <= @MaxLength)
		RETURN @input

kwestground

SELECT TOP 1
    T2."UpdateDate", T2."UpdateTS", 
    CAST(
    CAST(T2."UpdateDate" AS date) || ' ' ||
    
    	SUBSTRING(RIGHT('0'+ CAST(T2."UpdateTS" AS varchar), 6), 1, 2) || ':' ||
    	SUBSTRING(RIGHT('0'+ CAST(T2."UpdateTS" AS varchar), 6), 3, 2) || ':' ||
    	SUBSTRING(RIGHT('0'+ CAST(T2."UpdateTS" AS varchar), 6), 5, 2)
    AS datetime)
FROM "ORDR" T2

kwestground

DECLARE @Table nvarchar(100) = 'MyTableName'

SELECT 'REPLACE(''INSERT INTO ['+@Table+'] (' +
    STUFF ((
        SELECT ', [' + name + ']'
        FROM syscolumns
        WHERE id = OBJECT_ID(@Table) AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') +
    ') VALUES (' +

kwestground

SELECT *,
CASE WHEN S1.ResourceName IS NULL THEN 'INSERT INTO LocaleStringResource VALUES ('+cast(S2.LanguageId as varchar)+', '''+S2.ResourceName+''', '''+REPLACE(S2.ResourceValue, '''', '''''')+''')'
ELSE 'UPDATE LocaleStringResource SET [ResourceValue] = '''+S2.ResourceValue+''' WHERE [ResourceName] = '''+S2.ResourceName+''' AND LanguageId='+cast(S2.LanguageId as varchar)+'' END AS [SQL]
FROM LocaleStringResource S1
FULL OUTER JOIN NopCommerceStage.dbo.LocaleStringResource S2 ON S1.ResourceName=S2.ResourceName AND S1.LanguageId=S2.LanguageId
WHERE ISNULL(S1.ResourceValue, '') <> ISNULL(S2.ResourceValue, '')

kwestground

WITH TreeRecursive AS
(
	SELECT T0.ParentCode, T0.ChildCode, 0 [Level] FROM TreeTable T0
		WHERE ParentCode = 'ParentCode'
	UNION ALL
	SELECT TS.ChildCode, T0.ChildCode, TS.[Level] + 1 AS [Level] FROM TreeTable TS
		INNER JOIN TreeTable T0 ON TS.ChildCode = T0.ParentCode
)

SELECT * FROM TreeRecursive

kwestground

ALTER FUNCTION POLR_LATEST_CURRENCY_RATE(CurrencyCode char(3))
RETURNS result decimal(15,2)
LANGUAGE SQLSCRIPT  
SQL SECURITY INVOKER AS
BEGIN	
	DECLARE found INT := 0;
	SELECT COUNT(1) INTO found FROM "ORTT" WHERE "Currency" = :CurrencyCode AND "RateDate" <= CURRENT_DATE;
	
	IF :found > 0
	THEN

kwestground

/*
exec sp_configure 'show advanced options', '1'
RECONFIGURE
exec sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
*/
SET NOCOUNT ON;

DECLARE @FilePath nvarchar(max) = 'C:\Temp\images\';
DECLARE @Command nvarchar(255) = 'dir '+@FilePath+' /b', @FileName nvarchar(255), @Sku nvarchar(255), @Index int, @BinaryData VARBINARY(MAX), @InsertedImageId int, @NopCommerceProductId int;

kwestground

SELECT DISTINCT 
	'INSERT INTO [LocaleStringResource] VALUES ('+substring(shortmessage, len(shortmessage), 1)+','''+
	SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18)
	+''','''+
	UPPER(LEFT(REVERSE(SUBSTRING(REVERSE(SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18)),0,CHARINDEX('.',REVERSE(SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18))))),1))+LOWER(SUBSTRING(REVERSE(SUBSTRING(REVERSE(SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18)),0,CHARINDEX('.',REVERSE(SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18))))),2,LEN(REVERSE(SUBSTRING(REVERSE(SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18)),0,CHARINDEX('.',REVERSE(SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18))))))))
	+''')'
FROM [Log]
LEFT JOIN LocaleStringResource LS ON LS.ResourceName = SUBSTRING(ShortMessage, 18, CHARINDEX(')',ShortMessage) - 18) AND CAST(LS.LanguageId AS varchar) = substring(shortmessage, len(shortmessage), 1) 
WHERE L.ShortMessage like 'Resource

kwestground

SELECT S."QName", S."QString", ST."QName", ST."QString",
CASE WHEN CAST(S."QString" AS varchar) <> CAST(ST."QString" AS varchar) THEN 1 ELSE 0 END AS "Diff"
FROM "OUQR" S 
LEFT JOIN "DB2"."OUQR" ST ON S."QName" = REPLACE(ST."QName", '-', '-')
WHERE S."QName" LIKE '%'