Porting a view from SAP SQL Anywhere to Postgres

Question

I am currently looking into Postgres as a possible alternative to SAP SQL Anywhere. I have successfully ported our development db (700 tables) into Postgres and am now attempting to create some equivalent views in the Postgres DB.

The following :

CREATE VIEW "SYS_Authorisation_BO_Access_View"
AS
SELECT     ROW_NUMBER() OVER (ORDER BY t1.OID) AS OID, t1.OID as BusinessObjectTypeID, t1.TypeName, t1.AccessType, t1.GroupID, a.Authorised, t1.defaultboauthorisation, COALESCE (a.Authorised, t1.defaultboauthorisation) 
                      AS FinalAuthorisation
FROM         "SYS_Authorisation_BO_Access" AS ab INNER JOIN
                      "SYS_Authorisation" AS a ON a.OID = ab.OID RIGHT OUTER JOIN
                          (SELECT     o.OID, o.TypeName, at.AccessType, g.OID as GroupID, 
                                                   CASE at.accesstype WHEN 'C' THEN g.defaultbocreateauthorisation WHEN 'M' THEN g.defaultbomodifyauthorisation WHEN 'D' THEN g.defaultbodeleteauthorisation
                                                    END AS defaultboauthorisation
                            FROM          XPObjectType AS o CROSS JOIN
                                                       (SELECT     'C' AS AccessType
                                                         UNION
                                                         SELECT     'D' AS AccessType
                                                         UNION
                                                         SELECT     'M' AS AccessType) AS at CROSS JOIN
                                                   SYS_Group AS g) AS t1 ON ab.BusinessObjectTypeID = t1.OID AND ab.AccessType = t1.AccessType AND a.GroupID = t1.GroupID

generates the following error :

ERROR:  column a.oid does not exist
LINE 6:                       "SYS_Authorisation" AS a ON a.OID = ab...

score 0 · Answer 1 · edited Oct 08 '18 at 11:59

OK, this turned out to be all case sensitivity. SAP SQL is case insensitive, Postgres isn't.

The following amended SQL worked:

CREATE VIEW "SYS_Authorisation_BO_Access_View"
AS
SELECT     ROW_NUMBER() OVER (ORDER BY t1."OID") AS "OID", t1."OID" as "BusinessObjectTypeID", t1."TypeName", t1."AccessType", t1."GroupID", a."Authorised", t1."DefaultBOAuthorisation", COALESCE (a."Authorised", t1."DefaultBOAuthorisation") 
                      AS "FinalAuthorisation"
FROM         "SYS_Authorisation_BO_Access" AS ab INNER JOIN
                      "SYS_Authorisation" AS a ON a."OID" = ab."OID" RIGHT OUTER JOIN
                          (SELECT     o."OID", o."TypeName", at."AccessType", g."OID" as "GroupID", 
                                                   CASE at."AccessType" WHEN 'C' THEN g."DefaultBOCreateAuthorisation" WHEN 'M' THEN g."DefaultBOModifyAuthorisation" WHEN 'D' THEN g."DefaultBODeleteAuthorisation"
                                                    END AS "DefaultBOAuthorisation"
                            FROM          "XPObjectType" AS o CROSS JOIN
                                                       (SELECT     'C' AS "AccessType"
                                                         UNION
                                                         SELECT     'D' AS "AccessType"
                                                         UNION
                                                         SELECT     'M' AS "AccessType") AS at CROSS JOIN
                                                   "SYS_Group" AS g) AS t1 ON ab."BusinessObjectTypeID" = t1."OID" AND ab."AccessType" = t1."AccessType" AND a."GroupID" = t1."GroupID"

Postgres is only case sensitive when you use those dreaded quoted identifiers: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS I highly recommend to not use double quotes at all in SQL (in general, not only Postgres) — , Oct 08 '18 at 12:01

Porting a view from SAP SQL Anywhere to Postgres

1 Answers1