CODEWITHSUNDEEP
sql-serveramazon-redshiftdatabase-migrationsql-view
Eugene Sukh
Eugene Sukh

Reputation: 2737

Convert view from SQL Server to Redshift

I have view in SQL Server, that I need to convert to Redshift

Here is the view:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.BE_ContactRelationsDual') AND type in (N'V'))
    DROP VIEW public.contactrelationsdual;
GO

CREATE VIEW public.contactrelationsdual
WITH SCHEMABINDING
AS
SELECT  CASE d.id
        WHEN 0 THEN
                cr.contactId
        ELSE
                cr.relatedContactId
        END me,
        CASE d.id
        WHEN 0 THEN
                cr.relatedContactId
        ELSE
                cr.contactId
        END him,
        cr.id, 
        cr.permissionId
FROM    public.contact_relations cr
CROSS JOIN
        public.system_dual d
WHERE   cr.contactId > 0
        AND cr.relatedContactId > 0
        AND cr.deletedDate IS NULL
GO

CREATE UNIQUE CLUSTERED INDEX
        UX_BE_ContactRelationsDual_Me_Him
ON      public.contactrelationsdual (me, him)
GO

I need to convert it to redshift. I have 2 problems:

This: 

CREATE VIEW public.contactrelationsdual
WITH SCHEMABINDING

and this:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.BE_ContactRelationsDual') AND type in (N'V'))     DROP
VIEW public.contactrelationsdual; GO

How I can correctly convert it to Redshift.

Upvotes: 0

Views: 234

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Remove with schemabinding:

CREATE VIEW public.contactrelationsdual AS
SELECT (CASE d.id WHEN 0 THEN cr.contactId ELSE cr.relatedContactId
        END) as me,
       (CASE d.id WHEN 0 THEN cr.relatedContactId ELSE cr.contactId
        END) as  him,
        cr.id, 
        cr.permissionId
FROM public.contact_relations cr CROSS JOIN
     public.system_dual d
WHERE cr.contactId > 0 AND
      cr.relatedContactId > 0 AND
      cr.deletedDate IS NULL;

This is pretty standard SQL and should work on almost any database.

The IF EXISTS part can be replaced with:

drop view if exists public.contactrelationsdual;

There is no need for a clustered index in Redshift.

Upvotes: 1

Related Questions