Porting a view from SAP SQL Anywhere to Postgres [duplicate]

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...

Ian Brown · Accepted Answer · 2018-10-08 11:59:53Z

0

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"

edited Oct 8, 2018 at 11:59

user330315

answered Oct 8, 2018 at 11:10

Ian Brown

1

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Postgres is only case sensitive when you use those dreaded quoted identifiers: postgresql.org/docs/current/static/… I highly recommend to not use double quotes at all in SQL (in general, not only Postgres)

Collectives™ on Stack Overflow

Porting a view from SAP SQL Anywhere to Postgres [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related